After updating the software on the server, the site began to slow down

Question

PHP

There is a server. FreeBSD 8.0, Apache 2.2.23, PHP 5.4.10, MySQL 5.1.67. It was necessary to update PHP on it (before that there was Apache 1.3 with PHP 5.2.4). As soon as Apache and PHP were updated, the pages of the site began to load in 2-5 seconds. Updated more thoroughly, with all sorts of dependencies, optimized / checked the tables in MySQL. It slows down when working with MySQL (I measured the execution time of various queries inside the running script itself). Moreover, the slow requests are constantly changing places in this script: either one was slowly executed, then another, then the third. I have already tried to find and optimize slow queries in MySQL logs, change MySQL and Apache settings. Put Nginx ahead. Did not help…

There are some embarrassing things about MySQL processes though. For example, there are many requests with the Sleep status and NULL instead of the table name, as well as many requests with the Locked status for one of the most used tables in the project. But requests to this table are normal, mostly by primary key. Separately, PHP scripts without MySQL, and even more so static pages, load quickly. I understand that everything points to a problem with either the MySQL configuration or crooked queries, but after all, everything was fine before the PHP and Apache upgrade.

What can be done? Help me please.

Just in case, top

last pid: 45111; load averages: 26.49, 25.19, 23.14 up 1+02:58:21 19:34:52
224 processes: 24 running, 199 sleeping, 1 zombie
CPU: 91.3% user, 0.0% nice, 4.8% system, 0.5% interrupt, 3.4% idle
Mem: 1740M Active, 3971M Inact, 1154M Wired, 125M Cache, 827M Buf, 907M Free
Swap: 4096M Total, 4096M Free

PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND
34985 mysql 80 44 0 535M 243M CPU0 0 0:13 28.22% mysqld
44953 www 1 101 0 159M 32980K RUN 4 0:14 28.08% httpd
45078 www 1 100 0 158M 31444K RUN 1 0:05 22.85% httpd
44708 www 1 76 0 159M 32608K select 1 0:21 22.75% httpd
44181 root 1 63 0 2740K 800K biowr 7 1:38 15.19% rm
44705 www 1 53 0 159M 32840K RUN 1 0:26 12.70% httpd
45057 www 1 53 0 159M 32572K select 4 0:07 12.50% httpd
45054 www 1 54 0 158M 31436K lockf 5 0:08 9.18% httpd
44119 www 1 55 0 158M 32272K lockf 0 0:53 8.98% httpd
45048 www 1 48 0 159M 31960K CPU2 2 0:09 7.76% httpd
45067 www 1 52 0 159M 32696K lockf 5 0:04 7.76% httpd
44986 www 1 51 0 158M 32012K lockf 4 0:17 7.37% httpd
44626 www 1 51 0 159M 32856K lockf 1 0:30 7.18% httpd
44643 www 1 50 0 159M 32744K RUN 7 0:25 7.18% httpd
45081 www 1 49 0 160M 33100K CPU0 0 0:03 7.08% httpd
44735 www 1 50 0 160M 33128K CPU5 5 0:16 6.98% httpd
45038 www 1 50 0 159M 32680K lockf 1 0:05 6.98% httpd
45047 www 1 50 0 159M 32736K select 3 0:04 6.69% httpd
45037 www 1 50 0 158M 31476K lockf 5 0:04 6.59% httpd
45059 www 1 50 0 159M 32680K lockf 6 0:05 6.49% httpd
44508 www 1 49 0 159M 32776K lockf 4 0:40 6.40% httpd
45084 www 1 50 0 158M 31708K select 4 0:03 6.40% httpd
44962 www 1 49 0 159M 32404K select 6 0:16 6.30% httpd
45062 www 1 50 0 159M 32688K lockf 5 0:04 6.30% httpd
45040 www 1 49 0 158M 31432K select 0 0:05 6.15% httpd
44526 www 1 48 0 159M 32820K select 4 0:37 6.05% httpd
44600 www 1 48 0 160M 33072K CPU7 7 0:27 6.05% httpd
44710 www 1 49 0 160M 33116K lockf 3 0:21 6.05% httpd
44673 www 1 48 0 159M 32864K CPU3 3 0:20 6.05% httpd
44790 www 1 49 0 159M 32864K lockf 6 0:20 6.05% httpd
45065 www 1 48 0 159M 31832K biowr 4 0:08 6.05% httpd
45049 www 1 48 0 159M 31688K biowr 6 0:07 6.05% httpd
45036 www 1 47 0 159M 32504K select 4 0:05 5.96% httpd
45060 www 1 49 0 160M 33048K RUN 6 0:04 5.96% httpd
44647 www 1 48 0 160M 33120K CPU6 6 0:27 5.86% httpd
44967 www 1 49 0 160M 33104K getblk 2 0:11 5.86% httpd
45063 www 1 49 0 160M 33084K lockf 1 0:04 5.86% httpd
45082 www 1 49 0 160M 33100K getblk 0 0:03 5.86% httpd
45103 www 1 48 0 159M 32680K RUN 0 0:03 5.86% httpd
44503 www 1 49 0 159M 33092K select 2 0:38 5.76% httpd
44776 www 1 48 0 159M 32756K select 4 0:19 5.76% httpd
44980 www 1 49 0 160M 33100K lockf 5 0:10 5.76% httpd
45088 www 1 48 0 160M 33056K getblk 3 0:03 5.76% httpd
45098 www 1 49 0 158M 31424K lockf 7 0:03 5.76% httpd
44669 www 1 49 0 159M 32740K lockf 6 0:28 5.66% httpd
44588 www 1 49 0 159M 33176K lockf 0 0:24 5.66% httpd
44990 www 1 48 0 159M 32740K select 7 0:13 5.66% httpd

Answer 1

load averages: 26.49, 25.19, 23.14
This can be a terrible overload if there are few processor cores.
How many? sysctl -a | egrep -i 'hw.ncpu'
and who are the zombies? 1 zombie

Answer 2

Look at the disk load gstat
and netstat -n | grep80| awk '{print $5}' | cut-d. -f 1,2,3,4 | sort | uniq -c | sort -n
can be ddos.

Answer 3

Possibly something to do with this change in PHP 5.4:

ext/mysql, mysqli and pdo_mysql now use mysqlnd by default.

Answer 4

From the top you can see that the entire percentage is fried, there is one muscle, and a lot of Apache and one rm
To begin with, we remove rm
, it is logical that the brakes of mysql are not connected with the muscle itself, but with the fact that the Apache ate the entire percentage, if the Apache ate it, then it is either not correct apache config or wrong php config well, or php code as always is shit and in the new version of overloadid php module in apache.

Answer 5

If swap is loaded, then this is a serious problem. It is necessary to either optimize for memory loading (perhaps start with mysql), or add memory to the server.
For example, you can start by removing unnecessary modules and reducing the number of processes for apache.

Answer 6

Does apachetop -f /path/to/apache.access.log show the expected situation?