MySQL Server Configuration Help and Bad Performance

[Gamingforce]

Hey everyone, we recently switched from 2 Redhat Linux Servers running Dual Tualatin 1.26 GHz to 2 FreeBSD Dual AMD Athlon MP 2000+ Servers. After the server move, things appear to be running much slower than before. It would be awesome if anyone can help with my server configuration.

Although the board is pretty quick when I'm logged out, once I'm logged in, page load times are 8-20 seconds long and post replies take a very very long time.

[ Server Specs ]
2 Servers (One for Apache, One for MySQL) each with:
Dual AMD Athlon MP 2000+ Processors
2 GB DDR Memory
3 15000k RPM SCSI Drives in Raid5
Mylex Acceleraid Card
FreeBSD 4.5
Apache 1.3.26
PHP 4.2.2
MySQL 3.23.51

Server Information: http://xelia.xarcos.com/phpSysInfo/
PHP Information: http://xelia.xarcos.com/phpinfo2.php

The database and web server are connected to each other on a separate private NIC at 100 Mbits.

[ How MySQL Was Compiled ]
We downloaded the source from mysql.com and did a standard compile.

[ Top Stats ]
Web Server:
last pid: 94819; load averages: 0.48, 0.60, 0.58 up 4+09:57:17 18:52:49
88 processes: 2 running, 83 sleeping, 3 zombie
CPU states: 5.6% user, 0.0% nice, 0.8% system, 0.6% interrupt, 93.0% idle
Mem: 179M Active, 1324M Inact, 224M Wired, 91M Cache, 199M Buf, 192M Free
Swap: 257M Total, 257M Free



Database Server:
last pid: 846; load averages: 0.57, 0.49, 0.48 up 0+10:23:46 18:53:40
21 processes: 3 running, 18 sleeping
CPU states: 32.8% user, 0.0% nice, 17.2% system, 0.0% interrupt, 50.0% idle
Mem: 100M Active, 643M Inact, 226M Wired, 8K Cache, 199M Buf, 1040M Free
Swap: 257M Total, 257M Free



[ MySQL Configuration: my.cnf ]
[mysql.server]
user=mysql
basedir=/usr/local/mysql

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
datadir=/usr/local/mysql/data
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=390
set-variable = key_buffer=256M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=4M
set-variable = record_buffer=4M
set-variable = sort_buffer=5M
set-variable = table_cache=1024
set-variable = thread_cache_size=512

[myisamchk]
set-variable = key_buffer=96M
set-variable = sort_buffer=96M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

[ Extended Status Output ]
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 23 |
| Aborted_connects | 127 |
| Bytes_received | 225239436 |
| Bytes_sent | 3620073707 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 88064 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 4921 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 8112 |
| Com_insert_select | 424 |
| 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 | 1732 |
| Com_replace_select | 10 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 1418864 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 3 |
| 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 | 79674 |
| Com_show_tables | 21 |
| Com_show_variables | 16 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 131829 |
| Connections | 8486 |
| Created_tmp_disk_tables | 182 |
| Created_tmp_tables | 25984 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 15250 |
| Handler_read_first | 44593 |
| Handler_read_key | 12655152 |
| Handler_read_next | 202148857 |
| Handler_read_prev | 16675 |
| Handler_read_rnd | 13477579 |
| Handler_read_rnd_next | 2630742744 |
| Handler_update | 904954 |
| Handler_write | 2746695 |
| Key_blocks_used | 47336 |
| Key_read_requests | 67312041 |
| Key_reads | 46826 |
| Key_write_requests | 88812 |
| Key_writes | 82276 |
| Max_used_connections | 131 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 669 |
| Open_files | 712 |
| Open_streams | 0 |
| Opened_tables | 675 |
| Questions | 1662263 |
| Select_full_join | 106 |
| Select_full_range_join | 0 |
| Select_range | 203293 |
| Select_range_check | 0 |
| Select_scan | 211024 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 362 |
| Sort_merge_passes | 0 |
| Sort_range | 196486 |
| Sort_rows | 17192301 |
| Sort_scan | 98713 |
| Table_locks_immediate | 1727679 |
| Table_locks_waited | 19269 |
| Threads_cached | 61 |
| Threads_created | 132 |
| Threads_connected | 71 |
| Threads_running | 3 |
| Uptime | 55220 |
+--------------------------+------------+

[ Other Scripts? Sites ]
vBulletin is the only main commercial script running on my site. It's modified though and I have a lot of custom coded scripts running on there but the performance impact should be small. The only site on is gamingforce.com.

[ Average/Max Users ]
We average around 120-150 users per hour and peak at around 170-180.

[ Apache Variables ]
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 256
MaxRequestsPerChild 0

Thanks a lot for your help everyone! :) I have been running vBulletin since the 1.1.4 I think and I don't think it has been running this slow ever since. Maybe switching to Athlon MPs was a bad idea? From benchmarks Athlons seemed much faster in both database and web transactions than even Pentium3 Xeons.

[Raz Meister]

I'm sure Eva will help you out with the number you need, but here are a few pointers else where in your server config.

I noticed you have 'resolve ips' enabled. You should consider disabling this in your httpd.conf as its unnecassary load.

Also consider disabling mod_gzip and using PHP's built in gzipper. mod_gzip is known to have memory problems.

And finally consider installing PHPA (http://www.php-accelerator.co.uk) to speed up your PHP files :)

[eva2000]

okay snipped some top info you don't really need ...

nice servers... very close specs to my own minus the raid and 15k scsis :)

1. i'd reduce your maxclients from 256 to 150
2. not necessary but i'd recommend raid 0+1 instead of raid 5 for performance on the database server of course that means an extra disk needed
3. install PHPA 1.3.2 on the web server - install guide at http://i4net.tv/marticle/get.php?action=getarticle&articleid=31
4. change my.cnf and restart mysql after to


[mysql.server]
user=mysql
basedir=/usr/local/mysql

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
datadir=/usr/local/mysql/data
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=400
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=3M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=7200
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[myisamchk]
set-variable = key_buffer=96M
set-variable = sort_buffer=96M
set-variable = read_buffer=16M
set-variable = write_buffer=16M


5. instaling mod_gzip is actually good http://i4net.tv/marticle/get.php?action=getarticle&articleid=12 no memory problems on my old server

Dual P3 866
768MB ECC PC133 RAM
18GB 10K SCSI

or on my new server

Dual AthlonMP 2000+
2GB ECC PC2100 DDRAM
2 x 18GB SCSI 10k (2nd disk for mysql data)
1 x 80GB EIDE

[Gamingforce]

I'm a huge fan of PHPA lol and have been using it for about a year now. I was just waiting for the next release Nick said would be released this week and will be officially compatible with PHP 4.2.2. Is mod_gzip bad? I liked it because it compressed html and other document types as well. Thanks for your help Razzie!

[eva2000]

yup PHP 4.2.2 supported from the downloads page http://www.php-accelerator.co.uk/download.php :)

mod_gzip has been fine on all the dedicated servers i've worked with to date :)

unfortunately looks like PHP 1.3.2 isn't available for my own PHP 4.1.2 with glibc 2.2.4 as yet :(

[Gamingforce]

Thanks for your help Eva! I'll follow up on the speed after running the new configuration for a bit. :)

One question: How do you disable the resolution of IPs in Apache?

[eva2000]

Originally posted by Gamingforce
Thanks for your help Eva! I'll follow up on the speed after running the new configuration for a bit. :)

One question: How do you disable the resolution of IPs in Apache? in httpd.conf

#
# HostnameLookups: Log the names of clients or just their IP addresses
# e.g., www.apache.org (on) or 204.62.129.132 (off).
# The default is off because it'd be overall better for the net if people
# had to knowingly turn this feature on, since enabling it means that
# each client request will result in AT LEAST one lookup request to the
# nameserver.
#
HostnameLookups Off

[DBs]

Hi

I suspect no amount of tweaking PHP/Apache will help in this instance.

MySQL and FreeBSD don't make a good mix due to issues with BSD and the threading library MySQL use.

Read this : http://www.mysql.com/doc/F/r/FreeBSD.html

[Gamingforce]

Should I revert to Redhat 7 for the MySQL end?

[DBs]

Thats down to personal preference.

All our systems run on stripped down Redhat 7. Before you go down this path, read that page and see what happens if you recompile.

[eva2000]

Originally posted by Gamingforce
Should I revert to Redhat 7 for the MySQL end? yes i've noticed with support i handle FreeBSD systems tend to have a few more problems with mysql than RH

i'd go RH 7.3 with 2.4.18-5 or later smp kernel

[DBs]

Originally posted by eva2000
yes i've noticed with support i handle FreeBSD systems tend to have a few more problems with mysql than RH

i'd go RH 7.3 with 2.4.18-5 or later smp kernel

Yes, and the "performance" gap between the 2 os' is now very small and indeed for some applications Linux is faster than BSD.

I don't want to get into an OS bashing war as I just look at the app and decide on the best OS for the job.

Which is why we run NT4,W2k,RedHat,FreeBSD systems at BarrysWorld.