MySQL Overload
 

I have recently installed MySQL 4.1.10a using a binary on two RHEL4 boxes and set them up in replication. These servers are being used to hold the databases for our 3 webservers that are in a load balancing setup.

The problem is that whatever server we are using for the master seems to overload randomly and use up the 800 connections it has as a limit and then just won't accept anymore connections and never seems to close them. If I do a netstat all the mysql connections are in time_wait and it just stays like that indefinatly until I kill it using kill -9 and then start it again. I don't think it has anything to do with the server being the master since I tried switching them and at the moment they are both masters, I think it is more that the one that is set to master is used almost exclusivly since we have not configured all the web stuff to use the slave to read...so it does very little.

I am thinking its either som limit with a setting in the OS thats stopping it from doing something like opening to many tables or something or its something wrong with a config setting in mysql. I have tried using both the Huge config that came with mysql and the settings from a server that this one is replacing that is a RHEL3 box and they both have the same effect...but from what I can see the box crashes a little less using the config from the old box.

Here are the two machines specs:

Duel Xeon 2.8 (Dell)
4 Gig if RAM
2 73 Gig SCSI HDs (mysql Data on the second HD)
RHEL 4
Private Connect between the two servers

I will also include the config from the servers, they are the same except for the server ID:
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

[mysqld]
server-id = 1
log-bin=mysql-bin
datadir=/mysql/data
port = 3306
socket = /tmp/mysql.sock
max_connections = 800
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

If anyone has any idea how I can fix this please let me know, I am at a lose for what is cauing it.
If you need some more specifics, let me know.

You could try decreasing wait_timeout but I'd guess it is more likely to be a problem with your client application.

I did increase the timeout and it leasted longer but it still ended up crashing today after about 24 hours up.

I honestly don't think its something with the client, I think mysql is not closing something for some reason and it hits some sort of limit. When it crashes it seems to be in a confused state, it marks logs with errors from the wrong users and things like that.

Anyone else have any ideas?

Still trying to solve this problem if anyone has any ideas.

I have tried to use a manaully compiled version of mysql 4.1.10a with the same results as well as a binary of 4.1.9.

I have now been trying to use the RPM version of 4.1.10a for RHEL4 but it won't start with my databases and I also don't want the data to be stored in /var/lib/mysql and as soon as I change anything in the config or use another config the /etc/rc.d/init.d/mysqld start fails with a time out error and manuallly trying to start from /usr/bin/mysqld_safe --user=mysql & starts and then instantly stops the server.


If anyone has any ideas I could really use the help.

Thanks

it's not related to SELinux at all which is enabled by default in RHEL4 ?

No I don't think so.

I actually fixed this a while ago and w don't seem to have to many issues now. I turned on skip resolve on the command line when starting mysql (would not take in the config) and we don't have an issue now. I guess the server was trying to resolve all the hosts and was taking to long.

Not sure if SELinux could be an issue, it still does not perform like I would want it to to be honest.

maybe you can see something interesting with the command "show processlist;" from de mysql prompt.
Is there anything in de mysql.log or do you have al slow-queries.log?

We had a slow query problem for a while, I made one of the people using the DBs change part of their application. Most of the time the process list is fine but other times I have a higher load on the site that this is a backend for and it does not preform as well. Server loads go very high averages go very high and it can not run as many connections as I would like. I think it would be alright on a Duel 2.8 Ghz with 4 Gig or Ram. Mind you it could be some sort of performance tuning issue.