Server peformance (mysql downgrade?)

[olkie]

Hi all! I recently bought a dedicated server for our forum.
Problem we're facing is a high load and slow performance of the forum.

As a test I migrated the server to a less powerful server were the load was way lower, and pages ran faster.

23:25:16 up 25 days, 7:29, 2 users, load average: 5.27, 4.34, 4.63

server specs/details:

Linux removed 2.6.18-4-686 #1 SMP Mon Mar 26 17:17:36 UTC 2007 i686 GNU/Linux

Version
removed:~# cat /etc/debian_version
3.1

CPU
removed:~# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Pentium(R) 4 CPU 2.66GHz
stepping : 9
cpu MHz : 2665.609
cache size : 512 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe up cid xtpr
bogomips : 5335.15

Memory
removed:~# cat /proc/meminfo
MemTotal: 2068896 kB
MemFree: 301168 kB
Buffers: 170792 kB
Cached: 803312 kB
SwapCached: 0 kB
Active: 1313544 kB
Inactive: 361000 kB
HighTotal: 1171392 kB
HighFree: 25296 kB
LowTotal: 897504 kB
LowFree: 275872 kB
SwapTotal: 2706912 kB
SwapFree: 2706876 kB
Dirty: 11540 kB
Writeback: 0 kB
AnonPages: 575604 kB
Mapped: 19936 kB
Slab: 83156 kB
PageTables: 3332 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
CommitLimit: 3741360 kB
Committed_AS: 929176 kB
VmallocTotal: 114680 kB
VmallocUsed: 4084 kB
VmallocChunk: 110520 kB

MySQL version:
MySQL on localhost (5.0.32-Debian_7etch1-log)
removed:~# mysql -V
mysql Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (i486) using readline 5.2

PHP version
PHP Version 4.4.4-8+etch1


Apache version
removed:~# apache2 -v
Server version: Apache/2.2.3
Server built: Mar 27 2007 15:06:55


The server it used to run on had MySQL 4, so did the other server I tried the forum on. We have an avarage of +90 users online.

MySQl seems to be the trouble maker as far as I can tell, when I stop MySQL the load goes down very fast.

I did ran slow queries and stuff, and disabled plugins the forum was using.. but they ran fine on the other hardware so im not too sure about this.

I suspect the problem has to do with my MySQL version, or some sort of conflict with it. Is there any way to downgrade MySQL, and is it wise to do this?

Any information would be much appreciated!

Thanks in advance

[falko]

You can try to optimize MySQL by tuning my.cnf. This link might help you: http://dev.mysql.com/doc/refman/5.0/en/optimizing-the-server.html
Also, you should install munin to get graphs and see what the real bottleneck is.

Do you have a PHP cache (something like eaccelerator) installed?

[olkie]

You can try to optimize MySQL by tuning my.cnf. This link might help you: http://dev.mysql.com/doc/refman/5.0/en/optimizing-the-server.html
Also, you should install munin to get graphs and see what the real bottleneck is.

Do you have a PHP cache (something like eaccelerator) installed?


MySQL is tuned, tried different settings.. and the one that works ok on other servers.

As for caching I use memcached.

I have not tried munin yet, will install it today.

As for the downgrade, is it possible?
Also I installed mysql-4 but the version says 5.

When I try to apt-get mysql 4 it says its already installed, yet the version still says 5. bit weird imho

[falko]

When I try to apt-get mysql 4 it says its already installed, yet the version still says 5. bit weird imho
Did you restart MySQL?

[olkie]

Did you restart MySQL?

Hi sorry for the late reply been away for a few days.

This happened when I first installed this machine, and mysql has been restarted many times since :)


removed:/var/lib/mysql# dpkg -l|grep mysql
ii courier-authlib-mysql 0.58-4 MySQL support for the Courier authentication
ii libdbd-mysql-perl 3.0008-1 A Perl5 database interface to the MySQL data
ii libmysql++-dev 2.0.7-3 mysql C++ library bindings (development)
ii libmysql++2c2a 2.0.7-3 mysql C++ library bindings (runtime)
ii libmysqlclient15-dev 5.0.32-7etch1 mysql database development files
ii libmysqlclient15off 5.0.32-7etch1 mysql database client library
ii mysql-client 5.0.32-7etch1 mysql database client (meta package dependin
ii mysql-client-5.0 5.0.32-7etch1 mysql database client binaries
ii mysql-common 5.0.32-7etch1 mysql database common files (e.g. /etc/mysql
ii mysql-server 5.0.32-7etch1 mysql database server (meta package dependin
ii mysql-server-4.1 5.0.32-7etch1 mysql database server (transitional package)
ii mysql-server-5.0 5.0.32-7etch1 mysql database server binaries
ii php4-mysql 4.4.4-8+etch1 MySQL module for php4
ii postfix-mysql 2.3.8-2+b1 MYSQL map support for Postfix

removed:~# apt-get install php4-mysql
Reading Package Lists... Done
Building Dependency Tree... Done
php4-mysql is already the newest version.
0 upgraded, 0 newly installed, 0 to remove and 163 not upgraded.


removed:~# apt-get install mysql-server-4.1
Reading Package Lists... Done
Building Dependency Tree... Done
mysql-server-4.1 is already the newest version.
0 upgraded, 0 newly installed, 0 to remove and 163 not upgraded.


removed:~# apt-get install mysql-client-4.1
Reading Package Lists... Done
Building Dependency Tree... Done
Note, selecting mysql-client-5.0 instead of mysql-client-4.1
mysql-client-5.0 is already the newest version.
0 upgraded, 0 newly installed, 0 to remove and 163 not upgraded.

would this last part be a problem?

How easy would it be to revert this back to mysql-client-4.1, and how?
Would the db be still intact, or can I copy a mysql directory structure and then place it back?

thanks in advance

[falko]

You can try this: Back up the /var/lib/mysql directory, then run
apt-get remove mysql-server-5.0 mysql-client-5.0
apt-get install mysql-server-4.1 mysql-client-4.1
and then replace the new /var/lib/mysql directory with your backup.

[olkie]

You can try this: Back up the /var/lib/mysql directory, then run
apt-get remove mysql-server-5.0 mysql-client-5.0
apt-get install mysql-server-4.1 mysql-client-4.1
and then replace the new /var/lib/mysql directory with your backup.


removed:/var/lib/mysql# apt-get install mysql-server-4.1 mysql-client-4.1
Reading Package Lists... Done
Building Dependency Tree... Done
Note, selecting mysql-client-5.0 instead of mysql-client-4.1
The following extra packages will be installed:
mysql-client-5.0 mysql-server-5.0
Suggested packages:
tinyca
The following NEW packages will be installed:
mysql-client-5.0 mysql-server-4.1 mysql-server-5.0
0 upgraded, 3 newly installed, 0 to remove and 163 not upgraded.
Need to get 0B/32.5MB of archives.
After unpacking 85.2MB of additional disk space will be used.
Do you want to continue? [Y/n]

unfortunately that doesnt work :(
thanks for your help btw! much appreciated.

Any other suggestions?

[falko]

What's in your /etc/apt/sources.list?

[olkie]

What's in your /etc/apt/sources.list?


removed:~# cat /etc/apt/sources.list
#deb file:///cdrom/ sarge main

deb http://ftp.nl.debian.org/debian/ stable main
deb-src http://ftp.nl.debian.org/debian/ stable main

deb http://security.debian.org/ stable/updates main
blob:~#

[falko]

Please change it to
#deb file:///cdrom/ sarge main

deb http://ftp.nl.debian.org/debian/ sarge main
deb-src http://ftp.nl.debian.org/debian/ sarge main

deb http://security.debian.org/ sarge/updates main, run apt-get update, and try again.