Hi, been having some very random issues with my site where the website will just display 'Database connection issue' too many connections. and a 'top' command shows mysql at 100% of CPU.

A mysql restart restores it fine but it keeps happening and I'm not sure why.

Which error logs are best to check on this , I tried var/log/mysql.log but theres nothing in it.

Thanks for any help

Also noticed from the graphs that whenever mysql goes to 100% (and the site goes down) then also the network ussage has a large spike. Are these some kind of http requests looing or something?

Thanks

Are you sure it's not just a large number of people visiting your site, or a half-assed ddos attempt? A large number of concurrent requests to database-heavy pages could cause the "too many connections" issue. Better check your web server logs for any unusual activity.

Hi, no the access logs that my site generates show googlebots etc but nothing amazing.
I have Livezilla on it so can see who's coming in or out and only running one site and traffic has been probably even lower than normal (has been since Googles mayday changes).

Maybe try using the slow query log and/or mytop?

IF that doesn't help, try running a full query log for a while (careful to not fill the disk - I personally directed the log to a fifo that was cat'd in a remote terminal with local logging enabled). Had similar problem, and slow log did not help because it was actually a flurry of quick* queries.
(*That is, a few dozen thousand full scans on a ~100MB table... unfortunately, each took less than 2 sec, so no slow log entries.)

Hi, thanks for the suggestions I have tried some of them and noted the last query that occurred when the site last went down just recently, I set the slow query log to 8 seconds and it only recorded one thing which was a standard query of which I also got in the browser when I reset mysql.


It does not indicate the issue as such so I am wondering if the last comment is more relevant as it is an issue where all the databse connections are being used for some reason, I will check that next.

increase your mysql threads and threads cache if your mysql is using too much CPU.

high CPU is a sign of excessive forking.

For a Linode 768 I have:
# Threads:
thread_concurrency = 4
thread_cache_size = 6

_________________
Linux Sysadmin
Ruby on Rails Developer

Erm, what?


Interesting...

http://dev.mysql.com/doc/refman/5.1/en/ ... oncurrency


MySQL runs a thread per connection, unless you enable the single thread mode, which's extremely inefficient and used only for debug purposes.

There's quite a few more likely things, depending on what "high CPU" really means.

_________________
Disclaimer: I am no longer employed by Linode; opinions are my own alone.

TRUE, CPU usage is divided in 3 categories: user, system, iowait.
I had experience that mysql was taking on all IOwait, after an extensive mysql profiling we had to tune my.cnf to match the work load (joins, query cache, threads cache, sort_buffer etc)
have a look at the mysql sample config files under /usr/share/doc/mysql-x.x.xx/support-files/ {my-medium.cnf my-large.cnf my-huge.cnf}

Some tools you can use to find out your CPU usage are mpstat (found on sysstat package) and iostat

also: http://www.cyberciti.biz/tips/how-do-i- ... ation.html

_________________
Linux Sysadmin

Ruby on Rails Developer