MySQL Database Tuning 4.7

lanesharon - May 24, 2006 - 07:26

I have found several posts on the Drupal site that mention tuning your SQL database. I even downloaded a module that had a recent CVS date and ran it. It ran fine, but I have to seriously doubt the output, since I do not have cache enabled on my drupal website (yet). Here is the output. I am hoping someone can explain it to me since all the links do not work in the text:
Thread Cache Monitor
Threads Created Connections Thread Cache Size Hit Ratio Warnings
2683 435020 10 0.6% The ideal situation is to get Threads Created as close as possible to Thread Cache Size. So no new connections are having to wait for new thread allocation. Stay as close to a 99% hit ratio as you can as this will reduce bottlenecks in your caching. Adjust your Thread Cache Size until this is achieved. You can set your Thread Cache Size on the fly by doing "SET GLOBAL thread_cache_size=N". Where N is the desired size of your Thread Cache. For additional information on the SET command please read SET Syntax

Table Cache Monitor
Table Cache Open Tables Opened Tables Warnings
1024 1024 119871 Your table cache is currently full. This can severely impact the performance of your MySQL server. If you have the memory, it may be time to increase your table cache. However, if your table cache is set too high, MySQL may start dropping connections. You can read about how MySQL uses the table cache here. Increase your thread cache by issuing a 'SET thread_cache_size=N' command. Where N is the desired size of your Thread Cache. More Information on the SET command can be found here.

Query Cache Monitor
Query Cache Ratio 0.46 Your query cache is not maintaining a healthy hit ratio of 1 / 10 - Inserts / Hits. This can mean that your mySQL instance has become hard drive bound. This statistic can be improved by using your mySQL thread, table, and query cache effectively. You can also improve this from a code standpoint. Maintain a level of consistency in your code. Whether or not a query will be placed in the cache is case sensitive, as mySQL checks for a byte identical match.
Query Cache Inserts 2838919
Query Cache Hits 6221877
Query Cache Available? YES
Query Cache Size 67108864
Query Cache Type ON
Query Cache Free Memory 14285864
Query Cache Free Blocks 4512 If your Query Cache has a lot of free blocks in it. This can indicate fragmentation in your Query Cache. This can decrease performance and generally just be a waste of resoures. Fix this problem by issuing a 'FLUSH QUERY CACHE' command. Read more about query cache maintenance here.
Query Cache Prunes 1014787
Query Cache Limit 2097152
Queries Not Cached 78475

Memory Monitor
Minimum Memory Needed : 19884630008
This number is a calculation of the maximum amount of memory your mySQL instance will consume on your server . It is a sum of all the caches and buffers that would benefit by not using swap or not having to goto the HD. Having a number larger than your total available amount of memory will result in swapping.

Min_memory_needed = key_buffer + innodb_buffer_pool + innodb_log_buffer + innodb_additional_mem_pool + net_buffer + max_connections * (read_buffer + join_buffer + sort_buffer + myisam_sort_buffer + thread_stack + tmp_table_size + read_rnd_buffer)
Key Buffer : 33554432
InnoDB Buffer Pool : 8388608
InnoDB Log Buffer : 1048576
InnoDB Additional Memory Pool : 1048576
Net Buffer : 16384
Max Connections : 450
Read Buffer : 2093056
Join Buffer : 2093056
Sort Buffer : 4194296
Myisam Sort Buffer : 33554432
Thread Stack : 196608
Tmp Table Size : 16777216
Read Rnd Buffer Size : 262144

Select Monitor
Variable Name Current Value Warnings
Select_full_join 9529 A high value here means that MySQL is not using indexes and is therefore taking longer to build a result set. The problem can be fixed by indexing important fields of the join.Enable the Slow Query Log and use Explain to examine your queries.
Select_scan 1021676 A high value here can be an indication of bottlenecks in your server optimization. This happens because Mysql is not using the indexes for the tables and so is having to do extra work for inefficient queries. Enable the Slow Query Log and use Explain to examine your queries.
Slow_queries 28 You Have queries which are executing slower than normal. Enable the Slow Query Log and use Explain to examine your queries.

You need to give some more

grcm - May 24, 2006 - 09:24

You need to give some more information e.g. type/size of hosting and the nature of the web site traffic. Steady 30 pages/sec? Quiet with slashdots every 6 months?

Also, there's no point in optimising until you've enabled caching.

-- Version Control your Drupal web site with The File High Club's Free Trial!

My Stats

lanesharon - May 24, 2006 - 16:47

Type of Hosting: Shared Plan
Apache version 1.3.34 (Unix)
PHP version 4.4.2
MySQL version 4.1.18-standard
Cpanel 10 and Cron access
Size: Unlimited, if I choose. The account is more than large enough now.

Brand new Drupal install, 4.7, with very little traffic. Google seems to visit the most. That's why I am confused at the results I am seeing on that report. This is not a high hit site, at all. And, if I am reading the report correctly, it is getting hit. Not sure from where, because it is not showing in Awstats.

Take Care, Sharon

 
 

Drupal is a registered trademark of Dries Buytaert.