How to setup mysql in debian for high load?

Question

MySQL

Hello.
On the server (8 cores, 32gb, linux debian) mysql 5.5.31 - up to several thousand queries are executed at a time.
In the morning everything is in order, but from 15:30 until the evening the load increases and nginx starts to constantly issue a 502 error.
When everything is in order:
screencast.com/t/ZpATtcji
Request duration is 0.3sec on average. Those. the MYSQL query itself is not very heavy.
When all is bad:
screencast.com/t/PsUD9fIssttL
With this, the top command shows the CPU load of the mysql process 100%-350%.
And what's worse, at times gives the error "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (11)"
I tried to set up mysql myself, according to various manuals from the Internet, but not too successfully, the problems remained. There was no experience in setting up highly loaded servers yet.
Here is the my.cfg file:
yadi.sk/d/SF7hykLEHMmTr
To prevent the error "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (11)" set open_files_limit 8192, but to no avail. It seems like something needs to be fixed in Linux itself, but it’s not clear what.
Nothing in Apache and mysql logs. No errors and it feels like logging just doesn't work properly.
I'm clearly missing something. The server configuration, then, is not at all weak, and these 2-3 thousand connections are probably not the limit.
Thank you in advance for your help.

Answer 1

First, you need to put the database in order - place the indexes where necessary.
Secondly - to optimize the application itself, it is possible to get rid of many requests.
In mysql, you can enable the slow query log (slow query log)
As for setting up mysql, in the case of innodb, I can advise the following recommendations www.pentarh.com/wp/2011/03/02/mysql-innodb-highloa ...
there is an option for the maximum number of simultaneous connections. But the best thing is to leave it unnecessarily - if you optimize everything else, then the default value should work fine.

Answer 2

Turn on slow log and see what mysql queries
run mysqltuner.pl and it will tell you what standard optimizations can be done
, hire a normal system administrator and live in peace.

Answer 3

0) are you sure that the error is due to the muscle? maybe you have something with php (if it is used) or with nginx itself? did you look at his logs?
1) read about persistent mysql connections
2) add query logging without indexes
3) read what mysqltuner/tuning-primer writes - sometimes useful things are advised
4) check the server and web server logs, maybe they also swear at the number of open files - then you need to edit these limits in the system (for example, here is one manual www.cyberciti.biz/faq/linux-increase-the-maximum-n... )
5) allocate more mysql memory, especially innodb
6) timeouts are very large - 4 hours - you may have a bunch of unused processes hanging
7) go to HNN in the mysql console and see the list of processes (show processlist;) - see what is happening there, locks may be immediately visible
8) switching to tcp / ip can help with an error on open files, but at the same time it can reduce performance

While you don't hit the entire IP stack when going over localhost, you still hit a big part of it. A unix socket is essentially just a 2-way pipe. It's faster and lighter.

and in any case - this is a temporary workaround of the problem, not a solution. It's just that sooner or later the same error will come up in another place
9) look at the iowait values ​​in top/iotop - perhaps the problem is not in the requests, but in the disk subsystem. In this case, moving the muscle to a separate section can help.
10) try to look at the load by processors (press 1 in top), perhaps one or two processors are packed to capacity, and the rest are idle. Perhaps, in this case, it would make sense to separate tasks by processes

Answer 4

Is it a web server? If so, first place breakpoints in the web application code and see where it stumbles or takes a long time to execute with an emphasis on the database. From there you will see problem requests and understand where the bottlenecks are.

Answer 5

Google mysql ulimit e.g. duntuk.com/how-raise-ulimit-open-files-and-mysql-o...
Plus, look at what urls the 502 error goes, if there is some common pattern, then just add the log to the raw processing given url, and find out what hangs from the logs.

Answer 6

Write to the mail, I'll try to share a suitable config.
In general - you are switching to perkona 5.5 already)

Answer 7

Try to adjust the settings according to this guide .
Of the tools for analysis - munin for graphs, mysql / fpm slow log, maatkit for analyzing these logs.