Linux

MySQL 查詢,2 台相似的伺服器,執行時間相差 2 分鐘

  • June 18, 2012

我有一個關於堆棧溢出的類似問題,但它似乎更多地與伺服器/mysql 設置相關而不是編碼。

下面的所有查詢都會在我們的開發伺服器上立即執行,因為它們最多可能需要 2 分 20 秒。

查詢執行時間似乎受到 LIKE 字元串的模糊不清的影響。如果他們與一個幾乎沒有比賽的國家/地區相匹配,則需要更少的時間,如果您對德國使用“ge”之類的東西 - 執行將需要更長的時間。但這並不總是這樣,有時它非常不穩定。

發送數據似乎是罪魁禍首,但這是為什麼以及這意味著什麼。生產中的記憶體看起來也很低(可用記憶體)?

生產:

Intel Quad Xeon E3-1220 3.1GHz

4GB DDR3

2x 1TB SATA in RAID1

網路速度 100Mb

Ubuntu

發展

Intel Core 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 請求,開發得到的很少,因為只有我自己和其他 1 個訪問它 - 4GB 的 RAM 是否會因為 apache 和 mysql 伺服器使用單台機器而耗盡?

生產:

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'

上述查詢的解釋計劃是,在開發和生產上執行產生相同的計劃。

+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+-------------+
| 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_user_id,project_company_has_user_company_id,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,  4 users,  load average: 1.94, 1.23, 0.87
Tasks: 160 total,   2 running, 157 sleeping,   0 stopped,   1 zombie
Cpu(s):  0.2%us, 50.6%sy,  0.0%ni, 49.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4037868k total,  3834300k used,   203568k free,   243736k buffers
Swap:  3905528k total,   265384k used,  3640144k free,  1207804k cached

但這並不能很好地代表生產的正常狀態,所以這裡是從今天開始執行查詢之外的一個抓取。

top - 11:04:58 up 79 days,  7:33,  4 users,  load average: 0.39, 0.58, 0.76
Tasks: 156 total,   1 running, 155 sleeping,   0 stopped,   0 zombie
Cpu(s):  3.3%us,  2.8%sy,  0.0%ni, 93.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4037868k total,  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,   2 running, 208 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.1%us,  0.2%sy,  0.0%ni, 99.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4111972k total,  1821100k used,  2290872k free,   238860k buffers
Swap:  4183036k total,    66472k used,  4116564k free,   921072k cached

差異可能來自:

  1. 在 PROD 伺服器(Quad Xeon E3-1220)上,您有一個 RAID1 磁碟設置,這可能會降低查詢速度,因為它正在寫入 2 個磁碟並從 2 個磁碟讀取http://en.wikipedia.org/wiki/RAID,這意味著送出時性能較慢,讀取操作時性能更高(選擇)。根據您的應用程序,這可能是好事也可能是壞事……
  2. 兩台伺服器上的交換分區不同,似乎使用的 RAM /swap 使用與 PROD / DEV 系統不同(即使它們具有相同數量的 ram )。我會使用 ps aux 檢查正在執行的程序並比較列表,因為您會看到在 prod 上執行了更多程序。
  3. 請看看你在 mysql / prod server 上有多少查詢的並發連接。
  4. 請查看 prod 和 dev 的磁碟速度差異。
  5. 它們是否具有相同的 OS/mysql 版本,並且 innodb 在兩種環境中都作為它們的引擎執行嗎?

引用自:https://serverfault.com/questions/394314