Analysis of download schedules and web server optimization?

Question

MySQL

Good evening, habr. I ask you for help in analyzing server load graphs and optimizing it, I think I'm missing something, or I just don't understand it.
Background: I inherited one site located on a fairly powerful vds (8 cores, 16Gb RAM, Ubuntu Server), made on joomla with several components, one of which is an actively used forum. All this worked on pure Apache + MySQL (the vast majority of tables in MyISAM). In the evening, when a large number of people come to the site, it periodically stops responding to requests, i.e. You can log in via ssh normally and work in the console, but the site itself, if it opens, is very slow. At such times, LA was around 14-16.
First of all, I set up the frontend (nginx) to serve static and proxy the rest to Apache, and set up memcached, in which Joomla began to store the cache. After that, LA in peaks became about 4. For a while the site worked fine, but after a few days the problems started again. (LA 8-9+)
This time I decided to dig deeper, and first I installed munin to monitor the system. Then I installed APC, adjusted the size of the opcode cache so that it did not overflow, tried to use it as a joomla cache store, but got scared by the 100% fragmentation that appeared, and returned the cache to memcached. I also ran the tuningprimer database'ohm, took advantage of the recommendations, made more table_cache and open_files_limit, made sure that the cache was enough. After all this, the maximum LA seen today was 5, but users complained that the site was down for a while.
In this regard, I have a question for the habra community: what else can be done in this situation and in which direction to look? As far as I can understand, the problem is created by a large number of database requests, many even get into slow-log, but you can do something with requests only by getting into the component code, which you want to do only as a last resort. What charts and configs to show for a better understanding of the situation?
UPD: I plan to try to get rid of apache, leaving only nginx + php-fpm. Will APC work normally with such a bundle, and will it help me at all?

Answer 1

1. MyISAM to InnoDB.
2. Fix your my.cnf appropriately.
MyISAM locks the entire table when reading. InnoDB just a line. InnoDB eats memory, but handles it more efficiently. InnoDB does not support full text search - it will be necessary to fasten the sphinx.

Answer 2

By itself, high LA is not a problem or a symptom, IMHO. You need to look at the composition of processes and key indicators at times of high LA values ​​in top.
If, for example, at peak load, wa is high, then the server “rested” on the disk and it makes sense to try to reduce the load on the disk subsystem using simple measures: disable access logs, get rid of errors in error logs, specify “BufferedLogs on” in apache config, put noatime for section, etc.
At the same time (and if there is a margin of memory), in relation to the muscle, one should not skimp on: table_cache, thread_cache_size, query_cache_size, max_heap_table_size, tmp_table_size and other "caching" parameters.
If, on the contrary, at high LA and wa there is a shortage of memory, it is necessary to reduce its consumption, since the system will most likely go into swap.
If 1-2 long requests most often appear in the same slow.log, it makes sense to deal with their origin and, depending on the latter, either get rid of them or optimize them.
Although in the end, with a further increase in the size of the database, you still need to optimize queries or change hardware.
As for the nginx + php-fpm bundle, I approve, because I have positive impressions of its use. But the transition to this bundle does not cancel what was written above, because it will not get worse.

Answer 3

You need to set the profiler on the code, reduce the time for the slolog and look at the problem requests, exploit them and fix them. It is possible to cache the result, for sure the problem is easily solved at the code level, or it will be solved - with a couple of indexes / migrating a couple of tables to InnoDB / replacing a query / cleaning up extra data / castration of extra functionality.

Answer 4

Perhaps the problem is in the forum itself? If it is crookedly written and generates a huge number of queries to the database, in this case it is easier to move to another forum, perhaps even a converter can be found.

Answer 5

what about caching?
what do you use at what levels?
eAccelerator? memcached? joomla plugins?

Answer 6

1) Decide who is laying down your site, slow php + apache or slow requests to the muscle
2) If apache + php take down the APC, install an accelerator, it shows itself much better + fasten php = fmp to nginx.
3) If the muscle crashes the site, then you can’t do without query optimization, my practice has shown that you can’t achieve significantly better site performance results simply by tweaking the muscle settings, one long request will always crash the site successfully, you need to find which queries crash the site, and then either disable this functionality if it is not important or rewrite the query directly.
4) Separating the database and Apache to different hosts can help to partially temporarily improve life.

Answer 7

According to the graphs you have given, it can be seen that everything rests on the processor. However, it is not clear what the processor eats. Please see the same top.
If the processor eats MySQL, then simple actions will not do: you need to dig in the direction of the forum and the slow queries it generates. It is likely that the creators of the component / forum have no idea about the indexes ... Then either tuning the forum, or replacing the forum with another one.
By no means do I want to cast a shadow on the developers of joomla - I like it myself, but sometimes there can be blunders ...