Pythian Group

!*MySQL memory usage potential*! — perl mysql_mem_cnf-usage.pl db1.sample

Analyze current database variables for memory settings and compare to recommendations for engine types and resource availability.
Engine types:
avail: 13 [InnoDB] 4 [MyISAM]
app: 0 [InnoDB] 70 [MyISAM]
mysql: 0 [InnoDB] 15 [MyISAM]
sample_app: 0 [InnoDB] 94 [MyISAM]
test: 0 [InnoDB] 0 [MyISAM]

Global engine stats: 13 [InnoDB] 183 [MyISAM]

Temp Space:
max_heap_table_size: 16777216
tmp_table_size (20971520) reduced to max_heap_table_size (16777216)
::created_tmp_disk_tables / created_tmp_tables: 524375 / 945129
:: if large, consider increasing tmp_table_size (current: 20971520)

Open Tables:
::opened_tables: 4696 – current 300 open
:: if large, consider increasing table_open_cache (current: 300)

Handler Types – suggests scan types:
::handler_read_key: 234180339
::handler_read_first: 7485442
::handler_read_next: 653975869
::handler_read_previous: 216841
::handler_read_rnd: 87718789
::handler_update: 46201061
::handler_delete: 12406222
::handler_write: 2437434595
::handler_rollback: 634167

COM_… Values:
::com_alter_table: 536
::com_commit: 942294
::com_create_table: 150
::com_delete: 2972374
::com_drop_table: 85
::com_flush: 246
::com_insert: 9805299
::com_insert_select: 5
::com_purge: 240
::com_replace: 124713
::com_rollback: 318714
::com_select: 57998672
::com_truncate: 1
::com_update: 13356230

General memory usage:
binlog_cache_size: 32768
query_cache_size: 16777216
::holding 6689 queries with 8946176 free memory
::qcache hit ratio: 91 %
::qcache_lowmem_prunes can suggest qcache too small – 7347343
TOTAL: 16.03 M

MyISAM fixed memory usage:
key_buffer_size: 536870912
::key_buffer usage: 44 % (high-water: 66 %)
::Read ratio to disk instead of buffer: 0.08 % (ideally less than 1%)
::Write ratio to disk instead of buffer: 49.64 % (may be high if lots of deletes/updates)
delay_key_write isON, lowers key_disk_write ratio.
myisam_sort_buffer_size: 8388608 (for sorting MyISAM indexes)
TOTAL: 520 M

InnoDB fixed memory usage:
have_innodb?: YES
innodb_buffer_pool_size: 2147483648
innodb_additional_mem_pool_size: 5242880
innodb_log_buffer_size: 1048576
TOTAL: 2054 M

Per connection memory usage:
thread stack: 131072
net_buffer_length: 16384 (alloc. per client, grows to 16776192)
TOTAL: 0.15 M

Per allocation memory usage:
bulk_insert_buffer_size: 8388608 (alloc. for insert..select, load data infile, insert value exteded)
sort_buffer_size: 2097144 (allocated for repair table or create index)
read_buffer_size: 131072 (allocated for sequential scans)
read_rnd_buffer_size: 262144 (pre-alloc. for sorted reads)
join_buffer_size: 131072 (using full-table-scan, alloc. per join)
TOTAL: 10.49 M

Summary:

max_connections: 500
Potential Connection Memory: 75 M
::Current Connections – 2 (2 active)
::Max_Used_Connections – 96
::Thread Cache (0) hit ratio – 0.0100000000000051 %

Theoretical Potential Consumption: 2649 M
MySQL formula: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
= 536870912 + (131072 + 2097144) * 500 = 1574.49 M
Physical memory available: 2027.7 M
Warning: estimated potential memory may be too high for resources available