MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Matthew McNicol Date:July 22 2004 7:25am
Subject:Re: Tuning MySQL for Large Database
View as plain text  
My 5 cents... tuning the MySQL database config file is half a solution. 
In my experience optimizing the application code and table/index 
structure delivers performance results.



Michael Sleman wrote:

> Hello,
> 
> We're running 1 web server (apache 2 & php) / 1 dedicated DB server
> (MySQL 4.0.20-standard) and are experiencing serious performance issues
> on the DB during some load testing.
> 
> Hardware on both
> Dual Xeon 2.8GHz, 2GB RAM
> 
> The database size is a little under 1 GB.
> 
> Naturally, we started taking a look at MySQL config and have gotten some
> significant speed improvements but the app is really slow and queries
> take 10secs on average to execute. From my past experiences with MySQL,
> I still think the DB can do much better. 
> 
> Any recommendations for additional configuration changes?
> 
> The stats during a 15 minute load test was up the roof at:
> Load Average: 12 avg, 26 peak, 
> MySQL processes: 20 avg, 80 peak 
> Memory usage was 280MB used, 1.4GB cached, 122MB buffers, 216MB FREE.
> 
> I'm attaching my.cnf, show status, show variables from a 15 minute load
> test.
> 
> Thanks,
> -Michael
> 
> 
> 
> =======
> my.cnf
> =======
> [mysqld]
> log_slow_queries = /var/log/slow-queries.log
> long_query_time = 5
> log-long-format
> 
> skip-locking
> key_buffer = 384M
> max_allowed_packet = 16M
> max_connections = 400
> table_cache = 512
> sort_buffer_size = 2M
> read_buffer_size = 1M
> myisam_sort_buffer_size = 64M
> thread_cache = 8
> thread_concurrency = 8
> query_cache_size = 32M
> 
> log-bin
> max_binlog_size = 100M
> 
> [mysqldump]
> quick
> max_allowed_packet = 16M
> 
> [mysql]
> no-auto-rehash
> 
> [isamchk]
> key_buffer = 384M
> sort_buffer = 256M
> read_buffer = 2M
> write_buffer = 2M
> 
> [myisamchk]
> key_buffer = 384M
> sort_buffer = 256M
> read_buffer = 2M
> write_buffer = 2M
> 
> [mysqlhotcopy]
> interactive-timeout
> 
> 
> 
> 
> 
> ============
> SHOW STATUS:
> ============
> 
> +--------------------------+----------+
> | Variable_name            | Value    |
> +--------------------------+----------+
> | Aborted_clients          | 0        |
> | Aborted_connects         | 9        |
> | Bytes_received           | 18494586 |
> | Bytes_sent               | 50916200 |
> | Com_admin_commands       | 0        |
> | Com_alter_table          | 0        |
> | Com_analyze              | 0        |
> | Com_backup_table         | 0        |
> | Com_begin                | 0        |
> | Com_change_db            | 201360   |
> | Com_change_master        | 0        |
> | Com_check                | 0        |
> | Com_commit               | 0        |
> | Com_create_db            | 0        |
> | Com_create_function      | 0        |
> | Com_create_index         | 0        |
> | Com_create_table         | 0        |
> | Com_delete               | 0        |
> | Com_delete_multi         | 0        |
> | Com_drop_db              | 0        |
> | Com_drop_function        | 0        |
> | Com_drop_index           | 0        |
> | Com_drop_table           | 0        |
> | Com_flush                | 0        |
> | Com_grant                | 0        |
> | Com_ha_close             | 0        |
> | Com_ha_open              | 0        |
> | Com_ha_read              | 0        |
> | Com_insert               | 1724     |
> | Com_insert_select        | 128      |
> | Com_kill                 | 0        |
> | Com_load                 | 0        |
> | Com_load_master_data     | 0        |
> | Com_load_master_table    | 0        |
> | Com_lock_tables          | 0        |
> | Com_optimize             | 0        |
> | Com_purge                | 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_rollback             | 0        |
> | Com_savepoint            | 0        |
> | Com_select               | 23934    |
> | Com_set_option           | 0        |
> | Com_show_binlog_events   | 0        |
> | Com_show_binlogs         | 0        |
> | Com_show_create          | 0        |
> | Com_show_databases       | 0        |
> | Com_show_fields          | 0        |
> | Com_show_grants          | 0        |
> | Com_show_keys            | 0        |
> | Com_show_logs            | 0        |
> | Com_show_master_status   | 0        |
> | Com_show_new_master      | 0        |
> | Com_show_open_tables     | 0        |
> | Com_show_processlist     | 0        |
> | Com_show_slave_hosts     | 0        |
> | Com_show_slave_status    | 0        |
> | Com_show_status          | 1        |
> | Com_show_innodb_status   | 0        |
> | Com_show_tables          | 0        |
> | Com_show_variables       | 0        |
> | Com_slave_start          | 0        |
> | Com_slave_stop           | 0        |
> | Com_truncate             | 0        |
> | Com_unlock_tables        | 0        |
> | Com_update               | 1921     |
> | Connections              | 1579     |
> | Created_tmp_disk_tables  | 0        |
> | Created_tmp_tables       | 505      |
> | Created_tmp_files        | 0        |
> | Delayed_insert_threads   | 0        |
> | Delayed_writes           | 0        |
> | Delayed_errors           | 0        |
> | Flush_commands           | 1        |
> | Handler_commit           | 0        |
> | Handler_delete           | 0        |
> | Handler_read_first       | 2        |
> | Handler_read_key         | 7193286  |
> | Handler_read_next        | 52615749 |
> | Handler_read_prev        | 0        |
> | Handler_read_rnd         | 252854   |
> | Handler_read_rnd_next    | 32018036 |
> | Handler_rollback         | 0        |
> | Handler_update           | 31659    |
> | Handler_write            | 27284    |
> | Key_blocks_used          | 5892     |
> | Key_read_requests        | 23227345 |
> | Key_reads                | 5759     |
> | Key_write_requests       | 15153    |
> | Key_writes               | 12482    |
> | Max_used_connections     | 78       |
> | Not_flushed_key_blocks   | 0        |
> | Not_flushed_delayed_rows | 0        |
> | Open_tables              | 356      |
> | Open_files               | 390      |
> | Open_streams             | 0        |
> | Opened_tables            | 362      |
> | Questions                | 406432   |
> | Qcache_queries_in_cache  | 1052     |
> | Qcache_inserts           | 17981    |
> | Qcache_hits              | 175797   |
> | Qcache_lowmem_prunes     | 0        |
> | Qcache_not_cached        | 5953     |
> | Qcache_free_memory       | 32395208 |
> | Qcache_free_blocks       | 171      |
> | Qcache_total_blocks      | 2301     |
> | Rpl_status               | NULL     |
> | Select_full_join         | 0        |
> | Select_full_range_join   | 0        |
> | Select_range             | 1        |
> | Select_range_check       | 0        |
> | Select_scan              | 301      |
> | Slave_open_temp_tables   | 0        |
> | Slave_running            | OFF      |
> | Slow_launch_threads      | 0        |
> | Slow_queries             | 626      |
> | Sort_merge_passes        | 0        |
> | Sort_range               | 8154     |
> | Sort_rows                | 252854   |
> | Sort_scan                | 323      |
> | Table_locks_immediate    | 25774    |
> | Table_locks_waited       | 10105    |
> | Threads_cached           | 7        |
> | Threads_created          | 291      |
> | Threads_connected        | 2        |
> | Threads_running          | 2        |
> | Uptime                   | 855      |
> +--------------------------+----------+
> 
> 
> 
> 
> ===============
> SHOW VARIABLES:
> ===============
> 
> +---------------------------------+-------------------------------------
> ------------------------------------$
> | Variable_name                   | Value
> $
> +---------------------------------+-------------------------------------
> ------------------------------------$
> | back_log                        | 50
> $
> | basedir                         | /
> $
> | binlog_cache_size               | 32768
> $
> | bulk_insert_buffer_size         | 8388608
> $
> | character_set                   | latin1
> $
> | character_sets                  | latin1 big5 czech euc_kr gb2312 gbk
> latin1_de sjis tis620 ujis dec8 dos $
> | concurrent_insert               | ON
> $
> | connect_timeout                 | 5
> $
> | convert_character_set           |
> $
> | datadir                         | /var/lib/mysql/
> $
> | default_week_format             | 0
> $
> | delay_key_write                 | ON
> $
> | delayed_insert_limit            | 100
> $
> | delayed_insert_timeout          | 300
> $
> | delayed_queue_size              | 1000
> $
> | flush                           | OFF
> $
> | flush_time                      | 0
> $
> | ft_boolean_syntax               | + -><()~*:""&|
> $
> | ft_min_word_len                 | 4
> $
> | ft_max_word_len                 | 254
> $
> | ft_max_word_len_for_sort        | 20
> $
> | ft_stopword_file                | (built-in)
> $
> | have_bdb                        | NO
> $
> | have_crypt                      | YES
> $
> | have_innodb                     | YES
> $
> | have_isam                       | YES
> $
> | have_raid                       | NO
> $
> | have_symlink                    | YES
> $
> | have_openssl                    | NO
> $
> | have_query_cache                | YES
> $
> | init_file                       |
> $
> | innodb_additional_mem_pool_size | 1048576
> $
> | innodb_buffer_pool_size         | 8388608
> $
> | innodb_data_file_path           | ibdata1:10M:autoextend
> 
> | innodb_buffer_pool_size         | 8388608
> $
> | innodb_data_file_path           | ibdata1:10M:autoextend
> $
> | innodb_data_home_dir            |
> $
> | innodb_file_io_threads          | 4
> $
> | innodb_force_recovery           | 0
> $
> | innodb_thread_concurrency       | 8
> $
> | innodb_flush_log_at_trx_commit  | 1
> $
> | innodb_fast_shutdown            | ON
> $
> | innodb_flush_method             |
> $
> | innodb_lock_wait_timeout        | 50
> $
> | innodb_log_arch_dir             | ./
> $
> | innodb_log_archive              | OFF
> $
> | innodb_log_buffer_size          | 1048576
> $
> | innodb_log_file_size            | 5242880
> $
> | innodb_log_files_in_group       | 2
> $
> | innodb_log_group_home_dir       | ./
> $
> | innodb_mirrored_log_groups      | 1
> $
> | innodb_max_dirty_pages_pct      | 90
> $
> | interactive_timeout             | 28800
> $
> | join_buffer_size                | 131072
> $
> | key_buffer_size                 | 402653184
> $
> | language                        | /usr/share/mysql/english/
> $
> | large_files_support             | ON
> $
> | license                         | GPL
> $
> | local_infile                    | ON
> $
> | locked_in_memory                | OFF
> $
> | log                             | OFF
> $
> | log_update                      | OFF
> $
> | log_bin                         | ON
> $
> | log_slave_updates               | OFF
> $
> | log_slow_queries                | ON
> $
> | log_warnings                    | ON
> $
> | long_query_time                 | 5
> $
> | low_priority_updates            | OFF
> $
> | lower_case_file_system          | OFF
> $
> | lower_case_table_names          | 0
> $
> | max_allowed_packet              | 16776192
> $
> | max_binlog_cache_size           | 4294967295
> $
> | max_binlog_size                 | 104857600
> $
> | max_connections                 | 400
> $
> | max_connect_errors              | 10
> $
> | max_delayed_threads             | 20
> $
> | max_insert_delayed_threads      | 20
> $
> | max_heap_table_size             | 16777216
> $
> | max_join_size                   | 4294967295
> $
> | max_relay_log_size              | 0
> $
> | max_seeks_for_key               | 4294967295
> $
> | max_sort_length                 | 1024
> $
> | max_user_connections            | 0
> $
> | max_tmp_tables                  | 32
> $
> | max_write_lock_count            | 4294967295
> $
> | myisam_max_extra_sort_file_size | 268435456
> $
> | myisam_max_sort_file_size       | 2147483647
> $
> | myisam_repair_threads           | 1
> $
> | myisam_recover_options          | OFF
> $
> | myisam_sort_buffer_size         | 67108864
> $
> | net_buffer_length               | 16384
> $
> | net_read_timeout                | 30
> $
> | net_retry_count                 | 10
> $
> | net_write_timeout               | 60
> $
> | new                             | OFF
> $
> | open_files_limit                | 2010
> $
> | pid_file                        |
> /var/lib/mysql/stgdb01.cendant.ip-soft.net.pid
> $
> | log_error                       |
> $
> | port                            | 3306
> $
> | protocol_version                | 10
> $
> | query_alloc_block_size          | 8192
> $
> | query_cache_limit               | 1048576
> $
> | query_cache_size                | 33554432
> $
> | query_cache_type                | ON
> $
> | query_prealloc_size             | 8192
> $
> | range_alloc_block_size          | 2048
> $
> | read_buffer_size                | 1044480
> $
> | read_only                       | OFF
> $
> | read_rnd_buffer_size            | 262144
> $
> | rpl_recovery_rank               | 0
> $
> | server_id                       | 1
> $
> | slave_net_timeout               | 3600
> $
> | skip_external_locking           | ON
> $
> | skip_networking                 | OFF
> $
> | skip_show_database              | OFF
> $
> | slow_launch_time                | 2
> $
> | socket                          | /var/lib/mysql/mysql.sock
> $
> | sort_buffer_size                | 2097144
> $
> | sql_mode                        | 0
> $
> | table_cache                     | 512
> $
> | table_type                      | MYISAM
> $
> | thread_cache_size               | 8
> $
> | thread_stack                    | 126976
> $
> | tx_isolation                    | REPEATABLE-READ
> $
> | timezone                        | EDT
> $
> | tmp_table_size                  | 33554432
> $
> | tmpdir                          | /tmp/
> $
> | transaction_alloc_block_size    | 8192
> $
> | transaction_prealloc_size       | 4096
> $
> | version                         | 4.0.20-standard-log
> $
> | version_comment                 | Official MySQL RPM
> $
> | version_compile_os              | pc-linux
> $
> | wait_timeout                    | 28800
> $
> +---------------------------------+-------------------------------------
> ------------------------------------$
> 
> 

-- 

_________________
Matthew McNicol

yellowmarker.co.uk
PHP / MySQL web development
Thread
Tuning MySQL for Large DatabaseMichael Sleman21 Jul
  • Re: Tuning MySQL for Large DatabaseMatthew McNicol22 Jul
  • Re: Tuning MySQL for Large DatabaseEgor Egorov26 Jul