GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

mass Alter even on small tables kills MYSQL

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • mass Alter even on small tables kills MYSQL

    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.

  • #2
    You could try http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces. html

    Comment


    • #3
      if you will take a look at my my.cnf, that I already use it ) because I know that by using this option I could be more flexible with altering, but in fact I am not (

      Comment


      • #4
        Oh I'm sorry. Do you also use that option on your other server? Is your disk almost full?

        If the server has nothing else to do, and an alter table of a table just a few MB in size takes more than a second, something's got to be wrong.

        Comment


        • #5
          disk is almost free.
          test office server is same situation and configuration is same too (per file for innodb).

          I broken my head what's the reason for death of the mysql at most modern Xeon only during altering...

          Comment

          Working...
          X