From: Matthew Hall <mhall@mhcomputing.net>
To: Sergei Kornilov <sk@zsrv.org>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: surrano@gmail.com <surrano@gmail.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: insert and query performance on big string table with pg_trgm
Date: Wed, 6 Dec 2017 18:04:54 -0800
Message-ID: <8C8D9D8C-F0C6-48D3-AE26-B733EFAED4DB@mhcomputing.net> (raw)
In-Reply-To: <313361512544990@web43j.yandex.ru>
References: <F5E9C927-1F84-488B-BD02-01DAFDD369D9@mhcomputing.net>
<CAMkU=1yPVyiyyXOxDyDCA18zqaorkoZrfns-2CZXvGi5uZw8xQ@mail.gmail.com>
<56B84857-06EA-4534-A13F-F7AA3B924446@mhcomputing.net>
<351E3FBB-7DFC-4BB0-841E-E7AB16DD57A3@mhcomputing.net>
<313361512544990@web43j.yandex.ru>
> On Dec 5, 2017, at 11:23 PM, Sergei Kornilov <sk@zsrv.org> wrote:
> You has very slow (or busy) disks, not postgresql issue. Reading 6760 * 8KB in 70 seconds is very bad result.
>
> For better performance you need better disks, at least raid10 (not raid5). Much more memory in shared_buffers can help with read performance and so reduce disk utilization, but write operations still will be slow.
>
> Sergei
Sergei,
Thanks so much for confirming, this really helps a lot to know what to do. I thought the disk could be some of my issue, but I wanted to make sure I did all of the obvious tuning first. I have learned some very valuable things which I'll be able to use on future challenges like this which I didn't learn previously.
Based on this advice from everyone, I'm setting up a box with more RAM, lots of SSDs, and RAID 10. I'll write back in a few more days after I've completed it.
I can also confirm that the previous advice about using a hash / digest based unique index seemed to make the loading process slower for me, not faster, which is an interesting result to consider for future users following this thread (if any). I don't yet have specific data how much slower, because it's actually still going!
Sincerely,
Matthew.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mhall@mhcomputing.net, sk@zsrv.org, jeff.janes@gmail.com, surrano@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: insert and query performance on big string table with pg_trgm
In-Reply-To: <8C8D9D8C-F0C6-48D3-AE26-B733EFAED4DB@mhcomputing.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox