mySQL slow : is it my configuration or is my machine put to its limits?
Since a couple of weeks, my webserver is acting strange. It is hosting quite a few websites, running PHP+mySQL. About 50 visitors use the server concurrently. This was not a problem during last year, but last month the server is serving pages slowly. The number of mysql processes is increasing to 30 or so, while this was not the case a couple of months ago. Then it was 2 or 5 or so. Also, by debugging some test-php-pages, I found that total processing time of a single php-test-file.php took about 8 seconds of mysql time and 9 seconds in total.
My machine: Pentium 4 2,4 ghz 1gb ram
Is it my configuration or is my machine put to its limits?
Please take a look at my configuration files below. Is something strange in it??
Thanks in advance for any suggestions!
Jackaroo
[mysql@foobar /root]$ mysqladmin -u root -p extended-status
Enter password:
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| Aborted_clients | 8 |
| Aborted_connects | 2 |
| Bytes_received | 1582805 |
| Bytes_sent | 25600881 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 2 |
| Com_change_db | 6718 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 2 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 398 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 140 |
| Com_insert_select | 2 |
| Com_kill | 0 |
| Com_load | 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 | 277 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 9988 |
| Com_set_option | 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_open_tables | 0 |
| Com_show_processlist | 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 | 956 |
| Connections | 1066 |
| Created_tmp_disk_tables | 177 |
| Created_tmp_tables | 381 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 79 |
| Handler_read_first | 1070 |
| Handler_read_key | 2781702 |
| Handler_read_next | 1458708 |
| Handler_read_prev | 16358 |
| Handler_read_rnd | 141468 |
| Handler_read_rnd_next | 7473423 |
| Handler_update | 1202536 |
| Handler_write | 1640622 |
| Key_blocks_used | 7793 |
| Key_read_requests | 5640256 |
| Key_reads | 5950 |
| Key_write_requests | 170173 |
| Key_writes | 750 |
| Max_used_connections | 50 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 85 |
| Open_streams | 0 |
| Opened_tables | 2198 |
| Questions | 19494 |
| Select_full_join | 67 |
| Select_full_range_join | 6 |
| Select_range | 443 |
| Select_range_check | 0 |
| Select_scan | 2607 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 60 |
| Sort_merge_passes | 0 |
| Sort_range | 969 |
| Sort_rows | 339671 |
| Sort_scan | 970 |
| Table_locks_immediate | 13106 |
| Table_locks_waited | 542 |
| Threads_cached | 0 |
| Threads_created | 1065 |
| Threads_connected | 48 |
| Threads_running | 46 |
| Uptime | 349 |
/my.cnf :
# Example mysql config file for very large systems.
#
# This is for large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/mf.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/var) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
[safe_mysqld]
open-files-limit=1024
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
#skip-show-database
safe-show-database
set-variable = key_buffer=384M
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=4
set-variable = myisam_sort_buffer_size=64M
## >start >
set-variable = thread_concurrency=4
set-variable = max_connections=1014
set-variable = max_user_connections=1024
set-variable = max_connect_errors=999999999
set-variable = wait_timeout=30
#log-bin
## <end <
server-id = 1
# Point the following paths to different dedicated disks
## >start >
tmpdir = /tmp/
## <end <
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=384M
#set-variable = bdb_max_lock=100000
# Uncomment the following if you are using Innobase tables
#innodb_data_file_path = ibdata1:2000M;ibdata2:2000M
#innodb_data_home_dir = /usr/local/mysql/var/
#innodb_log_group_home_dir = /usr/local/mysql/var/
#innodb_log_arch_dir = /usr/local/mysql/var/
#set-variable = innodb_mirrored_log_groups=1
#set-variable = innodb_log_files_in_group=3
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
#no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[myisamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[mysqlhotcopy]
interactive-timeout
[mysql@foobar /root]$ ps -aux
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
mysql 31607 0.1 1.7 20160 17968 pts/1 S 17:36 0:00 /usr/local/mysql/libexec/mysqld
mysql 31608 0.0 1.7 20160 17968 pts/1 R 17:36 0:00 /usr/local/mysql/libexec/mysqld
mysql 31609 0.0 1.7 20160 17968 pts/1 S 17:36 0:00 /usr/local/mysql/libexec/mysqld
mysql 723 8.8 1.7 20160 17968 pts/1 R 17:42 0:02 /usr/local/mysql/libexec/mysqld
mysql 729 9.6 1.7 20160 17968 pts/1 R 17:42 0:03 /usr/local/mysql/libexec/mysqld
mysql 730 9.4 1.7 20160 17968 pts/1 R 17:42 0:03 /usr/local/mysql/libexec/mysqld
mysql 745 7.9 1.7 20160 17968 pts/1 R 17:42 0:02 /usr/local/mysql/libexec/mysqld
mysql 748 7.6 1.7 20160 17968 pts/1 R 17:42 0:02 /usr/local/mysql/libexec/mysqld
mysql 752 5.6 1.7 20160 17968 pts/1 R 17:42 0:01 /usr/local/mysql/libexec/mysqld
mysql 753 6.2 1.7 20160 17968 pts/1 R 17:42 0:01 /usr/local/mysql/libexec/mysqld
mysql 759 0.0 1.7 20160 17968 pts/1 S 17:42 0:00 /usr/local/mysql/libexec/mysqld
mysql 760 5.5 1.7 20160 17968 pts/1 R 17:42 0:01 /usr/local/mysql/libexec/mysqld
mysql 763 0.0 1.7 20160 17968 pts/1 S 17:42 0:00 /usr/local/mysql/libexec/mysqld
mysql 778 0.0 1.7 20160 17968 pts/1 S 17:42 0:00 /usr/local/mysql/libexec/mysqld
mysql 780 0.0 1.7 20160 17968 pts/1 S 17:42 0:00 /usr/local/mysql/libexec/mysqld
mysql 781 0.0 1.7 20160 17968 pts/1 S 17:42 0:00 /usr/local/mysql/libexec/mysqld
mysql 786 0.1 1.7 20160 17968 pts/1 S 17:42 0:00 /usr/local/mysql/libexec/mysqld
mysql 807 0.2 1.7 20160 17968 pts/1 S 17:42 0:00 /usr/local/mysql/libexec/mysqld
mysql 808 0.0 1.7 20160 17968 pts/1 S 17:42 0:00 /usr/local/mysql/libexec/mysqld
mysql 810 0.1 1.7 20160 17968 pts/1 S 17:42 0:00 /usr/local/mysql/libexec/mysqld
mysql 811 0.2 1.7 20160 17968 pts/1 S 17:42 0:00 /usr/local/mysql/libexec/mysqld
mysql 814 0.4 1.7 20160 17968 pts/1 S 17:42 0:00 /usr/local/mysql/libexec/mysqld
mysql 816 0.0 1.7 20160 17968 pts/1 S 17:42 0:00 /usr/local/mysql/libexec/mysqld
mysql 827 0.0 1.7 20160 17968 pts/1 S 17:43 0:00 /usr/local/mysql/libexec/mysqld
mysql 841 0.6 1.7 20160 17968 pts/1 S 17:43 0:00 /usr/local/mysql/libexec/mysqld
mysql 842 0.4 1.7 20160 17968 pts/1 S 17:43 0:00 /usr/local/mysql/libexec/mysqld
mysql 844 0.4 0.0 1716 944 pts/1 S 17:43 0:00 bash
mysql 862 0.7 1.7 20160 17968 pts/1 S 17:43 0:00 /usr/local/mysql/libexec/mysqld
mysql 872 0.5 1.7 20160 17968 pts/1 S 17:43 0:00 /usr/local/mysql/libexec/mysqld
mysql 878 3.5 1.7 20160 17968 pts/1 R 17:43 0:00 /usr/local/mysql/libexec/mysqld
mysql 879 1.0 1.7 20160 17968 pts/1 S 17:43 0:00 /usr/local/mysql/libexec/mysqld
mysql 881 0.0 1.7 20160 17968 pts/1 S 17:43 0:00 /usr/local/mysql/libexec/mysqld
mysql 882 0.0 1.7 20160 17968 pts/1 S 17:43 0:00 /usr/local/mysql/libexec/mysqld
mysql 883 0.0 1.7 20160 17968 pts/1 S 17:43 0:00 /usr/local/mysql/libexec/mysqld
mysql 884 0.0 1.7 20160 17968 pts/1 S 17:43 0:00 /usr/local/mysql/libexec/mysqld
mysql 885 0.0 1.7 20160 17968 pts/1 R 17:43 0:00 /usr/local/mysql/libexec/mysqld
mysql 886 0.0 0.0 2544 856 pts/1 R 17:43 0:00 ps -aux
