MySQL查询随机滞后

2020/12/19 00:21 · php ·  · 0评论

我有一个查询,看起来像这样:

SELECT id FROM user WHERE id='47'

像这样使用概要分析数据时,索引已编入索引,并且此查询的读取总是很快。

SET profiling = 1;
SHOW PROFILES;

查询总是在大约0.0002秒内执行。

但是,如果我从PHP方面分析查询,如下所示:

$current = microtime(true);
$data = $conn->query($full_query);
$elapsed = microtime(true) - $current;

然后,偶尔可能有50个查询中有1个需要约0.2秒。但是,在我的测试脚本中,我有代码进行测试,该代码使用SET profiling = 1来配置查询。并且即使通过PDO的PHP往返行程可能是0.2秒,查询时间仍为0.0002。

我知道的或未引起问题的事情:

  1. 查询并不慢。当我查看来自同一查询运行的同一查询,在PHP中进行剖析并使用SET PROFILING进行剖析时,该查询始终是快速的,并且即使从PHP端显示需要0.2秒,也永远不会登录到慢速查询日志中。
  2. 这与“跳过名称解析”无关-这是不一致的,并且我已经使用“跳过名称解析”功能
  3. 这与查询缓存无关,两者都存在该行为
  4. 即使从缓存出来的查询上也会发生此行为。
  5. 该查询实际上并未选择ID,但我使用此查询进行测试以表明它不是磁盘访问问题,因为该字段已被明确索引。
  6. 该表只有10-20兆,索引为1兆。机器显示很少的负载,并且innodb没有使用所有缓冲区。
  7. 该表是针对除我的测试查询之外没有其他活动的表进行测试的。

是否有人对要检查的内容有任何想法?在我看来,这是一个网络问题,但是我需要能够看到它并找到解决问题的方法,所以我没有足够的地方进行下一步检查了。有任何想法吗?

我会分析机器。

您说这种情况每50次发生1次,并且每个查询的基准时间为0.2秒。您应该能够放在屏幕顶部,然后在PHP中运行查询循环以对RDBMS进行负载测试并收集性能统计信息。

50 * 0.2 =10 seconds由于您的“ 50分之一”统计信息可能基于手动运行的单个查询-根据我在您的描述中所读的内容,您可能需要运行多个尝试30秒和90秒的负载测试。

在这段时间内,观看top过程画面。按,按CPU排序P每次按“ P”键都会更改进程CPU消耗的排序顺序,因此请确保您最消耗CPU的开销。(按M内存使用情况排序。请查看手册页以获取更多信息)

寻找在负载测试期间冒泡到顶部的任何东西。您应该会看到更高的跃点-但是暂时。
(请注意,这样的过程可能不会达到列表的顶部,但不一定,但是可能仍会引入足够的磁盘负载或其他活动来使MySQL服务器落后)

我在系统上注意到了同样的现象。通常需要一毫秒的查询会突然花费1-2秒。我所有的情况都是简单的单表INSERT / UPDATE / REPLACE语句---不在任何SELECT上。没有明显的负载,锁定或线程堆积。

我曾怀疑这是由于清除了脏页,清除磁盘更改或某些隐藏的互斥锁所致,但是我还没有缩小范围。

也排除

  1. 服务器负载-与高负载无关

  2. 加载引擎-发生在InnoDB / MyISAM / Memory MySQL查询中

  3. 缓存-无论开启还是关闭

  4. 日志旋转-事件中没有相关性

已经使用查询分析器对您有好处。如果您使用的是MySQL 5.6,则还可以在PERFORMANCE_SCHEMA中访问许多新的性能度量与查询事件探查器相比,它具有测量更多细节的功能,并且还可以进行全局测量,而不仅仅是一个会话。据报道,P_S将替换查询分析器。

为了诊断您的问题,我将首先确认或排除TCP / IP问题。例如,测试PHP脚本,以查看通过UNIX套接字进行连接时它是否获得相同的间歇延迟。您可以通过连接来做到这一点,localhost这意味着PHP脚本必须与数据库在同一服务器上运行。如果绕过TCP / IP时问题消失了,这将告诉您根本原因很可能是TCP / IP。

如果您处于虚拟环境(如云托管)中,则由于同一个云的其他用户间歇性地消耗了所有带宽,因此您很容易遇到性能变化。这是云的缺点之一。

如果您怀疑这是TCP / IP问题,则可以独立于PHP或MySQL测试TCP / IP延迟。现成的典型工具包括pingtraceroute但是还有很多其他的您也可以使用netcat测试网络速度使用可以随时间重复测量的工具,因为听起来大多数时候您的性能不错,偶尔也会出现故障。

另一个可能是故障出在PHP。您可以尝试使用XHProfPHP进行性能分析,以了解它在哪里花费时间。

尝试找出问题所在。运行一个像这样的小脚本:

https://drive.google.com/file/d/0B0P3JM22IdYZYXY3Y0h5QUg2WUk/edit?usp=sharing

...了解链中哪些步骤令人兴奋。如果您安装了ssh2,它也会ps axu在运行时间最长的测试循环后立即返回,以查看运行情况。

在我的家庭开发盒上针对localhost运行,结果如下所示:

Array
(
    [tests summary] => Array
        (
            [host_ping] => Array
                (
                    [total_time] => 0.010216474533081
                    [max_time] => 0.00014901161193848
                    [min_time] => 9.7036361694336E-5
                    [tests] => 100
                    [failed] => 0
                    [last_run] => 9.8943710327148E-5
                    [average] => 0.00010216474533081
                )

            [db_connect] => Array
                (
                    [total_time] => 0.11583232879639
                    [max_time] => 0.0075201988220215
                    [min_time] => 0.0010058879852295
                    [tests] => 100
                    [failed] => 0
                    [last_run] => 0.0010249614715576
                    [average] => 0.0011583232879639
                )

            [db_select_db] => Array
                (
                    [total_time] => 0.011744260787964
                    [max_time] => 0.00031399726867676
                    [min_time] => 0.00010991096496582
                    [tests] => 100
                    [failed] => 0
                    [last_run] => 0.0001530647277832
                    [average] => 0.00011744260787964
                )

            [db_dataless_query] => Array
                (
                    [total_time] => 0.023221254348755
                    [max_time] => 0.00026106834411621
                    [min_time] => 0.00021100044250488
                    [tests] => 100
                    [failed] => 0
                    [last_run] => 0.00021481513977051
                    [average] => 0.00023221254348755
                )

            [db_data_query] => Array
                (
                    [total_time] => 0.075078248977661
                    [max_time] => 0.0010559558868408
                    [min_time] => 0.00023698806762695
                    [tests] => 100
                    [failed] => 0
                    [last_run] => 0.00076413154602051
                    [average] => 0.00075078248977661
                )

        )

    [worst full loop] => 0.039211988449097
    [times at worst loop] => Array
        (
            [host_ping] => 0.00014400482177734
            [db_connect] => 0.0075201988220215
            [db_select_db] => 0.00012803077697754
            [db_dataless_query] => 0.00023698806762695
            [db_data_query] => 0.00023698806762695
        )

    [ps_at_worst] => USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
root         1  0.0  0.0   2884  1368 ?        Ss   Sep19   0:29 /sbin/init
root         2  0.0  0.0      0     0 ?        S    Sep19   0:00 [kthreadd]
root         3  0.0  0.0      0     0 ?        S    Sep19   0:00 [migration/0]
root         4  0.0  0.0      0     0 ?        S    Sep19   0:06 [ksoftirqd/0]
root         5  0.0  0.0      0     0 ?        S    Sep19   0:00 [migration/0]
root         6  0.0  0.0      0     0 ?        S    Sep19   0:25 [watchdog/0]
root         7  0.0  0.0      0     0 ?        S    Sep19   7:42 [events/0]
root         8  0.0  0.0      0     0 ?        S    Sep19   0:00 [cgroup]
root         9  0.0  0.0      0     0 ?        S    Sep19   0:00 [khelper]
root        10  0.0  0.0      0     0 ?        S    Sep19   0:00 [netns]
root        11  0.0  0.0      0     0 ?        S    Sep19   0:00 [async/mgr]
root        12  0.0  0.0      0     0 ?        S    Sep19   0:00 [pm]
root        13  0.0  0.0      0     0 ?        S    Sep19   0:23 [sync_supers]
root        14  0.0  0.0      0     0 ?        S    Sep19   0:24 [bdi-default]
root        15  0.0  0.0      0     0 ?        S    Sep19   0:00 [kintegrityd/0]
root        16  0.0  0.0      0     0 ?        S    Sep19   0:47 [kblockd/0]
root        17  0.0  0.0      0     0 ?        S    Sep19   0:00 [kacpid]
root        18  0.0  0.0      0     0 ?        S    Sep19   0:00 [kacpi_notify]
root        19  0.0  0.0      0     0 ?        S    Sep19   0:00 [kacpi_hotplug]
root        20  0.0  0.0      0     0 ?        S    Sep19   0:00 [ata/0]
root        21  0.0  0.0      0     0 ?        S    Sep19   0:00 [ata_aux]
root        22  0.0  0.0      0     0 ?        S    Sep19   0:00 [ksuspend_usbd]
root        23  0.0  0.0      0     0 ?        S    Sep19   0:00 [khubd]
root        24  0.0  0.0      0     0 ?        S    Sep19   0:00 [kseriod]
root        25  0.0  0.0      0     0 ?        S    Sep19   0:00 [md/0]
root        26  0.0  0.0      0     0 ?        S    Sep19   0:00 [md_misc/0]
root        27  0.0  0.0      0     0 ?        S    Sep19   0:01 [khungtaskd]
root        28  0.0  0.0      0     0 ?        S    Sep19   0:00 [kswapd0]
root        29  0.0  0.0      0     0 ?        SN   Sep19   0:00 [ksmd]
root        30  0.0  0.0      0     0 ?        S    Sep19   0:00 [aio/0]
root        31  0.0  0.0      0     0 ?        S    Sep19   0:00 [crypto/0]
root        36  0.0  0.0      0     0 ?        S    Sep19   0:00 [kthrotld/0]
root        38  0.0  0.0      0     0 ?        S    Sep19   0:00 [kpsmoused]
root        39  0.0  0.0      0     0 ?        S    Sep19   0:00 [usbhid_resumer]
root        70  0.0  0.0      0     0 ?        S    Sep19   0:00 [iscsi_eh]
root        74  0.0  0.0      0     0 ?        S    Sep19   0:00 [cnic_wq]
root        75  0.0  0.0      0     0 ?        S<   Sep19   0:00 [bnx2i_thread/0]
root        87  0.0  0.0      0     0 ?        S    Sep19   0:00 [kstriped]
root       123  0.0  0.0      0     0 ?        S    Sep19   0:00 [ttm_swap]
root       130  0.0  0.0      0     0 ?        S<   Sep19   0:04 [kslowd000]
root       131  0.0  0.0      0     0 ?        S<   Sep19   0:05 [kslowd001]
root       231  0.0  0.0      0     0 ?        S    Sep19   0:00 [scsi_eh_0]
root       232  0.0  0.0      0     0 ?        S    Sep19   0:00 [scsi_eh_1]
root       291  0.0  0.0      0     0 ?        S    Sep19   0:35 [kdmflush]
root       293  0.0  0.0      0     0 ?        S    Sep19   0:00 [kdmflush]
root       313  0.0  0.0      0     0 ?        S    Sep19   2:11 [jbd2/dm-0-8]
root       314  0.0  0.0      0     0 ?        S    Sep19   0:00 [ext4-dio-unwrit]
root       396  0.0  0.0   2924  1124 ?        S<s  Sep19   0:00 /sbin/udevd -d
root       705  0.0  0.0      0     0 ?        S    Sep19   0:00 [kdmflush]
root       743  0.0  0.0      0     0 ?        S    Sep19   0:00 [jbd2/sda1-8]
root       744  0.0  0.0      0     0 ?        S    Sep19   0:00 [ext4-dio-unwrit]
root       745  0.0  0.0      0     0 ?        S    Sep19   0:00 [jbd2/dm-2-8]
root       746  0.0  0.0      0     0 ?        S    Sep19   0:00 [ext4-dio-unwrit]
root       819  0.0  0.0      0     0 ?        S    Sep19   0:18 [kauditd]
root      1028  0.0  0.0   3572   748 ?        Ss   Sep19   0:00 /sbin/dhclient -1 -q -lf /var/lib/dhclient/dhclient-eth0.leases -pf /var/run/dhclient-eth0.pid eth0
root      1072  0.0  0.0  13972   828 ?        S<sl Sep19   2:13 auditd
root      1090  0.0  0.0   2052   512 ?        Ss   Sep19   0:00 /sbin/portreserve
root      1097  0.0  0.2  37568  3940 ?        Sl   Sep19   2:01 /sbin/rsyslogd -i /var/run/syslogd.pid -c 5
rpc       1120  0.0  0.0   2568   800 ?        Ss   Sep19   0:09 rpcbind
rpcuser   1138  0.0  0.0   2836  1224 ?        Ss   Sep19   0:00 rpc.statd
root      1161  0.0  0.0      0     0 ?        S    Sep19   0:00 [rpciod/0]
root      1165  0.0  0.0   2636   472 ?        Ss   Sep19   0:00 rpc.idmapd
root      1186  0.0  0.0   2940   756 ?        Ss   Sep19  13:27 lldpad -d
root      1195  0.0  0.0      0     0 ?        S    Sep19   0:00 [scsi_tgtd/0]
root      1196  0.0  0.0      0     0 ?        S    Sep19   0:00 [fc_exch_workque]
root      1197  0.0  0.0      0     0 ?        S    Sep19   0:00 [fc_rport_eq]
root      1199  0.0  0.0      0     0 ?        S    Sep19   0:00 [fcoe_work/0]
root      1200  0.0  0.0      0     0 ?        S<   Sep19   0:00 [fcoethread/0]
root      1201  0.0  0.0      0     0 ?        S    Sep19   0:00 [bnx2fc]
root      1202  0.0  0.0      0     0 ?        S<   Sep19   0:00 [bnx2fc_l2_threa]
root      1203  0.0  0.0      0     0 ?        S<   Sep19   0:00 [bnx2fc_thread/0]
root      1206  0.0  0.0   2184   564 ?        Ss   Sep19   1:08 /usr/sbin/fcoemon --syslog
root      1240  0.0  0.0   8556   976 ?        Ss   Sep19   1:22 /usr/sbin/sshd
root      1415  0.0  0.1  12376  2088 ?        Ss   Sep19   6:09 sendmail: accepting connections
smmsp     1424  0.0  0.0  12168  1680 ?        Ss   Sep19   0:02 sendmail: Queue runner@01:00:00 for /var/spool/clientmqueue
root      1441  0.0  0.0   5932  1260 ?        Ss   Sep19   0:56 crond
root      1456  0.0  0.0   2004   504 tty2     Ss+  Sep19   0:00 /sbin/mingetty /dev/tty2
root      1458  0.0  0.0   2004   504 tty3     Ss+  Sep19   0:00 /sbin/mingetty /dev/tty3
root      1460  0.0  0.0   2004   508 tty4     Ss+  Sep19   0:00 /sbin/mingetty /dev/tty4
root      1462  0.0  0.0   2004   504 tty5     Ss+  Sep19   0:00 /sbin/mingetty /dev/tty5
root      1464  0.0  0.0   2004   508 tty6     Ss+  Sep19   0:00 /sbin/mingetty /dev/tty6
root      1467  0.0  0.0   3316  1740 ?        S<   Sep19   0:00 /sbin/udevd -d
root      1468  0.0  0.0   3316  1740 ?        S<   Sep19   0:00 /sbin/udevd -d
apache    3796  0.0  0.4  32668  9452 ?        S    Dec16   0:08 /usr/sbin/httpd
apache    3800  0.0  0.4  32404  9444 ?        S    Dec16   0:08 /usr/sbin/httpd
apache    3801  0.0  0.4  33184  9556 ?        S    Dec16   0:07 /usr/sbin/httpd
apache    3821  0.0  0.4  32668  9612 ?        S    Dec16   0:08 /usr/sbin/httpd
apache    3840  0.0  0.4  32668  9612 ?        S    Dec16   0:07 /usr/sbin/httpd
apache    3841  0.0  0.4  32404  9464 ?        S    Dec16   0:07 /usr/sbin/httpd
apache    4032  0.0  0.4  32668  9632 ?        S    Dec16   0:07 /usr/sbin/httpd
apache    4348  0.0  0.4  32668  9460 ?        S    Dec16   0:07 /usr/sbin/httpd
apache    4355  0.0  0.4  32664  9464 ?        S    Dec16   0:07 /usr/sbin/httpd
apache    4356  0.0  0.5  32660  9728 ?        S    Dec16   0:07 /usr/sbin/httpd
apache    4422  0.0  0.4  32676  9460 ?        S    Dec16   0:06 /usr/sbin/httpd
root      5002  0.0  0.0   2004   504 tty1     Ss+  Nov21   0:00 /sbin/mingetty /dev/tty1
root      7540  0.0  0.0   5112  1380 ?        S    Dec17   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql     7642  0.1  1.0 136712 20140 ?        Sl   Dec17   2:35 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root      8001  0.0  0.4  31028  9600 ?        Ss   Dec13   0:18 /usr/sbin/httpd
root      8092  0.0  0.0      0     0 ?        S    13:47   0:00 [flush-253:2]
root      8511  0.0  0.0      0     0 ?        S    13:48   0:00 [flush-8:0]
root      8551 16.0  0.4  28612  8008 pts/0    S+   13:49   0:00 php test-mysql-connection.php exit
root      8552 44.0  0.1  11836  3252 ?        Ss   13:49   0:00 sshd: root@notty 
root      8560  0.0  0.0   4924  1032 ?        Rs   13:49   0:00 ps axu
root     12520  0.0  0.1  11500  3212 ?        Ss   09:05   0:00 sshd: jonwire [priv]
jonwire  12524  0.0  0.1  11832  1944 ?        S    09:05   0:05 sshd: jonwire@pts/0
jonwire  12525  0.0  0.0   5248  1736 pts/0    Ss   09:05   0:00 -bash
root     16309  0.0  0.0   5432  1436 pts/0    S    12:01   0:00 su -
root     16313  0.0  0.0   5244  1732 pts/0    S    12:01   0:00 -bash
apache   16361  0.0  0.5  32908  9836 ?        S    Dec15   0:08 /usr/sbin/httpd
apache   16363  0.0  0.5  32908  9784 ?        S    Dec15   0:08 /usr/sbin/httpd
apache   16364  0.0  0.4  32660  9612 ?        S    Dec15   0:08 /usr/sbin/httpd
apache   16365  0.0  0.4  32668  9608 ?        S    Dec15   0:08 /usr/sbin/httpd
apache   16366  0.0  0.7  35076 13948 ?        S    Dec15   0:08 /usr/sbin/httpd
apache   16367  0.0  0.4  32248  9264 ?        S    Dec15   0:08 /usr/sbin/httpd
apache   16859  0.0  0.5  32916  9844 ?        S    Dec15   0:08 /usr/sbin/httpd
apache   20379  0.0  0.4  32248  8904 ?        S    Dec15   0:08 /usr/sbin/httpd
root     28368  0.0  0.0      0     0 ?        S    Nov01   0:21 [flush-253:0]
apache   31973  0.0  0.4  31668  8608 ?        S    Dec16   0:08 /usr/sbin/httpd

)

ps axu这里的结果几乎没有用,因为我正在连接到本地主机。但是,从这些结果中我可以看到,数据库连接延迟偶尔会突然增加,“网络”延迟(某些TCP / IP缓冲区?)也会突然增加。

如果您是我,我会将测试周期数提高到5000或50000。

我只能猜测,但是由于您消除了服务器负载,并且我假设您检查了InnoDb-Stats中的红色标记(phpmyadmin在该标记上有很大的帮助,尽管有更专业的工具),剩下的仍然是不一致的用法。键。可能是您的查询略有不同,并且存在一个使用次优索引的星座吗?

请添加一个FORCE INDEX PRIMARY或类似的重复测试。

我发现在诊断MySQL问题方面非常有用的是mysqltuner这是一个PERL脚本,用于查看您的MySQL实例并提出各种调优改进。老实说,很难跟踪您可以进行的所有调优,并且此脚本非常有用,可以让您了解潜在的扼流点。

还有其他需要考虑的是Linux本身是如何工作的,这也可以解释为什么您随机地落后。当您top在Linux机器上装载(任何机器,无论装载如何)时,您会注意到您的内存几乎已全部用完(除非您刚刚重新启动)。这不是问题,也不是您的盒子超载。Linux尽可能多地将其加载到RAM中,以节省时间并将不常用的内容交换到交换文件中,就像所有现代操作系统(称为虚拟RAM)一样。通常没什么大不了,但您可能正在使用InnoDB作为表类型(当前的默认值),它将内容加载到RAM中以节省时间。可能发生的情况是您的查询已加载到RAM中(快速),但闲置时间足够长,可以换出到交换文件中(慢得多)。因此,当Linux将其移回RAM时,性能会受到较小的影响(交换文件比MySQL从磁盘移出文件更有效)。MySQL和它InnoDB都没有办法告诉我,因为就他们而言,它仍在RAM中。博客对此问题进行了详细描述,相关部分为

通常情况下,可以进行一点点交换使用(我们确实担心活动-交换进出),但是在许多情况下,“真实的”有用内存正在交换:主要是InnoDB缓冲池的一部分。当再次需要它时,会严重影响性能以将其交换回去,从而导致随机查询中的随机延迟。这可能会导致生产系统上总体上无法预测的性能,并且通常在交换开始后,系统可能会陷入致命的状态。

我们发现底层硬件问题导致了此问题。我们使用VMotion将服务器移至新硬件,问题不再存在。VMWare没有显示警报或硬件问题。尽管如此,离开该硬件仍可解决此问题。非常非常奇怪。

本文地址:http://php.askforanswer.com/mysqlchaxunsuijizhihou.html
文章标签: ,   ,  
版权声明:本文为原创文章,版权归 admin 所有,欢迎分享本文,转载请保留出处!

文件下载

老薛主机终身7折优惠码boke112

上一篇:
下一篇:

评论已关闭!