关于mysql & mariadb的一些疑问。

大圈 发布于 2016/05/18 11:42
阅读 499
收藏 0
各位大神 请教个问题  我用vmware虚拟机测试的,host1和host2硬件环境一模一样, LNMP环境下:  host1的数据库是mysql5.6 , host2的数据库是mariadb10.1.13 ,数据库都没有做任何优化和改动。两台主机php-fpm开启的最大进程数相同。ab测试请求同样的php页面,但是host1在20s左右处理完,host2则需要120s左右。最后我

查看了一下主要是host2的top的wait占比非常大,基本都在80%。
Cpu0  :  5.3%us,  2.8%sy,  0.0%ni,  0.0%id, 90.5%wa,  0.2%hi,  1.2%si,  0.0%st
Cpu1  :  4.4%us,  1.1%sy,  0.0%ni,  0.0%id, 94.5%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   1019964k total,   956520k used,    63444k free,     1012k buffers
Swap:  2064380k total,   455948k used,  1608432k free,    20016k cached

然后进到host2的数据库(mariadb)里show processlist;

可以看到:
| 212 | root | localhost | wordpress | Query   |    1 | Creating tmp table | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id =  |    0.000 |
| 213 | root | localhost | wordpress | Query   |    1 | Creating tmp table | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id =  |    0.000 |
| 215 | root | localhost | wordpress | Query   |    1 | Creating tmp table | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id =  |    0.000 |
| 227 | root | localhost | wordpress | Query   |    1 | Creating tmp table | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id =  |    0.000 |
| 228 | root | localhost | wordpress | Query   |    1 | Creating tmp table | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id =  |    0.000 |
| 229 | root | localhost | wordpress | Sleep   |    1 |                    | NULL                                                                                                 |    0.000 |
| 230 | root | localhost | wordpress | Query   |    1 | Creating tmp table | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id =  |    0.000 |
| 231 | root | localhost | wordpress | Query   |    1 | Creating tmp table | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id =  |    0.000 |
| 232 | root | localhost | wordpress | Sleep   |    1 |                    | NULL                                                                                                 |    0.000 |
| 233 | root | localhost | wordpress | Query   |    1 | Creating tmp table | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id =  |    0.000 |


| 170 | root | localhost | wordpress | Query   |    8 | removing tmp table | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id =  |    0.000 |
| 170 | root | localhost | wordpress | Query   |    7 | removing tmp table | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id =  |    0.000 |
| 161 | root | localhost | wordpress | Query   |    7 | Copying to tmp table | SELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM wp_posts  WHE |    0.000 |
| 162 | root | localhost | wordpress | Query   |    4 | Sorting result       | SELECT SQL_CALC_FOUND_ROWS wp_comments.comment_ID FROM wp_comments  WHERE ( comment_approved = '1' ) |    0.000 |



| 184 | root | localhost | wordpress | Query   |    2 | statistics           | SELECT option_value FROM wp_options WHERE option_name = 'theme_mods_twentysixteen' LIMIT 1           |    0.000 |
| 185 | root | localhost | wordpress | Query   |    9 | Sending data         | SELECT p.ID FROM wp_posts AS p  WHERE p.post_date < '2016-05-17 14:34:59' AND p.post_type = 'post'   |    0.000 |
| 186 | root | localhost | wordpress | Query   |    8 | statistics           | SELECT option_value FROM wp_options WHERE option_name = 'theme_switched' LIMIT 1                     |    0.000 |
| 188 | root | localhost | wordpress | Query   |    6 | statistics           | SELECT option_value FROM wp_options WHERE option_name = 'current_theme' LIMIT 1                      |    0.000 |
| 190 | root | localhost | wordpress | Query   |    9 | Sending data         | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (4) ORDER BY meta_id ASC      |    0.000 |
| 195 | root | localhost | wordpress | Query   |    5 | statistics           | SELECT option_value FROM wp_options WHERE option_name = 'theme_mods_twentysixteen' LIMIT 1           |    0.000 |
| 197 | root | localhost | wordpress | Query   |    6 | Sorting result       | SELECT  t.*, tt.* FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WH |    0.000 |
| 198 | root | localhost | wordpress | Query   |    5 | statistics           | SELECT option_value FROM wp_options WHERE option_name = 'theme_mods_twentysixteen' LIMIT 1           |    0.000 |
| 212 | root | localhost | wordpress | Query   |    9 | statistics           | SELECT * FROM wp_users WHERE ID = '1'                                                                |    0.000 |
| 213 | root | localhost | wordpress | Query   |    6 | statistics           | SELECT option_value FROM wp_options WHERE option_name = 'theme_mods_twentysixteen' LIMIT 1           |    0.000 |
| 215 | root | localhost | wordpress | Query   |    5 | statistics           | SELECT option_value FROM wp_options WHERE option_name = 'theme_mods_twentysixteen' LIMIT 1           |    0.000 |
| 227 | root | localhost | wordpress | Query   |    9 | Sending data         | SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (1) ORDER BY umeta_id ASC     |    0.000 |
| 228 | root | localhost | wordpress | Query   |    9 | statistics           | SELECT * FROM wp_users WHERE ID = '1'                                                                |    0.000 |
| 229 | root | localhost | wordpress | Query   |    9 | Copying to tmp table | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id =  |    0.000 |
| 230 | root | localhost | wordpress | Query   |    9 | statistics           | SELECT * FROM wp_users WHERE ID = '1'                                                                |    0.000 |
| 231 | root | localhost | wordpress | Query   |    9 | Sending data         | SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (1) ORDER BY umeta_id ASC     |    0.000 |
| 232 | root | localhost | wordpress | Query   |    9 | Sending data         | SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'                              |    0.000 |
| 233 | root | localhost | wordpress | Query   |    9 | statistics           | SELECT option_value FROM wp_options WHERE option_name = 'theme_mods_twentysixteen' LIMIT 1           |    0.000 |
| 234 | root | localhost | wordpress | Query   |    5 | Opening tables       | SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'                              |    0.000 |
| 235 | root | localhost | wordpress | Query   |    6 | Opening tables       | SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'                              |    0.000 |
| 236 | root | localhost | wordpress | Query   |    6 | Opening tables       | SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'                              |    0.000 |
| 237 | root | localhost | wordpress | Query   |    4 | Opening tables       | SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'                              |    0.000 |

主要就是这个东西都在进行磁盘IO,但是为什么我查看mysql时就没有这些线程呢,基本都是下面的状态:

| 198 | root                 | localhost:38951 | wordpress | Query   |    0 | Sending data   | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id =


| 12045 | root | localhost:53268 | wordpress | Sleep   |    0 |       | NULL             |
| 12047 | root | localhost:53272 | wordpress | Sleep   |    0 |       | NULL             |
| 12049 | root | localhost:53276 | wordpress | Sleep   |    0 |       | NULL             |
| 12050 | root | localhost:53283 | wordpress | Sleep   |    0 |       | NULL             |
这种状态,说明执行时间非常短啊。

哪位前辈给指点一下,mariadb怎么有这么多的处理过程呢?还是说是我的其他方面原因。
加载中
返回顶部
顶部