下面的查询都会立即在我们的开发服务器上执行,因为它们最多可能需要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 limitsVariables 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 limitsVariables 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/secsudo 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/secServer 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/secServer 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.78Tasks: 162 total,2 running,160 sleePing,0 stopped,0 zombIEcpu(s): 0.1%us,50.4%sy,0.0%ni,49.5%ID,0.0%wa,0.0%hi,0.0%si,0.0%stMem: 4037868k total,3772580k used,265288k free,243704k buffersSwap: 3905528k total,265384k used,3640144k free,1207944k cachedtop - 15:44:31 up 78 days,12:13,load average: 1.94,1.23,0.87Tasks: 160 total,157 sleePing,1 zombIEcpu(s): 0.2%us,50.6%sy,49.3%ID,3834300k used,203568k free,243736k buffersSwap: 3905528k total,1207804k cached
但这不是生产正常状态的良好表示,因此这里是从今天开始执行查询之外的它.
top - 11:04:58 up 79 days,7:33,load average: 0.39,0.58,0.76Tasks: 156 total,1 running,155 sleePing,0 zombIEcpu(s): 3.3%us,2.8%sy,93.9%ID,3676136k used,361732k free,271480k buffersSwap: 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.06Tasks: 210 total,208 sleePing,0.2%sy,99.7%ID,0.0%stMem: 4111972k total,1821100k used,2290872k free,238860k buffersSwap: 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在两种环境中都作为引擎运行？
总结以上是内存溢出为你收集整理的linux – MySQL查询,2个类似的服务器,执行时间差2分钟全部内容，希望文章能够帮你解决linux – MySQL查询,2个类似的服务器,执行时间差2分钟所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）