Re: [PERFORM] Very poor read performance, query independent

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: [PERFORM] Very poor read performance, query independent
Дата
Msg-id CAMkU=1zD=SNFF=U-HtRHcu2dAdqyorD6o_UCQoYHuoh-gcs_uA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [PERFORM] Very poor read performance, query independent  (Charles Nadeau <charles.nadeau@gmail.com>)
Ответы Re: [PERFORM] Very poor read performance, query independent
Список pgsql-performance
Дерево обсуждения
On Tue, Jul 11, 2017 at 4:02 AM, Charles Nadeau <charles.nadeau@gmail.com> wrote:
Jeff,

I used fio in a quick benchmarking script inspired by https://smcleod.net/benchmarking-io/:
#!/bin/bash
#Random throughput
echo "Random throughput"
sync
fio --randrepeat=1 --ioengine=libaio --direct=1 --gtod_reduce=1 --name=test --filename=test --bs=4M --iodepth=256 --size=10G --readwrite=randread --ramp_time=4
#Random IOPS
echo "Random IOPS"
sync
fio --randrepeat=1 --ioengine=libaio --direct=1 --gtod_reduce=1 --name=test --filename=test --bs=4k --iodepth=256 --size=4G --readwrite=randread --ramp_time=4
#Sequential throughput
echo "Sequential throughput"
sync
fio --randrepeat=1 --ioengine=libaio --direct=1 --gtod_reduce=1 --name=test --filename=test --bs=4M --iodepth=256 --size=10G --readwrite=read --ramp_time=4
#Sequential IOPS
echo "Sequential IOPS"
sync
fio --randrepeat=1 --ioengine=libaio --direct=1 --gtod_reduce=1 --name=test --filename=test --bs=4k --iodepth=256 --size=4G --readwrite=read --ramp_time=4

I don't think any of those are directly relevant to PostgreSQL, as it doesn't use direct IO, doesn't use libaio, and is rarely going to get anywhere near 256 iodepth.  So the best they can do is put a theoretical ceiling on the performance.  Also, random IO with a 4MB stride doesn't make any sense from a PostgreSQL perspective.

 

Performing the test you suggested, I get 128.5MB/s. Monitoring the test, I find that the throughput is constant from start to finish and that the iowait is also constant at 5%:

I would have expected it to do better than that.  Maybe you increase the kernel readahead setting.  I've found the default to be much too small.  But it doesn't make much difference to you, as you appear to be doing random IO in your queries, not sequential.


Could you suggest another way to benchmark random reads?

Your 1100 IOPS times 8kb block size gives about 8MB/s of throughput, which is close to what you report.  So I think I'd would instead focus on tuning your actual queries.  You say the problem is not query-dependent, but I think that that just means all the queries you looked at are similar.  If you looked at a query that can't use indexes, like count(unindexed_column) from biggest_table; you would find it doing much more IO than 4MB/s.

Can you pick the simplest query you actually care about, and post both an "explain (analyze, timing off)" and an "explain (analyze, buffers)" for it?  (Preferably turning "track_io_timing" on first).

One other question I had, you said you had "2x Intel Xeon E5550", which should be 8 CPU (or 16, if the hyperthreads 
are reported as separate CPUs).  But you also said: "Also using dstat I can see that iowait time is at about 25%".  Usually if there is only one thing going on on the server, then IOWAIT won't be more than reciprocal of #CPU.  Is the server busy doing other stuff at the same time you are benchmarking it?

Cheers,

Jeff

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Joshua D. Drake"
Дата:
Сообщение: Re: [PERFORM] Very poor read performance, query independent
Следующее
От: Jeff Janes
Дата:
Сообщение: Re: [PERFORM] Very poor read performance, query independent