I don't know if it is the right place, but I would appreciate some help to optimize my database server.
I switch to innodb, set collation and indexes as suggested.
I adjusted my.cnf according to the initial recommendation of DBTuner and some googling.
Below is the current report.
For example, these warming do not change whatever I set for the mentioned parameters
- The query cache is not being fully utilized.
- Too many temporary tables are being written to disk. Increase max_heap_table_size and tmp_table_size.
- Too many intermediate temporary tables are being created; consider increasing sort_buffer_size (sorting), read_rnd_buffer_size (random read buffer, ie, post-sort), read_buffer_size (sequential scan).
- The rate of opening tables is high, increase table_open_cache to avoid this.
I already set huge numbers without any effect.
Note that I am using cacherouter with APC, this might have an impacr on the mysql settings.
Thanks for help ,
Queries
Uptime in seconds: 522848
Uptime: 6d 1h 14m 8s
Questions: 90973971
% slow queries: 0.00142678173299
slow query rate: 0.000235773956733 per day
Long query time: 2
Slow query logging: ON
% reads: 43.5249923387
% writes: 56.4750076613
qps: 173.996976177
reads per sec: 0.0719245237252 per day
writes per sec: 0.0933242675105 per day
Queries: 173.996976177 per second
Connections: 207 Thousand
Bytes sent: 266 Billion
Bytes received: 34 Billion
versions
Supported Version: 5
Release Series: 5.1
Minor Version: 58
Distribution: (Debian)
Distribution: (Debian)
MySQL Architecture: x86_64
Query cache
Query cache efficiency (%): 93.8692499175
% query cache used: 49.0942209959
The query cache is not being fully utilized.
(Qcache_free_memory / query_cache_size * 100 <80)
(527145184 / 1073741824 * 100 49.0942209959<80)
Query cache low memory prunes: 0 per day
Query cache size: 1.0 Gb
Query cache min result size: 8.0 Mb
Sorts
Total sorts: 491378
% sorts that cause temporary tables: 0.109080992637
rate of sorts that cause temporary tables: 3.6905563376 per hour
sort_buffer_size: 500.0 Kb
read_rnd_buffer_size: 256.0 Kb
Sort rows: 12.1683414683 per second
There are lots of rows being sorted. Consider using indexes in more queries to avoid sorting too often.
(&hr_bytime(Sort_rows/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime(6362193/522848) dbtuner_stristr('12.1683414683 per second', array('second', 'minute')))
Joins,scans
rate of joins without indexes: 1.27814010955 per second
There are too many joins without indexes -- this means that joins are doing full table scans.
(&hr_bytime((Select_range_check + Select_scan + Select_full_join)/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime((0 + 654239 + 14034)/522848) dbtuner_stristr('1.27814010955 per second', array('second', 'minute')))
rate of reading first index entry: 1.89994606463 per second
The rate of reading the first index entry is high; this usually indicates frequent full index scans.
(&hr_bytime(Handler_read_first/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime(993383/522848) dbtuner_stristr('1.89994606463 per second', array('second', 'minute')))
rate of reading fixed position: 6.39883675562 per second
The rate of reading data from a fixed position is high; this indicates many queries need to sort results and/or do a full table scan, including join queries that do not use indexes.
(&hr_bytime(Handler_read_rnd/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime(3345619/522848) dbtuner_stristr('6.39883675562 per second', array('second', 'minute')))
rate of reading next table row: 5629.62670795 per second
The rate of reading the next table row is high; this indicates many queries are doing full table scans.
(&hr_bytime(Handler_read_rnd_next/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime(2943439065/522848) dbtuner_stristr('5629.62670795 per second', array('second', 'minute')))
temp tables
tmp_table_size-max_heap_table_size: 0
tmp_table_size: 1.0 Gb
max_heap_table_size: 1.0 Gb
% temp disk tables: 19.4564074878
temp disk rate: 10.9182401004 per minute
Too many temporary tables are being written to disk. Increase max_heap_table_size and tmp_table_size.
(&hr_bytime(Created_tmp_disk_tables/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime(95143/522848) dbtuner_stristr('10.9182401004 per minute', array('second', 'minute')))
temp table rate: 45.1981837934 per minute
Too many intermediate temporary tables are being created; consider increasing sort_buffer_size (sorting), read_rnd_buffer_size (random read buffer, ie, post-sort), read_buffer_size (sequential scan).
(&hr_bytime(Created_tmp_tables/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime(393863/522848) dbtuner_stristr('45.1981837934 per minute', array('second', 'minute')))
MyISAM index cache
MyISAM key buffer size: 256.0 Mb
max % MyISAM key buffer ever used: 34.4104766846
MyISAM key buffer (index cache) % used is low. You may need to decrease the size of key_buffer_size, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.
((Key_blocks_used)*key_cache_block_size/key_buffer_size * 100 <95)
((90205)*1024/268435456 * 100 34.4104766846<95)
% MyISAM key buffer used: 35.8875274658
MyISAM key buffer (index cache) % used is low. You may need to decrease the size of key_buffer_size, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.
((1-Key_blocks_unused*key_cache_block_size/key_buffer_size) * 100 <95)
((1-168067*1024/268435456) * 100 35.8875274658<95)
% index reads from memory: 99.882188626
other caches
table open cache size (5.1+): 5000
Size of the table cache
(table_open_cache >-1)
(5000 5000>-1)
rate of table open: 5.20659924108 per minute
The rate of opening tables is high, increase table_open_cache to avoid this.
(&hr_bytime(Opened_tables/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime(45371/522848) dbtuner_stristr('5.20659924108 per minute', array('second', 'minute')))
% open files: 60.7468477207
rate of open files: 43.1230491462 per hour
Immediate table locks %: 99.9765799932
Table lock wait rate: 22.9007283187 per hour
thread cache: 100
Total threads created: 30
thread cache hit rate %: 0.000144535126854
Threads that are slow to launch: 0
Slow launch time: 2
Connections
% connections used: 10
Max connections used: 30
Max connections limit: 300
% aborted connections: 0.0274616741022
rate of aborted connections: 9.41918110043 per day
% aborted clients: 0.0284252416146
rate of aborted clients: 9.74967868291 per day
InnoDB
Is InnoDB enabled?: YES
% innoDB log size: 13.0208333333
InnoDB log file size is not an appropriate size, in relation to the InnoDB buffer pool. Consider changing either\ninnodb_log_file_size or innodb_buffer_pool_size
(innodb_log_file_size / innodb_buffer_pool_size * 100 >=0)
(838860800 / 6442450944 * 100 13.0208333333>=0)
other
MyISAM concurrent inserts: 1
INSERT DELAYED USAGE
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Not_flushed_delayed_rows
Comments
Comment #1
dalinThese automated scripts are good at telling you when some buffer or other setting needs to be increased. However they are really dumb about figuring out when further increases are not likely to help (or will even hinder) performance.
Since you are not using the DB for cache tables the query cache may not be useful. Do some benchmarks to compare the performance of disabling it completely (depending on your site performance may improve noticeably). But at the very least, 1G is faaaar too big. I wouldn't set it any larger than 256M.
The tuning script also is pretty dumb about sorting, simply because it doesn't have access to the right data. Many queries must use temp tables because they filter and/or sort on multiple tables. This is an inherent problem with relational databases. So the values that you have for max_heap_table_size, tmp_table_size, sort_buffer_size, read_rnd_buffer_size, and read_buffer_size are probably all waaaay too big. I generally don't increase more than 8x the default.
Remember that each site is unique and you need to test before and after you make a change to see if it is actually helping you.
Also your innodb_buffer_pool_size is only 6GB. This is the single most important setting and I would recommend trying 512M (if you have the RAM available) and increase if it improves performance.
Also after you've done some basic MySQL tuning you are probably better off to head back to Drupal for further gains (improve block caching logic, cache views, panels, etc.).
Comment #2
jvieille commentedThanks for the advices.
innodb_buffer_pool_size = 6G, so much more than your 512M recommendation
the server has 12GB of RAM
Comment #3
jvieille commentedRegarding the query cache, I disabled it totally.
I don"t feel a performance impact from a user perspective.
However, Munin reports a very different behaviour.
Previously, Mysql queries where mainly served by Mysql cache.
After the change, the query rate increased a lot - see attached. It basically went from low blue (cache hits) to high green (select queries)
Does that means that APC is not effective?
APC dashborad is here
Thanks for help