当前位置:Gxlcms > mysql > EasyXSS更新MySql表优化2013.03.12

EasyXSS更新MySql表优化2013.03.12

时间:2021-07-01 10:21:17 帮助过:54人阅读

今天很烦躁,没什么心情。vps客服说耗CPU很严重,索性跟了下xss.tw的性能问题。 top -c 使用top命令,发现,mysqld的进程占CPU居高不下,跟进mysql的语句: mysql show processlist;+-----+------+-----------+------+---------+------+--------------+-----

今天很烦躁,没什么心情。vps客服说耗CPU很严重,索性跟了下xss.tw的性能问题。


top -c

使用top命令,发现,mysqld的进程占CPU居高不下,跟进mysql的语句:


mysql> show processlist;
+-----+------+-----------+------+---------+------+--------------+----------------------------------------------------------+
| Id  | User | Host      | db   | Command | Time | State        | Info                                                     |
+-----+------+-----------+------+---------+------+--------------+----------------------------------------------------------+
| 213 | root | localhost | NULL | Query   |    0 | NULL         | show processlist                                         |
| 313 | xss  | localhost | xss  | Query   |    0 | Sending data | SELECT * FROM `xss_result_data` WHERE ( `rid` = 254565 ) |
| 316 | xss  | localhost | xss  | Query   |    1 | Sending data | SELECT * FROM `xss_result_data` WHERE ( `rid` = 702159 ) |
| 317 | xss  | localhost | xss  | Query   |    1 | Sending data | SELECT * FROM `xss_result_data` WHERE ( `rid` = 468659 ) |
| 319 | xss  | localhost | xss  | Query   |    1 | Sending data | SELECT * FROM `xss_result_data` WHERE ( `rid` = 797221 ) |
| 321 | xss  | localhost | xss  | Query   |    1 | Sending data | SELECT * FROM `xss_result_data` WHERE ( `rid` = 581303 ) |
+-----+------+-----------+------+---------+------+--------------+----------------------------------------------------------+
6 rows in set (0.00 sec)

发现耗时基本上都在1秒以上。 rid是外键,那么给它个索引。


ALTER TABLE `xss_result_data` ADD INDEX(`rid`)

运行这条mysql语句时,我才恍然大悟,原来设计EasyXSS的数据库时,其实没有任何一个表做过索引,怪不得性能那么差劲。随即继续跟进processlist,把耗时1秒以上的语句,对表字段适当的做了索引,速度就快起来了。查询processlist,都查不到语句在跑了,哈哈。


mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 54 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 54 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 54 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 54 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> 
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 54 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

现在cpu的负载明显降低很多很多很多很多很多。。。 原来客服说,长期大于5,才把server给关闭的。现在,0.1都不到。。


load average: 0.00, 0.01, 0.09

人气教程排行