MySQL Forums
Forum List  »  Performance

PLEASE HELP - performance is horrible!
Posted by: mike
Date: November 29, 2005 02:40PM

I AM WILLING TO TRY ANYTHING! I am at my wit's end!



Hardware:

Opteron 1.6ghz
1GB RAM
hda: 156301488 sectors (80026 MB) w/2048KiB Cache, CHS=16383/255/63, UDMA(100)
hdc: 320173056 sectors (163928 MB) w/8192KiB Cache, CHS=19929/255/63, UDMA(100)
(Using hdc for MySQL)

Filesystem: XFS
OS: Debian-amd64 (unstable/etch) kernel 2.6.15-rc2-mm1, deadline scheduler
(using unstable/etch so I can get the latest glibc, so the pthread issue is supposed to be resolved)

Database is using InnoDB. Switched from MyISAM to see if I got any more stability or performance or just generally better behavior, and nothing yet...

I've tried MySQL.com compiled binaries, and I just compiled my own. Neither one gives me anything useful.

All connections are over TCP (this is a dedicated machine for MySQL, 4 webservers are hitting it from PHP/FastCGI under Zeus webserver)

70 queries per second should NOT be killing this box.

Threads: 34 Questions: 15323 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 317 Queries per second avg: 65.764

[root@web06 var]# uptime
12:36:32 up 53 min, 3 users, load average: 25.53, 13.15, 6.75

vmstat output:

r b swpd free buff cache si so bi bo in cs us sy id wa
40 0 0 564048 12 240264 0 0 0 5 317 217 100 0 0 0
36 1 0 564048 12 240312 0 0 48 0 466 442 100 0 0 0
33 1 0 563920 12 240452 0 0 132 17 738 1416 100 0 0 0
39 0 0 563792 12 240544 0 0 84 17 652 1103 98 2 0 0
36 1 0 563792 12 240644 0 0 100 9 508 621 99 1 0 0
35 1 0 563664 12 240740 0 0 96 13 673 1404 100 0 0 0
7 0 0 563664 12 240760 0 0 20 9 550 1232 99 1 0 0
32 0 0 563536 12 240812 0 0 52 9 623 1164 100 0 0 0
37 1 0 563528 12 240856 0 0 44 9 638 870 100 0 0 0
37 0 0 563528 12 240908 0 0 52 481 622 1597 99 1 0 0
34 0 0 563144 12 241100 0 0 120 1651 766 1076 98 2 0 0
37 0 0 563144 12 241116 0 0 16 5 626 1105 100 0 0 0
38 0 0 563016 12 241148 0 0 32 0 470 744 99 1 0 0
35 0 0 563016 12 241152 0 0 4 9 379 377 99 1 0 0
35 0 0 563016 12 241172 0 0 20 30 706 1176 99 1 0 0
39 0 0 562640 12 241284 0 0 112 5 741 1332 100 0 0 0
39 0 0 562640 12 241324 0 0 36 13 718 1159 99 1 0 0

here is the my.cnf file:

[mysqld]
socket = /tmp/mysql.sock
port = 3308
user = mysql
datadir = /usr/local/mysql/var
basedir = /usr/local/mysql
mysqld-path = /usr/local/mysql/libexec/mysqld
#log = /usr/local/mysql5/var/log.log
#nonguarded
pid-file = /usr/local/mysql/var/web06.pid
myisam-recover = FORCE
max_connections = 300
server-id = 1
skip-concurrent-insert
skip-name-resolve
skip-thread-priority
skip-external-locking
skip-bdb
expire_logs_days = 7
# these can use tweaking
# tweaked this
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 8M
net_buffer_length = 8K
myisam_sort_buffer_size = 128M
max_heap_table_size = 256M
tmp_table_size = 256M
read_rnd_buffer_size = 16M
max_binlog_size = 128M
# more than enough
thread_cache_size = 75
wait_timeout = 30

# for innodb
innodb_buffer_pool_size = 512M
innodb_flush_log_at_trx_commit = 0

innodb_data_home_dir = /usr/local/mysql/var/
innodb_data_file_path = ibdata1:1024M:autoextend
set-variable = innodb_buffer_pool_size=100M
set-variable = innodb_additional_mem_pool_size=10M

mysql> show status;
+-----------------------------------+-----------+
| Variable_name | Value |
+-----------------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 234 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 91 |
| Bytes_sent | 76 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 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_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 | 0 |
| 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 | 1 |
| 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 | 3908 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 3 |
| Created_tmp_tables | 1 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 1 |
| Delayed_writes | 3655 |
| 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 | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 130 |
| Innodb_buffer_pool_pages_data | 5847 |
| Innodb_buffer_pool_pages_dirty | 64 |
| Innodb_buffer_pool_pages_flushed | 2073 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 61 |
| Innodb_buffer_pool_pages_misc | 553 |
| Innodb_buffer_pool_pages_total | 6400 |
| Innodb_buffer_pool_read_ahead_rnd | 23 |
| Innodb_buffer_pool_read_ahead_seq | 1 |
| Innodb_buffer_pool_read_requests | 197416293 |
| Innodb_buffer_pool_reads | 6958 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 51004 |
| Innodb_data_fsyncs | 778 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 128913408 |
| Innodb_data_reads | 7292 |
| Innodb_data_writes | 2268 |
| Innodb_data_written | 70792704 |
| Innodb_dblwr_pages_written | 2073 |
| Innodb_dblwr_writes | 104 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 5918 |
| Innodb_log_writes | 525 |
| Innodb_os_log_fsyncs | 561 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 2846208 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 57 |
| Innodb_pages_read | 7599 |
| Innodb_pages_written | 2073 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 29 |
| Innodb_row_lock_time_avg | 29 |
| Innodb_row_lock_time_max | 29 |
| Innodb_row_lock_waits | 1 |
| Innodb_rows_deleted | 3400 |
| Innodb_rows_inserted | 1294 |
| Innodb_rows_read | 137327684 |
| Innodb_rows_updated | 3147 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 107036 |
| Key_blocks_used | 135 |
| Key_read_requests | 11017 |
| Key_reads | 135 |
| Key_write_requests | 200 |
| Key_writes | 200 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 43 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 2 |
| Open_streams | 0 |
| Open_tables | 357 |
| Opened_tables | 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 | 29333 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 1 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 31558 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 3 |
| Threads_connected | 41 |
| Threads_created | 43 |
| Threads_running | 38 |
| Uptime | 461 |
+-----------------------------------+-----------+
222 rows in set (0.06 sec)

mysql> show variables;
+---------------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/local/mysql/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 0 |
| connect_timeout | 5 |
| datadir | /usr/local/mysql/var/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 7 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 10485760 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 104857600 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:1024M:autoextend |
| innodb_data_home_dir | /usr/local/mysql/var/ |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 0 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| 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_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 20 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 134217728 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/local/mysql/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1047552 |
| max_binlog_cache_size | 18446744073709551615 |
| max_binlog_size | 134217728 |
| max_connect_errors | 10 |
| max_connections | 300 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 268434432 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036854775807 |
| myisam_recover_options | FORCE |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 134217728 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 8192 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /usr/local/mysql/var/web06.pid |
| port | 3308 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 16773120 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 1 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 8388600 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 10 |
| system_time_zone | PST |
| table_cache | 357 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 75 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 268435456 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.16 |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | unknown-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+----------------------------------------+
209 rows in set (0.07 sec)

Thank anyone in advance!

please reply to my thread about mysql usage - i'd like to collect some data! thanks!
http://forums.mysql.com/read.php?11,57080

Options: ReplyQuote


Subject
Views
Written By
Posted
PLEASE HELP - performance is horrible!
2521
November 29, 2005 02:40PM
3098
November 29, 2005 06:51PM
1624
November 29, 2005 08:24PM
1568
November 29, 2005 08:31PM
1671
November 29, 2005 09:36PM
1456
November 30, 2005 02:36AM
1575
November 29, 2005 09:42PM
1759
November 30, 2005 09:48PM
1705
December 05, 2005 07:13AM
1669
December 08, 2005 06:59AM
1627
December 08, 2005 07:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.