Optimization of bundles of Nginx, Apache, PHP, MySql

Unexpectedly came the task to figure out why a particular site is not running as fast as I want. Based on CakePHP, in conjunction with Apache and MySQL. This article describes how to find bottlenecks and improving as much as possible.

The name of the site will not shine — think, programmers will learn themselves. Let me just say that this is an application for social network load 70–150 thousand visitors at the usual time. All complicated by the fact that from time to time by direct mail, which attracts about 200–300 thousand visitors for a couple of hours.

So, under the cut description of the whole fight over the 4 days.

This article focuses on 2 groups of people. The first is, of course, we, the Admins who have to chip away at such ugliness. Sometimes pulling almost fetched dying server. The second part of the audience, which I hope will be sure to read all of the material is the programmers. Friends, going forward, will say: wouldn’t it be a shame if you correctly have designed their projects, many incidents could have been avoided. Especially knowing on what audience you write your draft.

I have had a server located on the site of EX10 hetzner. For those who do not know is the 64 GB RAM and 6-core processor. Let’s call it the core of the system. There are 2 servers, one with static, the other with bekènd base. A small application, InnoDB database to 500 MB.

With constant load, as it turned out later, 70–100 online sessions, the situation is the following: the 100% CPU on each core. At the top, of course, MySQL and Apache fight for system resources.

Nginx

It was the first attempt to reduce server load by caching of Apache in order to remove the issuance of statics.

Set in a very simple configuration: it was all all files by mask trying to take away from a specific folder, if there is no file in it, take it to a proxied server, put in this directory, and issue the client.

http {
proxy_cache_path  /var/tmp/nginx_cache/  levels=1:2   keys_zone=ok:100m inactive=1d max_size=1024m;
server {
        location ~*  \.(js|JPG|jpg|png|jpeg|gif|zip|tgz|gz|rar|doc|xls|exe|pdf|ppt|txt|wav|bmp|rtf)$ {
                expires 1y;
                open_file_cache_errors  off;
                error_page 404 = @fetch;
                root  /var/tmp/_fetch_ok;
                }

        location @fetch {
                proxy_store_access      user:rw  group:rw  all:r;
                proxy_store on;
                proxy_pass http://127.0.0.1:80;
                proxy_temp_path /var/tmp/_fetch_ok_temp;
                root  /var/tmp/_fetch_ok;
                }


       location / {
                proxy_cache ok;
                proxy_pass   http://127.0.0.1;
                proxy_cache_valid any 10m;
                proxy_buffer_size 8k;
               }
}
}

http {

proxy_cache_path /var/tmp/nginx_cache/ levels=1:2 keys_zone=ok:100m inactive=1d max_size=1024m;

server {

location ~* \.(js|JPG|jpg|png|jpeg|gif|zip|tgz|gz|rar|doc|xls|exe|pdf|ppt|txt|wav|bmp|rtf)$ {

expires 1y;

open_file_cache_errors off;

error_page 404 = @fetch;

root /var/tmp/_fetch_ok;

}

location @fetch {

proxy_store_access user:rw group:rw all:r;

proxy_store on;

proxy_pass http://127.0.0.1:80;

proxy_temp_path /var/tmp/_fetch_ok_temp;

root /var/tmp/_fetch_ok;

}

location / {

proxy_cache ok;

proxy_pass http://127.0.0.1;

proxy_cache_valid any 10m;

proxy_buffer_size 8k;

}

Config is not completely, and only necessary for this example blocks.

Unfortunately, not very this led to any results.

The pros were only 3:

Apache stopped issuing pictures, that is a little less loaded

Apache stopped to talk directly with the outside world, hence it can be disable keep-alive and reduce the number of children.

First there was a bottleneck is absolutely all requests to a site managed by a PHP script which .htaccess redirects any request. Including, you wouldn’t believe all the static content, and even css.

MySQL

Here is the most difficult, because at this point I don’t have much experience with SQL queries, and optimization.

Started by reading the documentation for MySQL.

Since we have at hands of InnoDB — I took the original position of the configuration file from the standard package my-innodb-heavy-4G.cnf

The following will describe the configuration settings that should be pay attention to high-traffic projects.

back_log = 5000

max_connections = 1600

The first parameter is the number of connections that can be queued until the server stops responding to new requests. The second is how many connections can be accepted by the server.

I have these values are large enough, as competing sessions on average goes up to 1300. Make more than you need–not worth it, because each connection may require a certain amount of RAM. More on that later.

max_connect_errors = 50

There simply is the number of errors that can make the client before you get there. Had to increase, since the project is under development, and the chances of incorrect queries a lot.

table_cache = 2048

Open a table requires some resources, hence this option is responsible for the number of open tables, waiting for the next connection some time after the last.

Find out whether you can change it to a variable

SHOW GLOBAL STATUS LIKE ‘ Opened_tables ‘;

It should not be as low as possible.

It is well written: http://www.mysql.ru/docs/man/Table_cache.html

max_allowed_packet = 16 m

The maximum packet size. If you do not use big BLOB, change does not make sense.

binlog_cache_size = 1 m

The cache size of the binary log for the transaction. In the official documentation of reviews grow if we have big transactions.

dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html # sysvar_binlog_cache_size

max_heap_table_size = 64 m

tmp_table_size = 64 m

As far as I understand, the less of them. Parameter, the maximum size of the temporary table fit in memory. If it reaches it is put on the disk. Therefore, you must try the on-disk tables created as little as possible. See how temporary tables to tables on disk at this point you can ask

Show status like ‘% tmp% tables ‘;

www.mysqlperformanceblog.com/2007/01/19/tmp_table_size-and-max_heap_table_size/

sort_buffer_size = 8 m

That would not fool anyone, did not take the move. Just to be clear, only that in the documentation are advised to look at this option only if the

Show status like ‘% Sort_merge_passes% ‘; more than zero

dev.mysql.com/doc/refman/5.5/en/server-system-variables.html # sysvar_sort_buffer_size

join_buffer_size = 2 m

As far as I understand, the maximum buffer size for operations that do not use the index. Not yet touched.

dev.mysql.com/doc/refman/5.5/en/server-system-variables.html # sysvar_join_buffer_size

thread_cache_size = 4096

The maximum number of treadov that remain to be reused after the request. Helpful to keep sufficient to MySQL as possible doing new treadov and used the old. Understand the effectiveness of the the relative parameters Threads_created/Connections;

dev.mysql.com/doc/refman/5.5/en/server-system-variables.html # sysvar_thread_cache_size

query_cache_size = 256 m

query_cache_limit = 8 m

I think it is better than my fellow, author of the переводаhabrahabr.ru/post/41166/no one will say.

This is probably the most important parameter, so it’s best to reread.

thread_stack = 192 k

No I want to describe the purpose of this option, I will merely note that it also influences the amount of RAM, as it too is allocated per connection. Hence again multiply the max connections

long_query_time = 2

log_long_format

log-queries-not-using–indexes

The MySQL server is a very handy tool to assess the performance of the database. This is a log file of long queries. In my experience it is usually inefficient queries or requests no index.

I advise you with this log file, go to programmeram.

key_buffer_size = 1 g

Parameter indexes in memory caching to optimize the value of Key_read_requests, Key_reads, see. The second parameter is the number of disk reads and buffer them not.

mysqltips.blogspot.com/2007/03/key-buffer.html

read_buffer_size = 1 m

boombick.org/blog/posts/3–after reading this text, not something I would venture to add, as I am sure he is right.

read_rnd_buffer_size = 24 m

The parameter influences the speed of sorting operations. Unfortunately not found how to evaluate its effectiveness.

dev.mysql.com/doc/refman/5.5/en/server-system-variables.html # sysvar_read_rnd_buffer_size

www.mysqlperformanceblog.com/2007/07/24/what-exactly-is-read_rnd_buffer_size/

myisam_sort_buffer_size = 128 m

myisam_max_sort_file_size = 10 g

myisam_max_extra_sort_file_size = 10 g

Settings affect sorting, so do not venture to change them. Increased first assuming that it will increase the performance of complex queries.

sync_binlog = 0

In this case, means not to synchronize the binary log to disk through system functions. If the parameter is greater than zero, then the server will synchronize data every n requests.

dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html # sysvar_sync_binlog

innodb_buffer_pool_size = 4 g

Increasing this parameter reduces the number of disk operations. Unfortunately hasn’t found a better measure. Because small decided to strongly increase. Somewhere across the Board, in the case of a large DATABASE to increase this parameter to 70% of RAM.

dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html # sysvar_innodb_buffer_pool_size

innodb_log_buffer_size = 32 m

According to the description, reduces disk operations with heavy transactions.

dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html # sysvar_innodb_log_buffer_size

innodb_log_file_size = 1024 m

According to the documentation, increase the IO load reduces the log file disk operations, but increases the recovery time in the event of errors.

dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html # sysvar_innodb_log_file_size

innodb_flush_log_at_trx_commit = 0

The value 0, the buffer is reset once in a minutusekundu, rather than after each insertos.

dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html # sysvar_innodb_flush_log_at_trx_commit

innodb_thread_concurrency = 14

Recommend to slightly more than the number of cores.

innodb_sync_spin_loops = 10

As far as I understand, affect the number of attempts to access the locked data. Increasing this value can we lose processing time and reducing write reliability is in DB.

www.mysqlperformanceblog.com/2006/09/07/internals-of-innodb-mutexes/

dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html # sysvar_innodb_sync_spin_loops

The DATABASE and memory

There is a wonderful perl script which gives a basic understanding of what needs to be changed in the database. mysqltuner.pl/mysqltuner.pl

Very often the script complains the maximum server memory consumption by mysql.

If you look at the source — here’s how this program considers the memory usage:

per_thread_buffers = read_buffer_size + sort_buffer_size + read_rnd_buffer_size + thread_stack + join_buffer_size;

total_per_thread_buffers = per_thread_buffers * max_connections;

server_buffers = key_buffer_size + max_tmp_table_size;

innodb_buffer_pool_size = + server_buffers;

innodb_additional_mem_pool_size = + server_buffers;

innodb_log_buffer_size = + server_buffers;

query_cache_size = + server_buffers;

total_possible_used_memory = server_buffers + total_per_thread_buffers;

I found it helpful to understand where I am not properly indicated importance.

By the way, immediately throw understate the parameters, if the script complains much RAM consumption base, is not worth it. As many have pointed out that this is only a theoretical value and the DATABASE may never try to pick up so many memories.

Optimizing database structure.

When we have done everything possible and still did not get a good result, it’s time to turn to the slow-log file.

The standard mysql application mysqldumpslow.

By running

mysqldumpslow–s c < slow path to log file > sorted by number of occurrences of a list of queries to the database that have been too long or did not use the indexes usually add the right indexes fixes both problems.

In most cases, when you see in the output of this program a large number of long requests (variable count), copy the piece of this query and are looking for in the text of the log file for an example of this query.

Next, go to the client DATABASE and execute this query by adding the first floor to explain.

About this you can read more here:

habrahabr.ru/post/31072/

So you can see whether the query uses an index or not.

If the table is not enough, you can safely add indexes, while going over the top is also not worth it. Indexes need columns which are used after the where and order. Start with a unique index to each stoblec. Otherwise, the index may not work.

Here you can find out in more detail how these indexes are:

dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

To be honest, after about 5 key optimization query server was able to handle 500–700 connections instead of 50, and time of issuance of the php page was reduced to 1 c instead of c. At maximum load the time the page was 5s instead of 50 c. (With performance measurements using Apache Benchmark (c) approximately 1000 threads)

A little more about nginx.

After optimizing noticed that at high loads more of a certain number of queries threw himself nginx instead of apache. The memory and CPU not loaded.

Was the deal. Saw logs, server nginx tries to open files more than he expected.

In Suse, with whom I had to face, for this limitation is file

/etc/security/limits.conf

To finish I go there these are the lines:

nginx soft nofile 300000

hard nofile nginx 300000

Server restart is not needed.

Apache2

Much configuration I have not yet started. The only thing that did is switched off keep-alive. The Apache calmly to reply and to do the following query, when nginx still pays the customer the page over a slow link.

eAccelerator

Do not forget that the optimizer also has a number of parameters that can be changed.

Here is what I changed:

eaccelerator.shm _size = 2096»

Virtual memory size, in megabytes, that can be used

_only eaccelerator.shm = “1” is used only in RAM and do not use the drive for io in software RAID SATA 2 drives of decided to do so.

Here is another that will be useful to read:

habrahabr.ru/post/41166/

habrahabr.ru/post/108418/

dev.mysql.com/doc/refman/5.5/en/server-system-variables.html

Special thanks to my friend who helped proofread the article and fix a mountain of grammatical and stylistic errors.

Instead of an epilogue

Friends, I understand that mistakes and errors in the conclusions presented in this article a whole bunch.

Big request to guru, his comments, help make the article better.

UPD. Summary of the necromancer out

Thanks for such a great interest. Did not expect.

Good link, I recommend reading: www.percona.com/files/presentations/percona-live/dc-2012/PLDC2012-optimizing-mysql-configuration.pdf

Thanks Albertum

About php caching system:

Yes indeed eAccelerator is not the only option.

There is also an APC, and Yes indeed its going to embed in PHP.

What is best judged not start because not done enough tests.

MySQL also present alternatives and a lot of them.

The key of course:

mariadb

percona

I personally chose the perkonu and so far satisfied.

That this is not the end result must be a bright move on — those who carefully read will see that it is only patching.

Nginx

MySQL

Leave a Reply Cancel reply