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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
