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