MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Michael Sleman Date:July 21 2004 9:49pm
Subject:Tuning MySQL for Large Database
View as plain text  
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
$
+---------------------------------+-------------------------------------
------------------------------------$

Thread
Tuning MySQL for Large DatabaseMichael Sleman21 Jul
  • Re: Tuning MySQL for Large DatabaseMatthew McNicol22 Jul
  • Re: Tuning MySQL for Large DatabaseEgor Egorov26 Jul