Announcement

Announcement Module
Collapse
No announcement yet.

dedicated DBServer performance

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • dedicated DBServer performance

    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!

  • #2
    Turn on the slow query log.
    My guess is that you have a lot of queries that doesn't use index and then MySQL needs to perform a table scan.

    And if the table is small enough to fit into RAM then this is a pure CPU intensive operation.

    What supports this theory is that you have a pretty high ratio of locks waited/locks immediate.

    Comment

    Working...
    X