Ticket #369: mysqltuner.txt

Queries

Uptime in seconds: 353421
Uptime: 4d 2h 10m 21s
Questions: 44184575
% slow queries: 0.008720237775287
slow query rate: 0.0021318160035334 per day
Long query time: 2
Slow query logging: ON
% reads: 77.487356803695
% writes: 22.512643196305
qps: 125.01966493219
reads per sec: 0.18943151730767 per day
writes per sec: 0.055036128927277 per day
Queries: 125.01966493219 per second
Connections: 360 Thousand
Bytes sent: 390 Billion
Bytes received: 10 Billion


versions

Supported Version: 5
Release Series: 5.1
Minor Version: 49
Distribution: (Debian)
Distribution: (Debian)

MySQL Architecture: x86_64


Query cache

Query cache efficiency (%): 72.405803474947
% query cache used: 30.010414123535
The query cache is not being fully utilized.
    (Qcache_free_memory / query_cache_size * 100 <80)
    (5034912 / 16777216 * 100 30.010414123535<80)
Query cache low memory prunes: 26.640426007509 per second
Increase query_cache_size -- there are too many low memory prunes.
    (&hr_bytime(Qcache_lowmem_prunes/Uptime_since_flush_status) =~ /second|minute/)
    (dbtuner_hr_bytime(9415286/353421) dbtuner_stristr('26.640426007509 per second', array('second', 'minute')))
Query cache size: 16.0 Mb
Query cache min result size: 16.0 Mb


Sorts

Total sorts: 2071671
% sorts that cause temporary tables: 0.065792300032196
rate of sorts that cause temporary tables: 13.88372507576 per hour
sort_buffer_size: 1.0 Mb
read_rnd_buffer_size: 32.0 Mb
Sort rows: 143.50195376053 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(50716604/353421) dbtuner_stristr('143.50195376053 per second', array('second', 'minute')))

Joins,scans

rate of joins without indexes: 1.638782075768 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 + 494507 + 84673)/353421) dbtuner_stristr('1.638782075768 per second', array('second', 'minute')))
rate of reading first index entry: 43.580262632951 per minute
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(256703/353421) dbtuner_stristr('43.580262632951 per minute', array('second', 'minute')))
rate of reading fixed position: 99.38185619983 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(35123635/353421) dbtuner_stristr('99.38185619983 per second', array('second', 'minute')))
rate of reading next table row: 4523.9129593318 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(1598845842/353421) dbtuner_stristr('4523.9129593318 per second', array('second', 'minute')))

temp tables

tmp_table_size-max_heap_table_size: 0
tmp_table_size: 16.0 Mb
max_heap_table_size: 16.0 Mb
% temp disk tables: 28.294290280249
Too many temporary tables are being written to disk. Increase max_heap_table_size and tmp_table_size.
    (Created_tmp_disk_tables / (Created_tmp_tables + Created_tmp_disk_tables) * 100 >25)
    (649423 / (1645821 + 649423) * 100 28.294290280249>25)
temp disk rate: 1.8375337062597 per second
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(649423/353421) dbtuner_stristr('1.8375337062597 per second', array('second', 'minute')))
temp table rate: 4.6568285415977 per second
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(1645821/353421) dbtuner_stristr('4.6568285415977 per second', array('second', 'minute')))


MyISAM index cache

MyISAM key buffer size: 128.0 Mb
max % MyISAM key buffer ever used: 55.672454833984
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)
    ((72971)*1024/134217728 * 100 55.672454833984<95)
% MyISAM key buffer used: 66.635131835938
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-43732*1024/134217728) * 100 66.635131835938<95)
% index reads from memory: 99.996478938936


other caches

table open cache size (5.1+): 2048
Size of the table cache
    (table_open_cache >-1)
    (2048 2048>-1)
rate of table open: 1.5333554033292 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(9032/353421) dbtuner_stristr('1.5333554033292 per minute', array('second', 'minute')))
% open files: 60.934930702373
rate of open files: 26.422878097227 per hour
Immediate table locks %: 99.959572846793
Table lock wait rate: 1.3111275221337 per minute
Too many table locks were not granted immediately. Optimize queries and/or use InnoDB to reduce lock wait.
    (&hr_bytime(Table_locks_waited/Uptime_since_flush_status) =~ /second|minute/)
    (dbtuner_hr_bytime(7723/353421) dbtuner_stristr('1.3111275221337 per minute', array('second', 'minute')))
thread cache: 8
Total threads created: 342
thread cache hit rate %: 0.00094877713169692
Threads that are slow to launch: 0
Slow launch time: 2


Connections

% connections used: 23.17880794702
Max connections used: 35
Max connections limit: 151
% aborted connections: 0.00055484042789294
rate of aborted connections: 0.48893529246989 per day
% aborted clients: 0.00083226064183941
rate of aborted clients: 0.73340293870483 per day


InnoDB

Is InnoDB enabled?: YES
% innoDB log size: 62.5
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)
    (5242880 / 8388608 * 100 62.5>=0)


other

MyISAM concurrent inserts: 1

INSERT DELAYED USAGE


Delayed_errors 0


Delayed_insert_threads 0


Delayed_writes 0


Not_flushed_delayed_rows