php and mysql application responding very slow

HI , I have a dedicated server with 12GB ram and quad core processor . The server has 2 hard disks and mysql data is stored on a SSD . The CPU load average of the server has remained under 1 all the time . top results are as follows [root@usloft2559 ~]# top top - 21:59:54 up 7 days, 13:51, 1 user, load average: 0.49, 0.28, 0.25 Tasks: 272 total, 1 running, 271 sleeping, 0 stopped, 0 zombie Cpu(s): 0.3%us, 0.1%sy, 0.0%ni, 98.4%id, 1.2%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 16325484k total, 16018776k used, 306708k free, 260556k buffers Swap: 2097144k total, 154720k used, 1942424k free, 13648908k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 5206 mysql 20 0 4821m 538m 5768 S 1.3 3.4 2282:50 mysqld 37 root 20 0 0 0 0 S 0.3 0.0 0:20.57 events/2 60 root 20 0 0 0 0 S 0.3 0.0 5:44.67 kblockd/2 126 root 39 19 0 0 0 S 0.3 0.0 5:02.69 kipmi0 8161 shishir 20 0 240m 13m 8364 S 0.3 0.1 1:02.09 php-cgi 14107 delta 20 0 242m 13m 8576 S 0.3 0.1 0:35.41 php-cgi 30134 apache 20 0 397m 16m 4912 S 0.3 0.1 0:00.19 httpd 30910 apache 20 0 397m 16m 4648 S 0.3 0.1 0:00.04 httpd 30935 root 20 0 15160 1460 1000 R 0.3 0.0 0:00.04 top 1 root 20 0 19232 1336 1108 S 0.0 0.0 0:02.81 init 2 root 20 0 0 0 0 S 0.0 0.0 0:00.01 kthreadd

I have a PHP and mysql based website and i am using eaccelerator .

most of the parameters are still set to default .

The application used to get server within 2 seconds earlier but now it has started taking more than 8 to 15 seconds to server a page .

I am using this server for my vehicle tracking application hosting so the load on the server is round the clock. there are 300 devices sending request to this server twice every minute .

I am concerned about the response time as it is heavily affecting the users .

kindly let me know what can i do to improve the server performance .

Thanks

Status: 
Active

Comments

Howdy -- it appears that your CPU load is very low, and that you have plenty of RAM available.

That may mean that you're seeing an issue with the application itself, and the queries that are being run.

If you access MySQL from the command line as root, using "mysql -p", try running this command from the MySQL prompt:

show full processlist

What output do you receive when running that?

The response to show full processlist is as follows

mysql> show full processlist; +-----+--------+-----------+--------+---------+------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+--------+-----------+--------+---------+------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | hansen | localhost | hansen | Sleep | 1 | | NULL | | 2 | hansen | localhost | hansen | Sleep | 1 | | NULL | | 3 | hansen | localhost | hansen | Sleep | 122 | | NULL | | 4 | hansen | localhost | hansen | Sleep | 3 | | NULL | | 5 | hansen | localhost | hansen | Sleep | 34 | | NULL | | 6 | hansen | localhost | hansen | Sleep | 7 | | NULL | | 7 | hansen | localhost | hansen | Sleep | 2 | | NULL | | 9 | hansen | localhost | hansen | Sleep | 169 | | NULL | | 10 | hansen | localhost | hansen | Sleep | 24 | | NULL | | 13 | hansen | localhost | hansen | Sleep | 1 | | NULL | | 14 | hansen | localhost | hansen | Sleep | 28 | | NULL | | 15 | hansen | localhost | hansen | Sleep | 27 | | NULL | | 16 | hansen | localhost | hansen | Sleep | 31 | | NULL | | 18 | hansen | localhost | hansen | Sleep | 34 | | NULL | | 19 | hansen | localhost | hansen | Sleep | 10 | | NULL | | 22 | hansen | localhost | hansen | Sleep | 1 | | NULL | | 24 | hansen | localhost | hansen | Sleep | 17 | | NULL | | 25 | hansen | localhost | hansen | Sleep | 54 | | NULL | | 82 | hansen | localhost | hansen | Sleep | 17 | | NULL | | 83 | hansen | localhost | hansen | Sleep | 21 | | NULL | | 84 | hansen | localhost | hansen | Sleep | 3 | | NULL | | 85 | hansen | localhost | hansen | Sleep | 17 | | NULL | | 86 | hansen | localhost | hansen | Sleep | 60 | | NULL | | 88 | hansen | localhost | hansen | Sleep | 10 | | NULL | | 90 | hansen | localhost | hansen | Sleep | 30 | | NULL | | 91 | hansen | localhost | hansen | Sleep | 25 | | NULL | | 92 | hansen | localhost | hansen | Sleep | 62 | | NULL | | 93 | hansen | localhost | hansen | Sleep | 17 | | NULL | | 94 | hansen | localhost | hansen | Sleep | 14 | | NULL | | 98 | hansen | localhost | hansen | Sleep | 33 | | NULL | | 99 | hansen | localhost | hansen | Sleep | 16 | | NULL | | 101 | hansen | localhost | hansen | Sleep | 2 | | NULL | | 102 | hansen | localhost | hansen | Sleep | 41 | | NULL | | 103 | hansen | localhost | hansen | Sleep | 107 | | NULL | | 104 | hansen | localhost | hansen | Sleep | 10 | | NULL | | 105 | hansen | localhost | hansen | Query | 0 | Sending data | select device_id,alarm,dated from 25022014 where device_id = '012497003346917' and recordstatus = '1' and dated between '2014-02-25 22:49:04' and '2014-02-25 22:51:04' | | 107 | hansen | localhost | hansen | Sleep | 36 | | NULL | | 108 | hansen | localhost | hansen | Sleep | 2 | | NULL | | 109 | hansen | localhost | hansen | Query | 0 | Sending data | select device_id,alarm,dated from 25022014 where device_id = '012497003349572' and recordstatus = '1' and dated between '2014-02-25 22:49:04' and '2014-02-25 22:51:04' | | 110 | hansen | localhost | hansen | Sleep | 17 | | NULL | | 111 | hansen | localhost | hansen | Sleep | 33 | | NULL | | 112 | hansen | localhost | hansen | Sleep | 1 | | NULL | | 168 | hansen | localhost | hansen | Sleep | 31 | | NULL | | 169 | hansen | localhost | hansen | Sleep | 12 | | NULL | | 171 | hansen | localhost | hansen | Sleep | 51 | | NULL | | 172 | hansen | localhost | hansen | Sleep | 106 | | NULL | | 173 | hansen | localhost | hansen | Sleep | 25 | | NULL | | 174 | hansen | localhost | hansen | Sleep | 13 | | NULL | | 175 | hansen | localhost | hansen | Sleep | 21 | | NULL | | 177 | hansen | localhost | hansen | Sleep | 1 | | NULL | | 178 | hansen | localhost | hansen | Sleep | 6 | | NULL | | 180 | hansen | localhost | hansen | Sleep | 22 | | NULL | | 181 | root | localhost | NULL | Query | 0 | NULL | show full processlist | | 187 | hansen | localhost | hansen | Sleep | 17 | | NULL | | 188 | hansen | localhost | hansen | Sleep | 16 | | NULL | | 189 | hansen | localhost | hansen | Sleep | 3 | | NULL | +-----+--------+-----------+--------+---------+------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 56 rows in set (0.00 sec)

That's showing a number of database connections that appear to have "stacked up".

Since you're not seeing any CPU load, that likely points to an optimization issue in either the PHP code, or the database queries being run by the code.

To correct that, you may need to work with a web/database developer to track down the source of the bottleneck in the application, and update the application.

Also, some applications support using a tool such as memcache to cache lookups to MySQL, if your application supports that, configuring it to use that may help as well.