Jump to content

SQL Optimization... are these critical?

Recommended Posts

- By: Matthew Montgomery -

MySQL Version 5.0.81-community i686

Uptime = 1 days 10 hrs 41 min 37 sec
Avg. qps = 7
Total Questions = 936575
Threads Connected = 1

The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 7 out of 936596 that take longer than 10 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.

The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

Max Memory Ever Allocated : 1.97 G
Configured Max Per-thread Buffers : 2.16 G
Configured Max Global Buffers : 330 M
Configured Max Memory Limit : 2.49 G
Physical Memory : 1.23 G

Max memory limit exceeds 90% of physical memory

Query cache is enabled
Current query_cache_size = 64 M
Current query_cache_used = 4 M
Current query_cache_limit = 8 M
Current Query cache Memory fill ratio = 7.73 %
Current query_cache_min_res_unit = 4 K
Query Cache is 22 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

Current join_buffer_size = 8.00 M
You have had 12 queries where a join could not use an index properly
join_buffer_size >= 4 M
This is not advised
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.

Current max_heap_table_size = 64 M
Current tmp_table_size = 63 M
Of 12174 temp tables, 26% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

Current Lock Wait ratio = 1 : 894
You may benefit from selective use of MyISAM.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

Link to comment
Share on other sites

That's a lot of information to review and take in before responding. I got as far as the 4th line.

I'd suggest that you should wait a bit longer before deciding whether to act on the mysqltuner output. For example, your board may be MUCH busier at weekends, or month end. You need more empirical data before deciding what action to take, otherwise you'll be forever fiddling with the settings for different times of the day/week/month.

I'd also suggest that you only change one or two settings at a time. Most changes affect the memory footprint, so that a change in one area can dramatically affect another.

I have a 'hit list' somewhere of the most important MySQL settings to change first, I'll see if I can dig it out.

Link to comment
Share on other sites


This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...