Mysql running at 100% randomly

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

10 Replies

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

threadcachesize = 6

> high CPU is a sign of excessive forking.
Erm, what?

> thread_concurrency = 4
Interesting…

http://dev.mysql.com/doc/refman/5.1/en/ … oncurrency">http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvarthreadconcurrency
> This variable is specific to Solaris systems, for which mysqld invokes the thr_setconcurrency() with the variable value. This function enables applications to give the threads system a hint about the desired number of threads that should be run at the same time.

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

@brfsa:

high CPU is a sign of excessive forking.
There's quite a few more likely things, depending on what "high CPU" really means.

@jed:

@brfsa:

high CPU is a sign of excessive forking.
There's quite a few more likely things, depending on what "high CPU" really means.

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">http://www.cyberciti.biz/tips/how-do-i-find-out-linux-cpu-utilization.html

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct