2

I have 2 servers running 1 website. Server #1 is dedicated to Apache, serving the website files to the Internet. Server #2 is dedicated to MySQL, serving the database to the Server #1. Gigabit ethernet connects the 2 servers.

The problem I'm having is with Server #2, when traffic spikes, it causes MySQL to go away and I'm forced to restart MySQL to get thing back online again. When we reach too much traffic online, the pages just hang and everyone gets a database error screen.

I think the my.cnf file is not dialed in correctly and needs to be optimized.

Both servers are identical Dell PowerEdge servers, here are the specs:

  • CPU - Dual Intel Xeon 2.40GHz
  • Memory - 3GB of RAM
  • Storage - 2x36GB 10K SCSI Hard Drives in RAID 1
  • Linux - Debian GNU/Linux 4.0 (etch) kernel version 2.6.18-6-686 (SMP)
  • Apache v2.2.3
  • PHP 5.2.0-8+etch16
  • MySQL 5.0.51a-24+lenny2+spu1-log
4
  • What do you mean by "goes away"? does it crash completely? just really slow? What does the memory/CPU usage on the machine look like when it happens? pegged? idle? Jul 16, 2010 at 20:51
  • The web server just hangs, waiting for MySQL, until eventually you get a database error screen. The servers are still going, I can still SSH into them, but I'm forced to restart the MySQL service from the command line and then we're good to go again. I do not have to reboot the database server, just the MySQL service on the server. At pegged and idle, Server #2 always has a low server load. Server #1 has a low server load at idle, but gets up there when pegged. Memory free is fine on both servers.
    – Daniel
    Jul 19, 2010 at 19:10
  • Can you post some of the configuration and profiling information I requested in #161286? It'll really help us answer your question.
    – BMDan
    Jul 21, 2010 at 11:49
  • On the off chance you haven't forgotten that you ever asked this question, or in case someone finds this page via a search, the most likely candidate for an answer, given your reply in this comment, is that you've got hanging MySQL connections that are clogging up the database server. Enforcing a wait_timeout or similar may be enough to mask the problem, but the real solution will be using SHOW PROCESSLIST and some detective work to figure out what piece of broken code is keeping the connection(s) open.
    – BMDan
    Jul 25, 2010 at 13:22

5 Answers 5

1

http://www.day32.com/MySQL/ has a script that gives you some very basic tuning parameters. What do your error logs say, does the server actually crash? Are things pushed to swap causing the machine to run incredibly slowly but it would recover?

have to wait to post, serverfault thinks I am a robot.

0

Try increasing max allowed packet size and wait timeout values on the server side

Also maybe your app server tries to connect to mysql but gets an errors, if so in some time it will be blacklisted. Try executing mysqladmin flush-hosts instead of doing a full restart. If this help, put the command inside a crontab and then you'll have a time to investigate a problem without downtimes in your production. Enable all client side logs and go.

0

(posted as an answer so I can use formatting)

Post the following:

grep -vE '^#' /etc/my.cnf
for db in $(mysql -e 'show databases' | sed 1d); do mysql -e 'show table status' $db | awk '{print $2,$5}'; done
mysqlreport

Get mysqlreport from http://hackmysql.com/mysqlreport if you can't get apt-get it.

0

I suspect that the max_connect_errors variable in MySQL fills up. By default it's only 10, after that MySQL starts rejecting that specific server which has exceeded the max_connect_errors value.

You may increase that value temporarily with SET GLOBAL max_connect_errors='10000' or so and permanently by putting

max_connect_errors = 10000

to your /etc/mysql/my.cnf file (or wherever you have it).

0

I'm guessing you need to properly tune your my.cnf. MySQL default configurations are way to conservative.

One common mistake that may be slowing your MySQL is having your key_buffer_size set to low value, which is the size of the buffer used for index blocks. Tune this to at least 30% of your RAM or the re-indexing process will probably be too slow.

One the other hand, log your slow queries by activating the slow_query_log. This will log all the queries that take more than 2 seconds to run (this can also be tuned). Then, take a look at this log an see what queries can be optimized. Sometimes, it's just a matter of creating an index.

You can also run show processlist in your MySQL shell and see what queries keep appearing. These queries also deserve some attention as they can probably be optimized.

You might also benefit of MySQL query cache.

There are some good scripts available online that can help you this tuning your MySQL server. MySQL Tuner is one of them. Follow the link and you'll also find some good tips and common mistakes.

Hope this helps!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .