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
Comments
Submitted by andreychek on Tue, 02/25/2014 - 11:06 Comment #1
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 processlistWhat output do you receive when running that?
Submitted by amitashok9999 on Tue, 02/25/2014 - 11:41 Comment #2
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,datedfrom25022014where device_id = '012497003346917' and recordstatus = '1' anddatedbetween '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 | selectdevice_id,alarm,datedfrom25022014where device_id = '012497003349572' and recordstatus = '1' anddatedbetween '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)Submitted by andreychek on Tue, 02/25/2014 - 11:58 Comment #3
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.