Hi,
Our main database server is a 2 x PIII 1.2Ghz with 1.5GB RAM and a 73GB SCSI RAID-5
made up of 3 36GB disks. It does between 300 and
1200 queries per second. The read to write ratio is about 4:1.
My problem is that we're hitting our max_connections more and more frequently. Is it
safe to raise this to (say) 200, or will this
cause performance problems? Already the machine is using up a lot of swap; would you
recommend that I bump the RAM up to 2GB, or
should I bring down the key_buffer_size in order to fit everything into physical RAM?
Should I be concerned about the load average
of the machine - it goes up to 6 at some points in the day.
Are there any other performance tips that anyone can give based on this configuration?
If you need more information, please let me
know.
Here is the output from top (at a fairly busy time of day):
1:39pm up 224 days, 8:09, 8 users, load average: 2.20, 3.37, 3.44
101 processes: 96 sleeping, 5 running, 0 zombie, 0 stopped
CPU0 states: 16.0% user, 60.1% system, 0.0% nice, 22.1% idle
CPU1 states: 18.0% user, 72.1% system, 0.0% nice, 8.1% idle
Mem: 1545040K av, 1531936K used, 13104K free, 0K shrd, 17048K buff
Swap: 2061428K av, 421876K used, 1639552K free 1016380K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
17727 root 18 0 1064 1060 824 R 31.5 0.0 0:01 top
17733 mysql 10 0 426M 385M 356M S 4.3 25.5 0:00 mysqld
17634 mysql 9 0 426M 385M 356M S 2.1 25.5 0:00 mysqld
17720 mysql 9 0 427M 386M 356M S 2.1 25.5 0:00 mysqld
17746 mysql 10 0 426M 385M 356M S 2.1 25.5 0:00 mysqld
15257 mysql 9 0 426M 385M 356M R 1.6 25.5 360:19 mysqld
17725 mysql 9 0 427M 386M 356M S 1.6 25.5 0:00 mysqld
17730 mysql 9 0 426M 385M 356M S 1.6 25.5 0:00 mysqld
17741 mysql 9 0 426M 385M 356M S 1.6 25.5 0:00 mysqld
17750 mysql 9 0 426M 385M 356M S 1.0 25.5 0:00 mysqld
4 root 19 19 0 0 0 RWN 0.5 0.0 7:07 ksoftirqd_CPU1
15260 mysql 9 0 426M 385M 356M S 0.5 25.5 88:46 mysqld
29177 root 6 0 740 696 560 S 0.5 0.0 0:17 watch
17654 mysql 9 0 427M 386M 356M S 0.5 25.5 0:00 mysqld
17717 mysql 9 0 426M 385M 356M S 0.5 25.5 0:00 mysqld
17718 mysql 9 0 427M 386M 356M S 0.5 25.5 0:00 mysqld
17734 mysql 9 0 426M 385M 356M S 0.5 25.5 0:00 mysqld
17745 mysql 9 0 426M 385M 356M S 0.5 25.5 0:00 mysqld
17749 mysql 9 0 426M 385M 356M S 0.5 25.5 0:00 mysqld
1 root 9 0 488 440 424 S 0.0 0.0 3:24 init
2 root 9 0 0 0 0 SW 0.0 0.0 0:05 keventd
3 root 19 19 0 0 0 RWN 0.0 0.0 7:20 ksoftirqd_CPU0
5 root 9 0 0 0 0 SW 0.0 0.0 93:38 kswapd
6 root 9 0 0 0 0 SW 0.0 0.0 0:00 bdflush
7 root 9 0 0 0 0 SW 0.0 0.0 14:45 kupdated
8 root 9 0 0 0 0 SW 0.0 0.0 0:00 scsi_eh_0
9 root 9 0 0 0 0 SW 0.0 0.0 0:00 scsi_eh_1
10 root -1 -20 0 0 0 SW< 0.0 0.0 0:00 mdrecoveryd
11 root 9 0 0 0 0 SW 0.0 0.0 161:25 kjournald
509 root 9 0 560 504 464 S 0.0 0.0 2:27 syslogd
514 root 9 0 456 392 392 S 0.0 0.0 0:00 klogd
713 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty
714 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty
715 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty
716 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty
717 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty
718 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty
Here is the output of SHOW VARIBLES (minus character_sets):
mysql> show variables;
+---------------------------------+---------------------------------------------------------------------------+
| Variable_name | Value
|
+---------------------------------+---------------------------------------------------------------------------+
| back_log | 200
|
| basedir | /usr/local/mysql-max-3.23.55-pc-linux-i686/
|
| bdb_cache_size | 8388600
|
| bdb_log_buffer_size | 262144
|
| bdb_home | /usr/local/mysql/data/
|
| bdb_max_lock | 10000
|
| bdb_logdir |
|
| bdb_shared_data | OFF
|
| bdb_tmpdir | /tmp/
|
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (January
21, 2003) |
| binlog_cache_size | 32768
|
| character_set | latin1
|
| concurrent_insert | ON
|
| connect_timeout | 5
|
| datadir | /usr/local/mysql/data/
|
| delay_key_write | ON
|
| delayed_insert_limit | 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| flush | OFF
|
| flush_time | 0
|
| have_bdb | YES
|
| have_gemini | NO
|
| have_innodb | DISABLED
|
| have_isam | YES
|
| have_raid | NO
|
| have_openssl | NO
|
| init_file |
|
| innodb_additional_mem_pool_size | 1048576
|
| innodb_buffer_pool_size | 8388608
|
| innodb_data_file_path |
|
| innodb_data_home_dir |
|
| innodb_file_io_threads | 4
|
| innodb_force_recovery | 0
|
| innodb_thread_concurrency | 8
|
| innodb_flush_log_at_trx_commit | 0
|
| innodb_fast_shutdown | ON
|
| innodb_flush_method |
|
| innodb_lock_wait_timeout | 50
|
| 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_mirrored_log_groups | 1
|
| interactive_timeout | 28800
|
| join_buffer_size | 131072
|
| key_buffer_size | 402649088
|
| language |
/usr/local/mysql-max-3.23.55-pc-linux-i686/share/mysql/english/ |
| large_files_support | ON
|
| locked_in_memory | OFF
|
| log | OFF
|
| log_update | OFF
|
| log_bin | ON
|
| log_slave_updates | OFF
|
| log_long_queries | OFF
|
| long_query_time | 10
|
| low_priority_updates | OFF
|
| lower_case_table_names | 0
|
| max_allowed_packet | 1047552
|
| max_binlog_cache_size | 4294967295
|
| max_binlog_size | 1073741824
|
| max_connections | 100
|
| max_connect_errors | 1000
|
| max_delayed_threads | 20
|
| max_heap_table_size | 16777216
|
| max_join_size | 4294967295
|
| max_sort_length | 1024
|
| max_user_connections | 0
|
| max_tmp_tables | 32
|
| max_write_lock_count | 4294967295
|
| myisam_max_extra_sort_file_size | 256
|
| myisam_max_sort_file_size | 2047
|
| myisam_recover_options | 0
|
| myisam_sort_buffer_size | 67108864
|
| net_buffer_length | 16384
|
| net_read_timeout | 30
|
| net_retry_count | 10
|
| net_write_timeout | 60
|
| open_files_limit | 0
|
| pid_file | /usr/local/mysql/data/db.pid
|
| port | 3306
|
| protocol_version | 10
|
| record_buffer | 2093056
|
| record_rnd_buffer | 2093056
|
| query_buffer_size | 0
|
| safe_show_database | OFF
|
| server_id | 1936
|
| slave_net_timeout | 3600
|
| skip_locking | ON
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slow_launch_time | 2
|
| socket | /tmp/mysql.sock
|
| sort_buffer | 2097144
|
| sql_mode | 0
|
| table_cache | 512
|
| table_type | MYISAM
|
| thread_cache_size | 8
|
| thread_stack | 65536
|
| transaction_isolation | READ-COMMITTED
|
| timezone | GMT
|
| tmp_table_size | 33554432
|
| tmpdir | /tmp/
|
| version | 3.23.55-max-log
|
| wait_timeout | 28800
|
+---------------------------------+---------------------------------------------------------------------------+
108 rows in set (0.00 sec)
-- Alex
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
