Re: sanity check on innodb config

[kevin foote]

Rick - Thanks for reassuring me on the error.. random bug?


mysql> SHOW VARIABLES LIKE 'query%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 134217728 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+-----------+


mysql> SHOW GLOBAL STATUS LIKE 'Qc%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 3014 |
| Qcache_free_memory | 107473232 |
| Qcache_hits | 286125 |
| Qcache_inserts | 233940 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 75217 |
| Qcache_queries_in_cache | 9269 |
| Qcache_total_blocks | 21895 |
+-------------------------+-----------+

Today I had a maintenance window so I adjusted some things my.cnf wise.. Here is current relevant [conn / buffer / inno] info. lowered the innodb_log_buffer_size and the max_connections.

# Buffers and Connections
key_buffer = 2M
max_allowed_packet = 32M
sort_buffer_size = 1M
read_buffer_size = 1M
query_cache_size = 128M
connect_timeout = 60
max_connections = 350
max_connect_errors = 1844674407370954751
wait_timeout = 300
interactive_timeout = 300

## INNODB SETTINGS
innodb_data_home_dir = /var/lib/mysql/ibdata
innodb_data_file_path = ib_data1:100M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/ibdata_logs
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 32M
innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 20M
innodb_support_xa = 1
#innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

I also upped the nice level of mysql to -15 and adjusted the OS (RHEL5 x86_64) a bit hugepages / swap wise..

/etc/security/limits.conf
mysql soft memlock 4194304
mysql hard memlock 4194304

/etc/sysctl.conf
vm.nr_hugepages = 2048

So far I see very little in swap via vmstat or sar -B

On the INNODB STATUS output, is this saying only 256k is being used of the innodb_buffer_pool space ??

...
Total memory allocated 4781405733; in additional pool allocated 20953344
Dictionary memory allocated 3414808
Buffer pool size 262144
Free buffers 216738
Database pages 43353
Modified db pages 112
...