Do I need to make any my.cnf config changes?

[altenerg]

Here's my mysqltuner output after 40 hours running on AWS t1.Micro EC2 instance, small IPB with only 30,000+ monthly page views:

-------- General Statistics --------------------------------------------------

[OK] Currently running supported MySQL version 5.1.49-3-log

[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------

[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 41M (Tables: 229)

[--] Total fragmented tables: 1

-------- Performance Metrics -------------------------------------------------

[--] Up for: 1d 16h 46m 36s (266K q [1.817 qps], 18K conn, TX: 3B, RX: 107M)

[--] Reads / Writes: 57% / 43%

[--] Total buffers: 48.0M global + 3.6M per thread (8 max threads)

[OK] Maximum possible memory usage: 76.5M (12% of installed RAM)

[OK] Slow queries: 0% (0/266K)

[OK] Highest usage of available connections: 25% (2/8)

[OK] Key buffer size / total MyISAM indexes: 16.0M/9.9M

[OK] Key buffer hit rate: 99.1% (4M cached / 40K reads)

[OK] Query cache efficiency: 49.3% (81K cached / 165K selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 7K sorts)

[!!] Temporary tables created on disk: 34% (1K on disk / 5K total)

[OK] Thread cache hit rate: 99% (2 created / 18K connections)

[!!] Table cache hit rate: 12% (257 open / 2K opened)

[OK] Open file limit used: 49% (510/1K)

[OK] Table locks acquired immediately: 99% (213K immediate / 213K locks)

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

When making adjustments, make tmp_table_size/max_heap_table_size equal

Reduce your SELECT DISTINCT queries without LIMIT clauses

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

tmp_table_size (> 16M)

max_heap_table_size (> 16M)

table_cache (> 384)

[altenerg]

As you can see I raised table_cache and it made no difference. At one point I raised tmp_table_size (> 32M)
max_heap_table_size (> 32M) ...also no difference

Here's another recent snapshot of mysqltuner output: Any suggestions?

[OK] Currently running supported MySQL version 5.1.61-log

[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------

[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 46M (Tables: 240)

[!!] Total fragmented tables: 19

-------- Security Recommendations -------------------------------------------

[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------

[--] Up for: 6d 13h 30m 18s (1M q [2.924 qps], 127K conn, TX: 61B, RX: 448M)

[--] Reads / Writes: 50% / 50%

[--] Total buffers: 48.0M global + 2.6M per thread (8 max threads)

[OK] Maximum possible memory usage: 68.5M (11% of installed RAM)

[OK] Slow queries: 0% (0/1M)

[OK] Highest usage of available connections: 25% (2/8)

[OK] Key buffer size / total MyISAM indexes: 16.0M/13.1M

[OK] Key buffer hit rate: 99.5% (26M cached / 141K reads)

[OK] Query cache efficiency: 51.7% (487K cached / 942K selects)

[!!] Query cache prunes per day: 1093

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 30K sorts)

[!!] Temporary tables created on disk: 33% (5K on disk / 17K total)

[OK] Thread cache hit rate: 99% (2 created / 127K connections)

[!!] Table cache hit rate: 3% (120 open / 3K opened)

[OK] Open file limit used: 22% (237/1K)

[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

When making adjustments, make tmp_table_size/max_heap_table_size equal

Reduce your SELECT DISTINCT queries without LIMIT clauses

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

query_cache_size (> 16M)

tmp_table_size (> 16M)

max_heap_table_size (> 16M)

table_cache (> 512)

[Grumpy]

Is there actually a problem you need to resolve? You say "it made no difference", so I feel like you're working towards a noticeable goal.

Also, it seems like you've dedicated a very small portion of ram for your mysql to use (68.5MB).

[altenerg]

The goal is to get rid of these warnings:

[!!] Temporary tables created on disk: 33% (5K on disk / 17K total)
[!!] Table cache hit rate: 3% (120 open / 3K opened)

[AlexJ]

I was not able to fix table cache hit rate either. If u figure out how to fix it, let me know.

What's your my.cnf?

[p4guru]

The goal is to get rid of these warnings:

[color=#282828][font=helvetica, arial, sans-serif][size=3][background=rgb(247, 247, 247)][!!] Temporary tables created on disk: 33% (5K on disk / 17K total)[/background][/size][/font][/color]

[color=#282828][font=helvetica, arial, sans-serif][size=3][background=rgb(247, 247, 247)][!!] Table cache hit rate: 3% (120 open / 3K opened)[/background][/size][/font][/color]

they're just guidelines not set in stone

Might want to try mysqlmymonlite.sh at http://mysqlmymon.com/ it includes a modified version of mysqltuner.pl for more clarity on some of the recommendations

[AlexJ]

Try this:

http://hackmysql.com...reportguide#l51

Mine is something like:

__ Tables ______________________________________________________________

Open			 1869 of 2600	%Cache:  71.88

Opened		  9.31k	 7.9/s

I am not sure how to decrease 7.9/s....

[Luis Manson]

i was NEVER able to get rid of that 33%....

[AlexJ]

Try this values:

query_cache_size = 56M

table_open_cache = 1300

open_files_limit = 4000

I believe that should improve your table cache hit rate. Once changed monitor it for 1-3 days.

[Grumpy]

The goal is to get rid of these warnings:

[color=#282828][font=helvetica, arial, sans-serif][size=3][background=rgb(247, 247, 247)][!!] Temporary tables created on disk: 33% (5K on disk / 17K total)[/background][/size][/font][/color]

[color=#282828][font=helvetica, arial, sans-serif][size=3][background=rgb(247, 247, 247)][!!] Table cache hit rate: 3% (120 open / 3K opened)[/background][/size][/font][/color]

This isn't a good goal.
There are times when mysqltuner will even give bad advices.
It warns about stupid things too like...
[!!] Maximum possible memory usage: 13.5G (86% of installed RAM)
Uh... this is the database server. Why would using 86% of the ram be a problem? lol

mysqltuner will ask for more and more ram for mysql until the point where entire database is going to operate from ram. And quite frankly, you only have like little over half a gig of ram. I personally have tmp table cache as half a gig. lol

[AlexJ]

Why would you assign 512M for temp table when more then 64-128M makes no difference for one forum? Just curious.

[Grumpy]

Why would you assign 512M for temp table when more then 64-128M makes no difference for one forum? Just curious.

1. Because I can afford to.
2. Because I didn't bother to fine tune to lower it back.
3. I have more than just the forums running.

But temp table is a limit, not a pre-assignment of memory block. So, values too big doesn't seem to create any negative side effects unless you can't actually handle that much ram usage.

[p4guru]

There are times when mysqltuner will even give bad advices.

It warns about stupid things too like...

[color=#282828][font=helvetica, arial, sans-serif][size=3][background=rgb(247, 247, 247)][!!] Maximum possible memory usage: 13.5G (86% of installed RAM)[/background][/size][/font][/color]

Uh... this is the database server. Why would using 86% of the ram be a problem? lol

mysqltuner will ask for more and more ram for mysql until the point where entire database is going to operate from ram. And quite frankly, you only have like little over half a gig of ram. I personally have tmp table cache as half a gig. lol

solution, use a better version of mysqltuner = mysqltuner v1.21 mod http://forums.cpanel...tml#post1142951 :smile:

i.e.

>>  MySQLTuner 1.2.1 mod - Major Hayden <major@mhtx.net>

>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

>>  Modified by George Liu (eva2000) at http://vbtechsupport.com/

>>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.2.12-MariaDB-mariadb115

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster

[OK] Total fragmented tables: 0

-------- Performance Metrics -------------------------------------------------

[--] Up for: 3s (33 q [11.000 qps], 16 conn, TX: 60K, RX: 1K)

[--] Reads / Writes: 100% / 0%

[--] Total buffers: 40.0M global + 928.0K per thread (2000 max threads)

[!!] Maximum possible memory usage: 1.8G (184% of installed RAM)

[OK] Slow queries: 0% (0/33)

[OK] Highest usage of available connections: 0% (1/2000)

[OK] Key buffer size / total MyISAM indexes: 16.0M/91.0K

[!!] Query cache efficiency: 0.0% (0 cached / 8 selects)

[OK] Query cache prunes per day: 0

[OK] Temporary tables created on disk: 0% (0 on disk / 11 total)

[OK] Thread cache hit rate: 93% (1 created / 16 connections)

[OK] Table cache hit rate: 53% (8 open / 15 opened)

[OK] Open file limit used: 0% (19/10K)

[OK] Table locks acquired immediately: 100% (18 immediate / 18 locks)

-------- Recommendations -----------------------------------------------------

General recommendations:

	MySQL started within last 24 hours - recommendations may be inaccurate

	Reduce your overall MySQL memory footprint for system stability.

	See notes on accuracy of this recommendation below

	Enable the slow query log to troubleshoot bad queries.

	Don not forget to disable slow query logging after troubleshooting

   - For MySQL 5.0 http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

   - For MySQL 5.1 http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

   - For MySQL 5.5 http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html

   - For MySQL 5.6 http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html

Variables to adjust:

  *** MySQL maximum memory usage is dangerously high ***

  *** Add RAM before increasing MySQL buffer variables ***

  *** Please note this recommendation is not entirely accurate.

	  The formula used to calculate max memory usage assumes all queries utilise

	  all memory buffers simultaneously. When in reality it is very rare for a

	  query to engage & utilise all memory buffers simultaneously.

	  Formula also assumes all predefined max_connections are reached.

	  You could have set max_connections = 1000 and in a whole year

	  of usage never hit beyond 50 max_used_connections. So your real MySQL

	  memory usage is only 1/20th of theorectical max memory usage reported.

	  So real max memory usage will never reach this peak.

	  So do not be too concerned with this warning.

	  It is better to monitor your real MySQL max_used_connection and MySQL

	  memory usage over time and adjust accordingly.

	  You can use tools such as Cacti, Munin or mysqlmymonlite.sh at mysqlmymon.com

	  to monitor your MySQL memory usage over time.

	  MySQL performs optimally when its required amount of memory is met.

	  Reducing and starving MySQL memory allocation to adhere to this

	  artificial max memory warning - of which in reality will never be reached,

	  will only reduce MySQL performance in many cases ***

[altenerg]

Why would you assign 512M for temp table when more then 64-128M makes no difference for one forum? Just curious.

To each his own, 95% of admins go crazy on my.ini settings. :hyper: They think somehow bigger settings like 512MB mean faster/better lol.

Many times having tons of ram and trying to force MySQL to use all just slows things down.

Speed and efficiency with modest settings have given me best results on tiny Ec2 instance:

[AlexJ]

U execution time is way low.. that's nice! For some reason, I can't achieve that... what's your php handler?

[Aussie Cable]

My execution time is similar, however we have much more resources available than a Ec2 instance.



I would be interested to see what your "modest settings" are altenerg (if you would be so kind in sharing :thumbsup: )

[AlexJ]

My execution time is similar, however we have much more resources available than a Ec2 instance.

I would be interested to see what your "modest settings" are altenerg (if you would be so kind in sharing :thumbsup: )

What's EC2 instance? Also can u share your php and mysql settings if you don't mind?

[Aussie Cable]

What's EC2 instance?

Look here under "micro instances", that will give you the correct information.

Also can u share your php and mysql settings if you don't mind?

My php settings can be found here (we have a high memory limit due to php intensive apps).

We do not run on a Ec2 instance, we are running on a dedicated machine (hex (6) core CPU, 12GB RAM).

Here are our mySQL settings:



We have yet to get these settings "Just right" (any suggestions are welcome).

I hope that helps.

[altenerg]

I'm trying to reply to this thread with attachments but IPB says:

You have used 738.52K of 250K

How much storage do you guys have here?

Also, next to the "choose files" for me it says

You can upload up to Uploading is not allowed of files (Max. single file size: 40MB)

:/

[Aussie Cable]

I'm trying to reply to this thread with attachments but IPB says:

You only have a small amount of space available for attachments, it is better to:

Upload your screenshots to flickr or imgur, then using the Image icon () paste your links, it will add it directly to the editor.

As for other attachments, use it sparingly, you may delete your old attachments from your User CP remembering that it will delete your attachments in the appropriate topic.

[altenerg]

You have used 738.52K of 250K

According this that I have No space available. How did I get to 738K if the limit is 250K?

[Aussie Cable]

Submit a bug in the tracker (seeings that you are not a IPS client).

[altenerg]

My license expired. Maybe thats why