我看到的奇怪且极其缓慢的 IO 模式是这样的(输出iostat -dxk 1 /dev/xvdb1
):
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
xvdb1 0.00 0.00 0.99 0.99 7.92 3.96 12.00 1.96 2206.00 502.00 99.41
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
xvdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.00 0.00 0.00 100.40
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
xvdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.00 0.00 0.00 100.40
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
xvdb1 0.00 0.00 0.99 0.00 3.96 0.00 8.00 0.99 2220.00 1004.00 99.41
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
xvdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.00 0.00 0.00 100.40
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
xvdb1 0.00 0.99 0.99 0.00 7.92 0.00 16.00 1.14 2148.00 1004.00 99.41
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
xvdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 2.01 0.00 0.00 100.40
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
xvdb1 0.00 0.00 1.00 1.00 4.00 8.00 12.00 2.01 1874.00 502.00 100.40
我不知道为什么磁盘利用率和等待时间如此之高,而读写率却如此之低。这可能是什么原因造成的?
被查询的表只有几个 varchar 列,其中一个是 last_name,它被索引了(实际上lower(last_name)
是被索引了)。查询本身很简单:
SELECT * FROM consumer_m WHERE lower(last_name) = 'hoque';
以下是解释输出:
QUERY PLAN
-------------------------------------------------------------------------------------------------
Bitmap Heap Scan on consumer_m (cost=2243.90..274163.41 rows=113152 width=164)
Recheck Cond: (lower((last_name)::text) = 'hoque'::text)
-> Bitmap Index Scan on consumer_m_last_name_index (cost=0.00..2215.61 rows=113152 width=0)
Index Cond: (lower((last_name)::text) = 'hoque'::text)
还要注意,数据库处于 auto_vacuum 状态,因此没有执行明确的真空/分析。
答案1
您的设备是,这意味着您正在 Xen 下运行。您的存储是如何配置的/dev/xvdb1
?底层设备是否存在争用,以及iostat
那?
除非你能消除这种可能性,否则我就会把责任推到表现不佳上。
基本上,解决此类性能问题的总体方法是考虑可能出现瓶颈的所有层,然后设计测试来消除每一个层,直到隔离问题为止。
答案2
以下是一些建议,顺序大致随机:
CentOS 默认不启用 Autovacum。您必须设置多个设置才能启用它。仔细检查以确保 vacum 进程确实运行。很容易错过所需的某个设置。
请注意,您必须对该查询执行第二个过滤步骤,这可能会很昂贵,具体取决于您得到的结果。我会考虑使用如下索引:
在 consumer_m (lower(last_name)) 上创建索引 consumer_m_lower_last;
这将与您的查询匹配并删除重新检查。
此外,正如 mattdm 指出的那样,您不能在虚拟化环境中信任 iostat。
你应该检查一下http://lonesysadmin.net/2008/02/21/elevatornoop/如果您在 XEN 环境中遇到 IO 问题。电梯设置可能会产生影响,但影响不会这么大。
底层磁盘是否使用 LVM 快照?虽然从管理角度来看这非常有用,但它可能会损害 IO 性能。如果您使用的块设备是快照,并且已对块设备进行快照,则情况也是如此。
答案3
我怀疑这不是 PostgreSQL 的问题,更可能是磁盘 IO 的问题。正如另一个答案的评论所提到的,如果是磁盘 IO 问题,您确实应该从 Dom0 进行测量,这样您就可以了解正在发生的一切。
不久前我遇到过类似的问题,后来发现是磁盘控制器的问题。磁盘访问速度非常慢,导致系统在等待磁盘 IO 时出现瓶颈(表现为非常高的平均负载和等待时间,但也导致等待磁盘的进程消耗比平时更多的 CPU)。原来是内核无法正确识别控制器,因此只能使用老式的 IDE 控制器,而不是快速的 SATA 控制器。
修复方法是启动
hda=noprobe hda=none
在 /etc/grub.conf 中的内核字符串末尾。(当然,添加您拥有的所有磁盘,ala:hdc=noprobe, hdc=none, hdd=
...)