Hello,
My dedicated DBServer is running high CPU recently with 0.0%idle sometimes.
Here are the server specs:
Dual Intel Xeon 2.66 GHz
3GB DDR
MySQL 4.1.20-standard is running on the server. Might upgrade to 5.1 soon.
No persistent connection is used in PHP script.
We’re considering using cluster DB servers, but was told that our
data require too many RAM to run in MySQL cluster environment. They executed an utility that estimates RAM requirements for
the database that will be clustered against our database and it valued the amount of RAM required as 4457632 KB (4.25 Gigs).
While the maximum RAM of our servers is 4 Gigs.
I’m not sure what i was told is correct, I would like to know there is otherway to solve my current problem. Following is my.cnf
[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
skip-locking
old_passwords=1
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=600
interactive_timeout=60
wait_timeout=30
connect_timeout=10
thread_cache_size=128
key_buffer=256M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=5M
read_buffer_size=2M
max_connect_errors=10
Try number of CPU’s*2 for thread_concurrency
thread_concurrency=8
myisam_sort_buffer_size=64M
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
#pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=512M
[mysql]
no-auto-rehash
#safe-updates
[isamchk]
key_buffer=256M
sort_buffer=256M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=256M
sort_buffer=256M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
extended status:
| Aborted_clients | 2410 |
| Aborted_connects | 451 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 218962129 |
| Bytes_sent | 4184673199 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 2 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 2080665 |
| 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 | 2252 |
| 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 | 5851 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 1 |
| 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 | 421544 |
| Com_set_option | 246 |
| 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 | 20 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 21 |
| 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 | 9 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 4 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 21 |
| 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_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 97564 |
| Com_update_multi | 0 |
| Connections | 379501 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 4 |
| Created_tmp_tables | 9658 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 2772 |
| Handler_discover | 0 |
| Handler_read_first | 323 |
| Handler_read_key | 9904053 |
| Handler_read_next | 114404914 |
| Handler_read_prev | 3384288478 |
| Handler_read_rnd | 1589479 |
| Handler_read_rnd_next | 2393943120 |
| Handler_rollback | 0 |
| Handler_update | 97398 |
| Handler_write | 3939043 |
| Key_blocks_not_flushed | 4 |
| Key_blocks_unused | 185336 |
| Key_blocks_used | 46624 |
| Key_read_requests | 346685993 |
| Key_reads | 67647 |
| Key_write_requests | 1691376 |
| Key_writes | 43389 |
| Max_used_connections | 601 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 923 |
| Open_streams | 0 |
| Open_tables | 898 |
| Opened_tables | 936 |
| Qcache_free_blocks | 88 |
| Qcache_free_memory | 31774296 |
| Qcache_hits | 2590545 |
| Qcache_inserts | 397737 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 23796 |
| Qcache_queries_in_cache | 1478 |
| Qcache_total_blocks | 3041 |
| Questions | 5578874 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 7 |
| Select_range_check | 0 |
| Select_scan | 65826 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 29 |
| Slow_queries | 2752 |
| Sort_merge_passes | 0 |
| Sort_range | 25136 |
| Sort_rows | 28061116 |
| Sort_scan | 44861 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 473259 |
| Table_locks_waited | 76364 |
| Threads_cached | 46 |
| Threads_connected | 482 |
| Threads_created | 8802 |
| Threads_running | 23 |
| Uptime | 10966 |
Our DB size is around 800MB currently.
Thanks!