I have dual 8 core Xeon (16 cores total), hardware RAID-1 with 15K rpm SAS, 8G of the memory.
I use only InnoDB engine. Structure of the my mysql server is around 14 databases with 2100 tables at each. Currently, tables are quite small (less than thousand of the records at each), most of the empty. Such structure is necessary for one highload project, it stores splitted mirrored user’s information.
Sometimes during development it’s neccesary to run mass Alter procedure, when we need to add some new fields for example. Alter commands were sent one by one without parralelizm. For example today I ran such procedure for 100 tables at each 14 databases, 99% of them were empty.
Ok, here is my issue, when I run this mass Alter - Mysql becomes slow like the old floppy disk, it looks like when he has even 1 alter command, he puts all resources on it, all other queries are executed with lowest priority.
First of all each Alter tooks 1-2s!!!
BUT CPU load is almost 0, IO stat is OK, no hdd high load.
Linux 2.6.27.7-9-default (server3) 09/17/09 x86_64
avg-cpu: %user %nice %system %iowait %steal %idle
8.59 0.00 2.27 0.11 0.00 89.05
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 9.61 49.15 1782.88 506005411 18354603253
sda1 0.79 11.49 11.26 118243500 115926880
sda2 1.06 7.82 26.70 80495233 274914754
sda3 7.76 29.84 1744.92 307240182 17963761619
When I am trying to execute any most simple query using primary key to other tables with max ten records, it executes it 0.5-1.5s!!! even I am trying to run it again and again (looks like it doesn’t use read disk cache absolutely).
Mysql ver. is 5.0.51a
site_o_id - is PK, number of the records is less than 15;
mysql> SELECT site_o_name, project_o_id FROM db_2.u144_sites_o WHERE site_o_id=30;
±---------------------±-------------+
| site_o_name | project_o_id |
±---------------------±-------------+
| ??? ?? | 2 |
±---------------------±-------------+
1 row in set (0.88 sec)
mysql> SELECT site_o_name, project_o_id FROM db_2.u144_sites_o WHERE site_o_id=30;
±---------------------±-------------+
| site_o_name | project_o_id |
±---------------------±-------------+
| ??? ?? | 2 |
±---------------------±-------------+
1 row in set (0.56 sec)
mysql> explain SELECT site_o_name, project_o_id FROM db_2.u144_sites_o WHERE site_o_id=30;
±—±------------±-------------±------±--------------± --------±--------±------±-----±------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±-------------±------±--------------± --------±--------±------±-----±------+
| 1 | SIMPLE | u144_sites_o | const | PRIMARY | PRIMARY | 3 | const | 1 | |
±—±------------±-------------±------±--------------± --------±--------±------±-----±------+
1 row in set (0.53 sec)
When alter queries are finished, this query tooks 0.00 as usual.
Because each alter query tooks 1-2s or sometimes more, the total alter time for 1400 tables tooks more than 1 hour.
I should notice that same operation(altering of the 1400 tables at 14 databases) at our test office server intel Q6600 (4 core), 8G RAM, software raid between 2 SATA 7200rpm hdds tooks less than 2 mins!!! with same my.cnf!
MySQL 5.0.51a.
OS - OpenSUSE 11.1, Linux server3 2.6.27.7-9-default #1 SMP 2008-12-04 18:10:04 +0100 x86_64 x86_64 x86_64 GNU/Linux, file system is XFS.
office test server: Debian Etch, Linux level7 2.6.24-etchnhalf.1-amd64 #1 SMP Tue Dec 2 17:21:26 UTC 2008 x86_64 GNU/Linux, file system is XFS
The total size of the database is around 14G.
Here is my my.cnf:
[mysqld]
basedir = /usr/local/mysql/
datadir = /usr/local/mysql/data
user = mysql
port = 3306
socket = /usr/local/mysql/mysql.sock
bind-address = 10.77.0.78
skip-locking
skip-external-locking
skip-name-resolve
character-set-server = cp1251
default-character-set = cp1251
character-set-client = cp1251
read_buffer_size = 128K
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
join_buffer_size = 4M
key_buffer = 64M
tmp_table_size = 32M
max_heap_table_size = 32M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 256
max_connections = 256
table_cache = 128000
thread_concurrency = 0
open_files_limit = 256000
query_cache_limit = 0M
query_cache_size = 0M
skip-bdb
innodb_open_files = 65535
binlog_cache_size = 1M
innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_size = 4G
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:100M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_log_arch_dir = /usr/local/mysql/data
innodb_file_io_threads = 16
innodb_thread_concurrency = 0
innodb_concurrency_tickets = 4096
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 8M
innodb_log_file_size = 1024M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 70
innodb_lock_wait_timeout = 120
innodb_thread_sleep_delay = 0
innodb_flush_method=O_DIRECT
innodb_support_xa=0
innodb_file_per_table
#memlock
server-id = 100
log_slow_queries = /usr/local/mysql/3-slow.log
long_query_time = 5
master-host=10.77.0.77
master-port=3308
master-user=****
master-password=*****
read_only=0
Status values of the problem Xeon server:
mysql> SHOW STATUS;
±----------------------------------±-----------+
| Variable_name | Value |
±----------------------------------±-----------+
| Aborted_clients | 8 |
| Aborted_connects | 3 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 111 |
| Bytes_sent | 5909 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_create_user | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 1 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 6489 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 2 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 227 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 358 |
| Innodb_buffer_pool_pages_data | 94423 |
| Innodb_buffer_pool_pages_dirty | 356 |
| Innodb_buffer_pool_pages_flushed | 101322 |
| Innodb_buffer_pool_pages_free | 166439 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 1282 |
| Innodb_buffer_pool_pages_total | 262144 |
| Innodb_buffer_pool_read_ahead_rnd | 66 |
| Innodb_buffer_pool_read_ahead_seq | 96 |
| Innodb_buffer_pool_read_requests | 1153408769 |
| Innodb_buffer_pool_reads | 75886 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 4854649 |
| Innodb_data_fsyncs | 71498 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 1363513344 |
| Innodb_data_reads | 84884 |
| Innodb_data_writes | 120353 |
| Innodb_data_written | 3696776704 |
| Innodb_dblwr_pages_written | 101322 |
| Innodb_dblwr_writes | 3137 |
| Innodb_log_waits | 3 |
| Innodb_log_write_requests | 741365 |
| Innodb_log_writes | 31370 |
| Innodb_os_log_fsyncs | 33065 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 375879680 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 29761 |
| Innodb_pages_read | 83089 |
| Innodb_pages_written | 101322 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 718664 |
| Innodb_row_lock_time_avg | 209 |
| Innodb_row_lock_time_max | 1616 |
| Innodb_row_lock_waits | 3434 |
| Innodb_rows_deleted | 9532 |
| Innodb_rows_inserted | 45354 |
| Innodb_rows_read | 1248787734 |
| Innodb_rows_updated | 1035839 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 214339 |
| Key_blocks_used | 9 |
| Key_read_requests | 3862 |
| Key_reads | 9 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 38 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 19 |
| Open_streams | 0 |
| Open_tables | 10865 |
| Opened_tables | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 2058021 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 2 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | ON |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 2335122 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 6 |
| Threads_connected | 32 |
| Threads_created | 38 |
| Threads_running | 1 |
| Uptime | 24816 |
| Uptime_since_flush_status | 24816 |
±----------------------------------±-----------+
226 rows in set (0.01 sec)
Please, advice … I googled everywhere and didn’t find anyting.
At furture, we will need sometimes to alter some tables. At now when we do altering server is almost die. It’s unacceptable for us to so slow DB at live system.