Tuning 400gb db

[Brian Z]

Hello,

We have been battling very slow db access on some large MySQL databases as of late.

The databases are all INNODB, and were about 420GB before a purging brought down the size to about 120GB today. This is close to the minimum size this set of databases will ever be at this point.

When the database grew to over 420GB, it just about filled the partition it was on. Auto-shrink of the INNODB file is another problem all together though. I suspect having data at the edge of the disks may be part of the problem. This lack of auto-shrink is going to cause hours of downtime while I dump the data to a file store, drop all the databases, then re-import everything to gett INNODB to shrink. Ack. Is there no better way to do this?

There is 1GB RAM right now, but we are looking at putting 4GB in tomorrow if we can tune MySQL to properly use it. I have tried a number of config file modifications, but am not sure where to focus.

We have also tried running a master-slave scenario to offload all the selects away from the front line server, but the replication fell behind over 3 days before we pulled the plug on that plan. The slave server was identical to the master.


IOSTAT is showing:
Linux 2.6.10-1.770_FC2 (mysqlserver) 06/09/2005

avg-cpu: %user %nice %sys %iowait %idle
16.06 0.00 2.02 24.89 57.04

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 78.61 255.42 23.05 2147483647 193824842


my.cnf shows:
# grep -v "#" /etc/my.cnf

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock


[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 16M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
thread_concurrency = 4
max_connections = 500
old-passwords

server-id=1



innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:50000M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 50M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 32M
sort_buffer_size = 32M
read_buffer = 1M
write_buffer = 1M

[myisamchk]
key_buffer = 32M
sort_buffer_size = 32M
read_buffer = 1M
write_buffer = 1M

[mysqlhotcopy]
interactive-timeout




MySQL Status shows:
mysql> show status;
--------------
show status
--------------

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 18641 |
| Aborted_connects | 423 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 3897177152 |
| Bytes_sent | 1651651998 |
| Com_admin_commands | 31126 |
| Com_alter_db | 0 |
| Com_alter_table | 786643 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 13 |
| Com_change_db | 11706050 |
| Com_change_master | 0 |
| Com_check | 1 |
| Com_checksum | 0 |
| Com_commit | 10434442 |
| Com_create_db | 54 |
| Com_create_function | 0 |
| Com_create_index | 2 |
| Com_create_table | 410334 |
| Com_dealloc_sql | 0 |
| Com_delete | 10040 |
| Com_delete_multi | 7 |
| Com_do | 0 |
| Com_drop_db | 1 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 878671 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 6 |
| Com_grant | 4 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 3 |
| Com_insert | 2217783 |
| Com_insert_select | 195470 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 588936 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 1 |
| Com_repair | 1 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 221524 |
| Com_savepoint | 0 |
| Com_select | 13318190 |
| Com_set_option | 35314120 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 18 |
| Com_show_charsets | 168 |
| Com_show_collations | 9634981 |
| Com_show_column_types | 0 |
| Com_show_create_db | 12 |
| Com_show_create_table | 317787 |
| Com_show_databases | 198241 |
| Com_show_errors | 0 |
| Com_show_fields | 261189 |
| Com_show_grants | 17 |
| Com_show_innodb_status | 3 |
| Com_show_keys | 141 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 1 |
| Com_show_privileges | 0 |
| Com_show_processlist | 242 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 14394 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 434016 |
| Com_show_variables | 19269795 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 3 |
| Com_unlock_tables | 480018 |
| Com_update | 1164882 |
| Com_update_multi | 0 |
| Connections | 12174869 |
| Created_tmp_disk_tables | 123 |
| Created_tmp_files | 766664 |
| Created_tmp_tables | 9327 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 577675 |
| Handler_delete | 15532 |
| Handler_discover | 0 |
| Handler_read_first | 1033146 |
| Handler_read_key | 264251845 |
| Handler_read_next | 3013531696 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 4994326 |
| Handler_read_rnd_next | 983391247 |
| Handler_rollback | 2056789 |
| Handler_update | 250304136 |
| Handler_write | 3331109210 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 348900 |
| Key_blocks_used | 29721 |
| Key_read_requests | 59820913 |
| Key_reads | 58004 |
| Key_write_requests | 2108861 |
| Key_writes | 351187 |
| Max_used_connections | 501 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 43 |
| Open_streams | 0 |
| Open_tables | 468 |
| Opened_tables | 1670782 |
| Qcache_free_blocks | 3513 |
| Qcache_free_memory | 26972056 |
| Qcache_hits | 27202262 |
| Qcache_inserts | 3280103 |
| Qcache_lowmem_prunes | 89517 |
| Qcache_not_cached | 9868100 |
| Qcache_queries_in_cache | 4403 |
| Qcache_total_blocks | 12815 |
| Questions | 147315886 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 643304 |
| Select_range_check | 0 |
| Select_scan | 1036049 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 1070 |
| Slow_queries | 86376 |
| Sort_merge_passes | 2129 |
| Sort_range | 863 |
| Sort_rows | 17671785 |
| Sort_scan | 1753 |
| Table_locks_immediate | 6285275 |
| Table_locks_waited | 24064 |
| Threads_cached | 0 |
| Threads_connected | 14 |
| Threads_created | 8356671 |
| Threads_running | 4 |
| Uptime | 8407437 |
+--------------------------+------------+
156 rows in set (0.00 sec)



Here is the hardware setup:

Dell P4 2.8GHz
4 x 250GB SATA drives in hardware RAID 10
1 GB RAM

Software:
Fedora Core 2
MySQL-server-4.1.8-0



I have done some looking into large setups, but I need to know the proven, production, method for scaling read and write access to a constantly growing database. Clustering active-active machines may be the way to go, but I am hesitent to recommend it until it has had more time in the market.

Any help or suggestions on configuration and tuning would be very welcome.


Brian