linux – MySQL查询,2个类似的服务器,执行时间差2分钟

前端之家收集整理的这篇文章主要介绍了linux – MySQL查询,2个类似的服务器,执行时间差2分钟前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有一个关于堆栈溢出的类似问题,但它似乎比编码更多的服务器/ mysql设置.

下面的查询都会立即在我们的开发服务器上执行,因为它们最多可能需要2分20秒.

查询执行时间似乎受到LIKE字符串所在的家庭模糊的影响.如果他们与匹配较少的国家匹配,则需要的时间较少,如果您对德国使用“ge”之类的东西,则执行时间会更长.但这并不总是那样,有时它很不稳定.

发送数据似乎是罪魁祸首,但其原因和含义是什么.生产中的内存看起来很低(可用内存)?

生产:

Intel Quad Xeon E3-1220 3.1GHz
4GB DDR3
RAID1中的2x 1TB SATA
网速100Mb
Ubuntu的

发展

英特尔酷睿i3-2100,2C / 4T,3.10GHz
500 GB SATA – 无RAID
4GB DDR3

更新2:
MysqLtuner输出

[PROD]

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MysqLTuner script
[OK] Currently running supported MysqL version 5.1.61-0ubuntu0.10.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 103M (Tables: 180)
[--] Data in InnoDB tables: 491M (Tables: 19)
[!!] Total fragmented tables: 38

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 77d 4h 6m 1s (53M q [7.968 qps],14M conn,TX: 87B,RX: 12B)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 463.8M (11% of installed RAM)
[OK] Slow queries: 0% (12K/53M)
[OK] Highest usage of available connections: 22% (34/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/10.6M
[OK] Key buffer hit rate: 98.7% (162M cached / 2M reads)
[OK] Query cache efficiency: 20.7% (7M cached / 36M selects)
[!!] Query cache prunes per day: 3934
[OK] Sorts requiring temporary tables: 1% (3K temp sorts / 230K sorts)
[!!] Joins performed without indexes: 71068
[OK] Temporary tables created on disk: 24% (3M on disk / 13M total)
[OK] Thread cache hit rate: 99% (690 created / 14M connections)
[!!] Table cache hit rate: 0% (64 open / 85M opened)
[OK] Open file limit used: 12% (128/1K)
[OK] Table locks acquired immediately: 99% (16M immediate / 16M locks)
[!!] InnoDB data size / buffer pool: 491.9M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 16M)
    join_buffer_size (> 128.0K,or always use indexes with joins)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 491M)

[dev的]

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MysqLTuner script
[OK] Currently running supported MysqL version 5.1.62-0ubuntu0.11.10.1
[!!] Switch to 64-bit OS - MysqL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 185M (Tables: 632)
[--] Data in InnoDB tables: 967M (Tables: 38)
[!!] Total fragmented tables: 73

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 2h 26m 9s (5K q [0.058 qps],1K conn,TX: 4M,RX: 1M)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 463.8M (11% of installed RAM)
[OK] Slow queries: 0% (0/5K)
[OK] Highest usage of available connections: 1% (2/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/18.6M
[OK] Key buffer hit rate: 99.9% (60K cached / 36 reads)
[OK] Query cache efficiency: 44.5% (1K cached / 2K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 44 sorts)
[OK] Temporary tables created on disk: 24% (162 on disk / 666 total)
[OK] Thread cache hit rate: 99% (2 created / 1K connections)
[!!] Table cache hit rate: 1% (64 open / 4K opened)
[OK] Open file limit used: 8% (88/1K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[!!] InnoDB data size / buffer pool: 967.7M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    table_cache (> 64)
    innodb_buffer_pool_size (>= 967M)

更新1:

在测试此处列出的查询时,通常只会发生一个其他查询,通常没有.

因为生产实际上是处理apache请求,开发变得非常少,因为它只有我自己和另外一个访问它的人 – 通过将这台机器用于apache和MysqL服务器,4GB的RAM是否会变得疲惫不堪?

生产:

sudo hdparm -tT /dev/sda
/dev/sda:
 Timing cached reads:   24872 MB in  2.00 seconds = 12450.72 MB/sec
 Timing buffered disk reads:  368 MB in  3.00 seconds = 122.49 MB/sec

sudo hdparm -tT /dev/sdb
/dev/sdb:
 Timing cached reads:   24786 MB in  2.00 seconds = 12407.22 MB/sec
 Timing buffered disk reads:  350 MB in  3.00 seconds = 116.53 MB/sec

Server version(MysqL + ubuntu versions): 5.1.61-0ubuntu0.10.04.1

发展:

sudo hdparm -tT /dev/sda
/dev/sda:
 Timing cached reads:   10632 MB in  2.00 seconds = 5319.40 MB/sec
 Timing buffered disk reads: 400 MB in  3.01 seconds = 132.85 MB/sec

Server version(MysqL + ubuntu versions): 5.1.62-0ubuntu0.11.10.1

原始数据:

这个查询不是有问题的查询,而是相关的,因为它很容易发布.

SELECT 
    f.form_question_has_answer_id 
FROM 
    form_question_has_answer f 
INNER JOIN 
    project_company_has_user p ON f.form_question_has_answer_user_id = p.project_company_has_user_user_id 
INNER JOIN 
    company c ON p.project_company_has_user_company_id = c.company_id 
INNER JOIN 
    project p2 ON p.project_company_has_user_project_id = p2.project_id 
INNER JOIN 
    user u ON p.project_company_has_user_user_id = u.user_id 
INNER JOIN 
    form f2 ON p.project_company_has_user_project_id = f2.form_project_id 
WHERE 
    (f2.form_template_name = 'custom' AND p.project_company_has_user_garbage_collection = 0 AND p.project_company_has_user_project_id = '29') AND (LCASE(c.company_country) LIKE '%ge%' OR LCASE(c.company_country) LIKE '%abcde%') AND f.form_question_has_answer_form_id = '174'

并且针对上述查询的解释计划是,在dev和production上运行产生相同的计划.

+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys                                                                                                                                | key                              | key_len | ref                                                | rows | Extra       |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+-------------+
|  1 | SIMPLE      | p2    | const  | PRIMARY                                                                                                                                      | PRIMARY                          | 4       | const                                              |    1 | Using index |
|  1 | SIMPLE      | f     | ref    | form_question_has_answer_form_id,form_question_has_answer_user_id                                                                            | form_question_has_answer_form_id | 4       | const                                              |  796 | Using where |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                                                                                                                                      | PRIMARY                          | 4       | new_klarents.f.form_question_has_answer_user_id    |    1 | Using index |
|  1 | SIMPLE      | p     | ref    | project_company_has_user_unique_key,project_company_has_user_user_id,project_company_has_user_company_id,project_company_has_user_project_id | project_company_has_user_user_id | 4       | new_klarents.f.form_question_has_answer_user_id    |    1 | Using where |
|  1 | SIMPLE      | f2    | ref    | form_project_id                                                                                                                              | form_project_id                  | 4       | const                                              |   15 | Using where |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY                                                                                                                                      | PRIMARY                          | 4       | new_klarents.p.project_company_has_user_company_id |    1 | Using where |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+-------------+

查询需要2分钟~20秒才能执行.

在服务器上运行的查询是这样的:

SELECT 
    COUNT(*) AS num_results 
FROM (SELECT 
        f.form_question_has_answer_id 
    FROM 
        form_question_has_answer f 
    INNER JOIN 
        project_company_has_user p ON f.form_question_has_answer_user_id = p.project_company_has_user_user_id 
    INNER JOIN 
        company c ON p.project_company_has_user_company_id = c.company_id 
    INNER JOIN 
        project p2 ON p.project_company_has_user_project_id = p2.project_id 
    INNER JOIN 
        user u ON p.project_company_has_user_user_id = u.user_id 
    INNER JOIN 
        form f2 ON p.project_company_has_user_project_id = f2.form_project_id 
    WHERE 
        (f2.form_template_name = 'custom' AND p.project_company_has_user_garbage_collection = 0 AND p.project_company_has_user_project_id = '29') AND (LCASE(c.company_country) LIKE '%ge%' OR LCASE(c.company_country) LIKE '%abcde%') AND f.form_question_has_answer_form_id = '174' 
    GROUP BY 
        f.form_question_has_answer_id;) dctrn_count_query;

随着解释计划(在开发和生产上同样相同):

+----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+------------------------------+
    | id | select_type | table | type   | possible_keys                                                                                                                                                                            | key                              | key_len | ref                                                | rows | Extra                        |
    +----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+------------------------------+
    |  1 | PRIMARY     | NULL  | NULL   | NULL                                                                                                                                                                                     | NULL                             | NULL    | NULL                                               | NULL | Select tables optimized away |
    |  2 | DERIVED     | p2    | const  | PRIMARY                                                                                                                                                                                  | PRIMARY                          | 4       |                                                    |    1 | Using index                  |
    |  2 | DERIVED     | f     | ref    | form_question_has_answer_form_id,form_question_has_answer_user_id                                                                                                                        | form_question_has_answer_form_id | 4       |                                                    |  797 | Using where                  |
    |  2 | DERIVED     | p     | ref    | project_company_has_user_unique_key,project_company_has_user_project_id,project_company_has_user_garbage_collection | project_company_has_user_user_id | 4       | new_klarents.f.form_question_has_answer_user_id    |    1 | Using where                  |
    |  2 | DERIVED     | f2    | ref    | form_project_id                                                                                                                                                                          | form_project_id                  | 4       |                                                    |   15 | Using where                  |
    |  2 | DERIVED     | c     | eq_ref | PRIMARY                                                                                                                                                                                  | PRIMARY                          | 4       | new_klarents.p.project_company_has_user_company_id |    1 | Using where                  |
    |  2 | DERIVED     | u     | eq_ref | PRIMARY                                                                                                                                                                                  | PRIMARY                          | 4       | new_klarents.p.project_company_has_user_user_id    |    1 | Using where; Using index     |
    +----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+------------------------------+

在生产服务器上,我的信息如下.

执行时:

+-------------+
| num_results |
+-------------+
|           3 |
+-------------+
1 row in set (2 min 14.28 sec)

显示资料:

+--------------------------------+------------+
| Status                         | Duration   |
+--------------------------------+------------+
| starting                       |   0.000016 |
| checking query cache for query |   0.000057 |
| opening tables                 |   0.004388 |
| System lock                    |   0.000003 |
| Table lock                     |   0.000036 |
| init                           |   0.000030 |
| optimizing                     |   0.000016 |
| statistics                     |   0.000111 |
| preparing                      |   0.000022 |
| executing                      |   0.000004 |
| Sorting result                 |   0.000002 |
| Sending data                   | 136.213836 |
| end                            |   0.000007 |
| query end                      |   0.000002 |
| freeing items                  |   0.004273 |
| storing result in query cache  |   0.000010 |
| logging slow query             |   0.000001 |
| logging slow query             |   0.000002 |
| cleaning up                    |   0.000002 |
+--------------------------------+------------+

在开发时,结果如下.

+-------------+
| num_results |
+-------------+
|           3 |
+-------------+
1 row in set (0.08 sec)

同样是此查询配置文件

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000022 |
| checking query cache for query | 0.000148 |
| opening tables                 | 0.000025 |
| System lock                    | 0.000008 |
| Table lock                     | 0.000101 |
| optimizing                     | 0.000035 |
| statistics                     | 0.001019 |
| preparing                      | 0.000047 |
| executing                      | 0.000008 |
| Sorting result                 | 0.000005 |
| Sending data                   | 0.086565 |
| init                           | 0.000015 |
| optimizing                     | 0.000006 |
| executing                      | 0.000020 |
| end                            | 0.000004 |
| query end                      | 0.000004 |
| freeing items                  | 0.000028 |
| storing result in query cache  | 0.000005 |
| removing tmp table             | 0.000008 |
| closing tables                 | 0.000008 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000005 |
+--------------------------------+----------+

如果我删除用户和/或项目内部连接,查询将减少到30秒.

我有的最后一点信息:

MysqLserver和Apache都在同一个盒子里,只有一个盒子可供生产.

顶部的产量:之前和之后后.

top - 15:43:25 up 78 days,12:11,4 users,load average: 1.42,0.99,0.78
Tasks: 162 total,2 running,160 sleeping,0 stopped,0 zombie
cpu(s):  0.1%us,50.4%sy,0.0%ni,49.5%id,0.0%wa,0.0%hi,0.0%si,0.0%st
Mem:   4037868k total,3772580k used,265288k free,243704k buffers
Swap:  3905528k total,265384k used,3640144k free,1207944k cached

top - 15:44:31 up 78 days,12:13,load average: 1.94,1.23,0.87
Tasks: 160 total,157 sleeping,1 zombie
cpu(s):  0.2%us,50.6%sy,49.3%id,3834300k used,203568k free,243736k buffers
Swap:  3905528k total,1207804k cached

但这不是生产正常状态的良好表示,因此这里是从今天开始执行查询之外的它.

top - 11:04:58 up 79 days,7:33,load average: 0.39,0.58,0.76
Tasks: 156 total,1 running,155 sleeping,0 zombie
cpu(s):  3.3%us,2.8%sy,93.9%id,3676136k used,361732k free,271480k buffers
Swap:  3905528k total,268736k used,3636792k free,1063432k cached

发展:这一期间或之后不会改变.

top - 15:47:07 up 110 days,22:11,7 users,load average: 0.17,0.07,0.06
Tasks: 210 total,208 sleeping,0.2%sy,99.7%id,0.0%st
Mem:   4111972k total,1821100k used,2290872k free,238860k buffers
Swap:  4183036k total,66472k used,4116564k free,921072k cached

解决方法

差异可能来自:

>在PROD服务器(Quad Xeon E3-1220)上,你有一个RAID1磁盘设置,它可能会减慢查询速度,因为它写入2个磁盘并从2个磁盘读取http://en.wikipedia.org/wiki/RAID,这意味着提交性能降低,读取操作性能更高(选择 ) .根据您的应用,这可能是好事还是坏事……>两个服务器上的交换分区不同,看起来RAM使用/交换使用与PROD / DEV系统不同(即使它们具有相同数量的ram).我将使用ps aux检查运行进程并比较列表,因为您将看到有更多进程在prod上运行.>请查看您在MysqL / prod服务器上有多少个querry的并发连接.>请查看prod和dev中的磁盘速度差异.>他们是否拥有相同的OS / MysqL版本,并且innodb在两种环境中都作为引擎运行?

原文链接:https://www.f2er.com/linux/396880.html

猜你在找的Linux相关文章