Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2005
    Posts
    94

    High server loads, high memory usage.

    I think MySQL is getting high off ram.. I have only 4,000KB of memory left.

    top - 18:08:33 up 136 days, 49 min, 1 user, load average: 59.49, 40.38, 25.95
    Tasks: 192 total, 3 running, 187 sleeping, 2 stopped, 0 zombie
    Cpu(s): 59.0% us, 18.4% sy, 0.0% ni, 0.0% id, 20.0% wa, 1.0% hi, 1.6% si
    Mem: 768500k total, 763564k used, 4936k free, 1244k buffers
    Swap: 2249060k total, 886816k used, 1362244k free, 11972k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    38 root 16 0 0 0 0 S 3.5 0.0 35:02.55 kswapd0
    14223 nobody 16 0 22600 9136 14m D 0.7 1.2 0:00.94 httpd
    16199 nobody 15 0 17212 3808 14m S 0.7 0.5 0:00.22 httpd
    16198 nobody 16 0 26012 11m 14m S 0.6 1.6 0:00.86 httpd
    12702 nobody 16 0 27908 4996 14m S 0.5 0.7 0:02.94 httpd
    13378 nobody 15 0 26600 8632 14m D 0.5 1.1 0:01.22 httpd
    15812 nobody 15 0 25052 9.8m 14m S 0.5 1.3 0:01.25 httpd
    15819 nobody 15 0 24868 10m 14m S 0.5 1.4 0:01.00 httpd
    15860 nobody 15 0 25744 4424 14m S 0.5 0.6 0:00.58 httpd
    16072 nobody 16 0 22596 9204 14m S 0.5 1.2 0:00.43 httpd
    16074 nobody 15 0 19344 5904 14m S 0.5 0.8 0:00.31 httpd
    16080 nobody 15 0 24056 10m 14m S 0.5 1.3 0:00.96 httpd
    16238 nobody 17 0 26456 12m 14m S 0.5 1.6 0:01.03 httpd
    13937 nobody 16 0 27172 11m 14m S 0.4 1.5 0:03.13 httpd
    15090 nobody 15 0 26016 9984 14m S 0.4 1.3 0:01.28 httpd
    15100 nobody 16 0 24076 10m 14m S 0.4 1.4 0:01.50 httpd
    15202 nobody 16 0 25624 10m 14m S 0.4 1.3 0:01.19 httpd
    15851 nobody 16 0 25204 4908 14m S 0.4 0.6 0:00.86 httpd
    16073 nobody 16 0 25796 10m 14m S 0.4 1.4 0:00.92 httpd
    16076 nobody 16 0 25124 9976 14m S 0.4 1.3 0:00.68 httpd
    16168 nobody 16 0 25096 10m 14m S 0.4 1.5 0:00.81 httpd
    10307 nobody 16 0 26064 9.9m 14m S 0.4 1.3 0:05.81 httpd
    10846 nobody 15 0 26840 4984 14m S 0.4 0.6 0:06.06 httpd
    12706 nobody 15 0 26028 8708 14m D 0.4 1.1 0:02.41 httpd
    13499 nobody 16 0 26144 9.9m 14m S 0.4 1.3 0:03.22 httpd
    13635 nobody 15 0 26404 9.8m 14m S 0.4 1.3 0:01.68 httpd
    13918 nobody 16 0 24056 9.9m 14m S 0.4 1.3 0:02.19 httpd
    15177 nobody 15 0 26044 4360 14m S 0.4 0.6 0:01.10 httpd
    As you can see, my loads are 50! I only have 700MB of memory and mysql drained it down to 4MB. *sigh*

    My current my.cnf configurations are as listed:

    # The following options will be passed to all MySQL clients
    [client]
    #password = your_password
    port = 3306
    socket = /var/lib/mysql/mysql.sock

    # Here follows entries for some specific programs

    # The MySQL server
    [mysqld]
    skip-locking
    max_connections=300
    connect_timeout=15
    key_buffer=16M
    join_buffer=1M
    record_buffer=1M
    sort_buffer=2M
    table_cache=1028
    thread_cache_size=286
    max_allowed_packet=5M
    wait_timeout=15
    query_cache_limit=1M
    query_cache_size=32M
    query_cache_type=1
    thread_concurrency=2
    log-bin

    # required unique id between 1 and 2^32 - 1
    # defaults to 1 if master-host is not set
    # but will not function as a master if omitted
    server-id = 1

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [mysql]
    no-auto-rehash

    [isamchk]
    key_buffer = 128M
    sort_buffer_size = 128M
    read_buffer = 2M
    write_buffer = 2M

    [myisamchk]
    key_buffer = 128M
    sort_buffer_size = 128M
    read_buffer = 2M
    write_buffer = 2M

    [mysqlhotcopy]
    interactive-timeout
    Urgent help is needed.

    If these do not help, I'd like to know the specifications for a server that feeds the following needs:

    700+ Visitors/day
    Heavy SQL usage including search reliance


    Thanks
    Mysqladmin returns:

    Uptime: 7087 Threads: 41 Questions: 222725 Slow queries: 2956 Opens: 13897 Flush tables: 1 Open tables: 64 Queries pe
    r second avg: 31.427
    Last edited by kiler; 11-27-2005 at 01:43 PM.

  2. #2
    Join Date
    Jun 2003
    Location
    Janesville, Wi
    Posts
    1,516
    Uh, mySQL isn't causing this. Apache is. You need to tune Apache and probably turn off KeepAlive. It looks like you're actually starving mySQL of memory, limiting it to 16MB. If you have a lot of viewers coming to the site, KeepAlive on Apache will end up causing problems instead of helping.

    Change:

    table_cache=1028
    thread_cache_size=286

    To:

    table_cache=256
    thread_cache_size=24

    I would also change the key_buffer to 64M

    Let me know how this helps.
    Jakiao

  3. #3
    Hi!

    Well its clear that the problem is with httpd (apache) try to optimize apache and you will get the result.

  4. #4
    Join Date
    Jun 2003
    Location
    Janesville, Wi
    Posts
    1,516
    I would like to remind you that there is a very big problem with mySQl as well. 3000 slow queries? That's just absolutely insane.

    7.6% of your queries are slow queries.
    Jakiao

  5. #5
    Join Date
    May 2004
    Location
    Blue Springs, Missouri
    Posts
    366
    you should look into qcache as well.

    query_cache_limit=1M
    query_cache_size=32M

    to

    query_cache_limit=8M
    query_cache_size=128M

  6. #6
    Join Date
    Jun 2003
    Location
    Janesville, Wi
    Posts
    1,516
    Correction: 1.33% of your queries are slow queries. Still a significant number of slow queries.

    omaha.stylee: I can't see how you could justify 128M of query cache. On my server, doing 500 queries per second, we only use 30M of the memory. I'm certain that at 34 queries per second, he/she will NEVER hit 30M.

    kiler: Run the following and print us the output, please.

    mysqladmin extended-status --user=username --password=password

    Replace "username" and "password" with the root user/pass.
    Jakiao

  7. #7
    Join Date
    May 2004
    Location
    Blue Springs, Missouri
    Posts
    366
    Jakiao, you're very correct.

    I'd never really looked into much, but after checking on my server this is what I had with a show status; in mysql

    | Questions | 9154638 |
    | Qcache_queries_in_cache | 15820 |
    | Qcache_inserts | 1604366 |
    | Qcache_hits | 5976000 |
    | Qcache_lowmem_prunes | 521211 |
    | Qcache_not_cached | 168666 |
    | Qcache_free_memory | 12677792 |

    but looks like I'm getting 65% from the qcache, which isn't bad ...

  8. #8
    Join Date
    Jun 2003
    Location
    Janesville, Wi
    Posts
    1,516
    Yeah, query_cache is only useful when you're working with static data within a database. A forum, for instance, will only cache into qcache posts and such. However, if you're working with a table that is constantly having its rows updated, etc, query_cache is pointless and will help you not one bit.

    kiler: One thing you want to keep a close eye on are various ratios in the mySQL extended status. My below examples will be quoted for ease of understanding.

    Ratio 1:

    You want to watch the "Table_locks_immediate" to the "Table_locks_waited".

    On my mySQL server, a ratio of about 60 immediate locks to 1 locks waited is what my server will best perform at due ot the content processed on the server.

    | Table_locks_immediate | 7849106 |
    | Table_locks_waited | 138732 |

    The higher that number, the better. A GREAT way to get the ratio higher is to modify /etc/init.d/mysqld and change the following line:

    $bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file >/dev/null 2>&1 &

    Your line may or may not appear that way, so best just to search for "$bindir/mysqld_safe"

    Change it to:

    $bindir/mysqld_safe --low-priority-updates --datadir=$datadir --pid-file=$server_pid_file >/dev/null 2>&1 &

    I found that adding "--low-priority-updates" creates a substantial increase in that ratio and an overall better performance of mySQL.
    Ratio 2:

    The next ratio to pay attention to is the key_read_requests and key_reads. The higher that number, the better as well. The fewer key_reads you have, the better your mySQL server is performing.

    | Key_read_requests | 987812648 |
    | Key_reads | 2609204 |
    Those are the two major ratios you want to keep an eye on.

    Another way to increase mySQL performance is through tuning of my.cnf. During your peak time of day, start playing with various configuration settings such as thread_concurrency. The recommended value is 2*number of CPU's, but I've found that 4*CPU's is possible. Change the values and see how your server load acts after each change.

    Good luck!
    Jakiao

  9. #9
    Join Date
    Jul 2005
    Posts
    94
    My website uses a static database which is updated everytime the game adds more items. The forums are dynamic. Both the website and forums use the same database.

    Below is three mysqladmin extended-status queries at different times.

    [b]Serverload: 20.15
    +----------------------------+-----------+
    | Variable_name | Value |
    +----------------------------+-----------+
    | Aborted_clients | 8 |
    | Aborted_connects | 78 |
    | Binlog_cache_disk_use | 0 |
    | Binlog_cache_use | 0 |
    | Bytes_received | 746933065 |
    | Bytes_sent | 201987336 |
    | 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 | 240066 |
    | 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 | 10108 |
    | 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 | 12747 |
    | 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 | 64243 |
    | 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 | 610606 |
    | Com_set_option | 18 |
    | Com_show_binlog_events | 0 |
    | Com_show_binlogs | 1 |
    | Com_show_charsets | 9 |
    | Com_show_collations | 9 |
    | Com_show_column_types | 0 |
    | Com_show_create_db | 1 |
    | Com_show_create_table | 4 |
    | Com_show_databases | 5 |
    | Com_show_errors | 0 |
    | Com_show_fields | 3 |
    | Com_show_grants | 1 |
    | Com_show_innodb_status | 0 |
    | Com_show_keys | 2 |
    | 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 | 114 |
    | Com_show_slave_hosts | 0 |
    | Com_show_slave_status | 0 |
    | Com_show_status | 10 |
    | Com_show_storage_engines | 0 |
    | Com_show_tables | 23 |
    | Com_show_variables | 18 |
    | 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 | 40579 |
    | Com_update_multi | 0 |
    | Connections | 237899 |
    | Created_tmp_disk_tables | 2614 |
    | Created_tmp_files | 7 |
    | Created_tmp_tables | 5019 |
    | Delayed_errors | 0 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Flush_commands | 1 |
    | Handler_commit | 0 |
    | Handler_delete | 16023 |
    | Handler_discover | 0 |
    | Handler_read_first | 26840 |
    | Handler_read_key | 3189915 |
    | Handler_read_next | 10643744 |
    | Handler_read_prev | 77971 |
    | Handler_read_rnd | 462879 |
    | Handler_read_rnd_next | 36402605 |
    | Handler_rollback | 0 |
    | Handler_update | 1006170 |
    | Handler_write | 226391 |
    | Key_blocks_not_flushed | 0 |
    | Key_blocks_unused | 924 |
    | Key_blocks_used | 6759 |
    | Key_read_requests | 9868065 |
    | Key_reads | 101446 |
    | Key_write_requests | 198328 |
    | Key_writes | 118140 |
    | Max_used_connections | 101 |
    | Not_flushed_delayed_rows | 0 |
    | Open_files | 83 |
    | Open_streams | 0 |
    | Open_tables | 64 |
    | Opened_tables | 110240 |
    | 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 | 1216369 |
    | Rpl_status | NULL |
    | Select_full_join | 5 |
    | Select_full_range_join | 0 |
    | Select_range | 80017 |
    | Select_range_check | 0 |
    | Select_scan | 82096 |
    | Slave_open_temp_tables | 0 |
    | Slave_retried_transactions | 0 |
    | Slave_running | OFF |
    | Slow_launch_threads | 164 |
    | Slow_queries | 27855 |
    | Sort_merge_passes | 2 |
    | Sort_range | 271148 |
    | Sort_rows | 774298 |
    | Sort_scan | 6245 |
    | Table_locks_immediate | 730391 |
    | Table_locks_waited | 45609 |
    | Threads_cached | 0 |
    | Threads_connected | 6 |
    | Threads_created | 237898 |
    | Threads_running | 1 |
    | Uptime | 45168 |
    +----------------------------+-----------+
    Serverload: 25.29

    +----------------------------+-----------+
    | Variable_name | Value |
    +----------------------------+-----------+
    | Aborted_clients | 8 |
    | Aborted_connects | 78 |
    | Binlog_cache_disk_use | 0 |
    | Binlog_cache_use | 0 |
    | Bytes_received | 758226011 |
    | Bytes_sent | 670856502 |
    | 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 | 244167 |
    | 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 | 10408 |
    | 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 | 13031 |
    | 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 | 65270 |
    | 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 | 618819 |
    | Com_set_option | 18 |
    | Com_show_binlog_events | 0 |
    | Com_show_binlogs | 1 |
    | Com_show_charsets | 9 |
    | Com_show_collations | 9 |
    | Com_show_column_types | 0 |
    | Com_show_create_db | 1 |
    | Com_show_create_table | 4 |
    | Com_show_databases | 5 |
    | Com_show_errors | 0 |
    | Com_show_fields | 3 |
    | Com_show_grants | 1 |
    | Com_show_innodb_status | 0 |
    | Com_show_keys | 2 |
    | 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 | 114 |
    | Com_show_slave_hosts | 0 |
    | Com_show_slave_status | 0 |
    | Com_show_status | 11 |
    | Com_show_storage_engines | 0 |
    | Com_show_tables | 23 |
    | Com_show_variables | 18 |
    | 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 | 41271 |
    | Com_update_multi | 0 |
    | Connections | 242001 |
    | Created_tmp_disk_tables | 2614 |
    | Created_tmp_files | 7 |
    | Created_tmp_tables | 5020 |
    | Delayed_errors | 0 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Flush_commands | 1 |
    | Handler_commit | 0 |
    | Handler_delete | 16247 |
    | Handler_discover | 0 |
    | Handler_read_first | 27100 |
    | Handler_read_key | 3222094 |
    | Handler_read_next | 10778866 |
    | Handler_read_prev | 80444 |
    | Handler_read_rnd | 466854 |
    | Handler_read_rnd_next | 36974292 |
    | Handler_rollback | 0 |
    | Handler_update | 1007856 |
    | Handler_write | 227702 |
    | Key_blocks_not_flushed | 0 |
    | Key_blocks_unused | 640 |
    | Key_blocks_used | 6779 |
    | Key_read_requests | 9993807 |
    | Key_reads | 102205 |
    | Key_write_requests | 201342 |
    | Key_writes | 120975 |
    | Max_used_connections | 101 |
    | Not_flushed_delayed_rows | 0 |
    | Open_files | 76 |
    | Open_streams | 0 |
    | Open_tables | 64 |
    | Opened_tables | 110751 |
    | 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 | 1234994 |
    | Rpl_status | NULL |
    | Select_full_join | 5 |
    | Select_full_range_join | 0 |
    | Select_range | 81488 |
    | Select_range_check | 0 |
    | Select_scan | 83285 |
    | Slave_open_temp_tables | 0 |
    | Slave_retried_transactions | 0 |
    | Slave_running | OFF |
    | Slow_launch_threads | 164 |
    | Slow_queries | 28034 |
    | Sort_merge_passes | 2 |
    | Sort_range | 273860 |
    | Sort_rows | 782272 |
    | Sort_scan | 6256 |
    | Table_locks_immediate | 740889 |
    | Table_locks_waited | 46227 |
    | Threads_cached | 0 |
    | Threads_connected | 101 |
    | Threads_created | 242000 |
    | Threads_running | 83 |
    | Uptime | 45751 |
    +----------------------------+-----------+
    Serverload: 42.33

    +----------------------------+-----------+
    | Variable_name | Value |
    +----------------------------+-----------+
    | Aborted_clients | 8 |
    | Aborted_connects | 78 |
    | Binlog_cache_disk_use | 0 |
    | Binlog_cache_use | 0 |
    | Bytes_received | 758760379 |
    | Bytes_sent | 675810654 |
    | 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 | 244240 |
    | 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 | 10413 |
    | 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 | 13040 |
    | 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 | 65329 |
    | 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 | 619087 |
    | Com_set_option | 18 |
    | Com_show_binlog_events | 0 |
    | Com_show_binlogs | 1 |
    | Com_show_charsets | 9 |
    | Com_show_collations | 9 |
    | Com_show_column_types | 0 |
    | Com_show_create_db | 1 |
    | Com_show_create_table | 4 |
    | Com_show_databases | 5 |
    | Com_show_errors | 0 |
    | Com_show_fields | 3 |
    | Com_show_grants | 1 |
    | Com_show_innodb_status | 0 |
    | Com_show_keys | 2 |
    | 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 | 114 |
    | Com_show_slave_hosts | 0 |
    | Com_show_slave_status | 0 |
    | Com_show_status | 12 |
    | Com_show_storage_engines | 0 |
    | Com_show_tables | 23 |
    | Com_show_variables | 18 |
    | 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 | 41287 |
    | Com_update_multi | 0 |
    | Connections | 242076 |
    | Created_tmp_disk_tables | 2614 |
    | Created_tmp_files | 7 |
    | Created_tmp_tables | 5020 |
    | Delayed_errors | 0 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Flush_commands | 1 |
    | Handler_commit | 0 |
    | Handler_delete | 16248 |
    | Handler_discover | 0 |
    | Handler_read_first | 27100 |
    | Handler_read_key | 3222749 |
    | Handler_read_next | 10785967 |
    | Handler_read_prev | 80506 |
    | Handler_read_rnd | 467020 |
    | Handler_read_rnd_next | 37010314 |
    | Handler_rollback | 0 |
    | Handler_update | 1007927 |
    | Handler_write | 227767 |
    | Key_blocks_not_flushed | 0 |
    | Key_blocks_unused | 659 |
    | Key_blocks_used | 6779 |
    | Key_read_requests | 9997706 |
    | Key_reads | 102264 |
    | Key_write_requests | 201398 |
    | Key_writes | 121027 |
    | Max_used_connections | 101 |
    | Not_flushed_delayed_rows | 0 |
    | Open_files | 77 |
    | Open_streams | 0 |
    | Open_tables | 64 |
    | Opened_tables | 110901 |
    | 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 | 1235501 |
    | Rpl_status | NULL |
    | Select_full_join | 5 |
    | Select_full_range_join | 0 |
    | Select_range | 81507 |
    | Select_range_check | 0 |
    | Select_scan | 83350 |
    | Slave_open_temp_tables | 0 |
    | Slave_retried_transactions | 0 |
    | Slave_running | OFF |
    | Slow_launch_threads | 165 |
    | Slow_queries | 28140 |
    | Sort_merge_passes | 2 |
    | Sort_range | 273986 |
    | Sort_rows | 782632 |
    | Sort_scan | 6256 |
    | Table_locks_immediate | 741237 |
    | Table_locks_waited | 46257 |
    | Threads_cached | 0 |
    | Threads_connected | 100 |
    | Threads_created | 242075 |
    | Threads_running | 77 |
    | Uptime | 45807 |
    +----------------------------+-----------+
    Now, I will show you the show status; command.

    Serverload: 48.53

    +----------------------------+-----------+
    | Variable_name | Value |
    +----------------------------+-----------+
    | Aborted_clients | 8 |
    | Aborted_connects | 78 |
    | Binlog_cache_disk_use | 0 |
    | Binlog_cache_use | 0 |
    | Bytes_received | 762585813 |
    | Bytes_sent | 809205809 |
    | 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 | 245490 |
    | 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 | 10520 |
    | 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 | 13124 |
    | 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 | 65723 |
    | 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 | 623114 |
    | Com_set_option | 18 |
    | Com_show_binlog_events | 0 |
    | Com_show_binlogs | 1 |
    | Com_show_charsets | 9 |
    | Com_show_collations | 9 |
    | Com_show_column_types | 0 |
    | Com_show_create_db | 1 |
    | Com_show_create_table | 4 |
    | Com_show_databases | 5 |
    | Com_show_errors | 0 |
    | Com_show_fields | 3 |
    | Com_show_grants | 1 |
    | Com_show_innodb_status | 0 |
    | Com_show_keys | 2 |
    | 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 | 114 |
    | Com_show_slave_hosts | 0 |
    | Com_show_slave_status | 0 |
    | Com_show_status | 13 |
    | Com_show_storage_engines | 0 |
    | Com_show_tables | 23 |
    | Com_show_variables | 18 |
    | 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 | 41425 |
    | Com_update_multi | 0 |
    | Connections | 243330 |
    | Created_tmp_disk_tables | 2615 |
    | Created_tmp_files | 7 |
    | Created_tmp_tables | 5022 |
    | Delayed_errors | 0 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Flush_commands | 1 |
    | Handler_commit | 0 |
    | Handler_delete | 16325 |
    | Handler_discover | 0 |
    | Handler_read_first | 27151 |
    | Handler_read_key | 3232922 |
    | Handler_read_next | 10865430 |
    | Handler_read_prev | 80774 |
    | Handler_read_rnd | 469696 |
    | Handler_read_rnd_next | 37380097 |
    | Handler_rollback | 0 |
    | Handler_update | 1008596 |
    | Handler_write | 228271 |
    | Key_blocks_not_flushed | 0 |
    | Key_blocks_unused | 554 |
    | Key_blocks_used | 6779 |
    | Key_read_requests | 10049261 |
    | Key_reads | 102756 |
    | Key_write_requests | 202193 |
    | Key_writes | 121779 |
    | Max_used_connections | 101 |
    | Not_flushed_delayed_rows | 0 |
    | Open_files | 79 |
    | Open_streams | 0 |
    | Open_tables | 64 |
    | Opened_tables | 111455 |
    | 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 | 1242795 |
    | Rpl_status | NULL |
    | Select_full_join | 5 |
    | Select_full_range_join | 0 |
    | Select_range | 81881 |
    | Select_range_check | 0 |
    | Select_scan | 83846 |
    | Slave_open_temp_tables | 0 |
    | Slave_retried_transactions | 0 |
    | Slave_running | OFF |
    | Slow_launch_threads | 166 |
    | Slow_queries | 28418 |
    | Sort_merge_passes | 2 |
    | Sort_range | 276455 |
    | Sort_rows | 787510 |
    | Sort_scan | 6261 |
    | Table_locks_immediate | 745769 |
    | Table_locks_waited | 46627 |
    | Threads_cached | 0 |
    | Threads_connected | 59 |
    | Threads_created | 243330 |
    | Threads_running | 35 |
    | Uptime | 46076 |
    +----------------------------+-----------+
    After the error message "too many connections", I ran the command mysqladmin status and the following showed:

    Uptime: 46335 Threads: 95 Questions: 1244137 Slow queries: 28847 Opens: 1121
    12 Flush tables: 1 Open tables: 64 Queries per second avg: 26.851
    Thanks
    -Kiler
    Last edited by kiler; 11-28-2005 at 12:37 AM.

  10. #10
    Join Date
    Jun 2003
    Location
    Janesville, Wi
    Posts
    1,516
    Okay, add the following into your my.cnf file (or edit the existing lines if these already exist):

    thread_cache = 24
    query_cache_type = 1
    query_prealloc_size = 32M
    query_cache_size = 32M
    If you're getting the too many connections error, do what I do: max_connections = 500

    Also, add the --low-priority-updates into the mysqld init file like stated in "Ratio 1" of my last post. Restart mySQL. This should dramatically help you.

    Finally, consider using mySQL persistant connections (in PHP, this is mysql_pconnect() instead of mysql_connect()). On my most active sites, using mysql_pconnect() gave me an INCREDIBLE performance boost with mySQL.

    ----------

    Back to Apache, could you tell me what the following values are for your Apache config (httpd.conf):

    Timeout
    KeepAlive
    MaxKeepAliveRequests
    KeepAliveTimeout
    MinSpareServers
    MaxSpareServers
    StartServers
    MaxClients
    MaxRequestsPerChild
    Last edited by Jakiao; 11-28-2005 at 01:00 AM.
    Jakiao

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •