Обсуждение: Regarding postgreSQL performance on DRAM

Поиск
Список
Период
Сортировка

Regarding postgreSQL performance on DRAM

От
Rohan Kadekodi
Дата:
Hello,

I am a researcher in the Storage and Systems Lab at UT Austin. I am working on analyzing the performance of PostgreSQL on Persistent Memory. To be more specific, I am trying to analyze the performance of PostgreSQL-10.5 on a ramdisk device mounted with the ext4 file system.

My workload is simple. I  insert 1 million rows into a table with 100 columns, where each column is 256 bytes in length, and every 10 inserts are batched into a transaction. 

On running strace on all the PostgreSQL processes spawned by the server, I am observing that the total time taken in system calls is less than 5% of the total time it takes to do the 1 million inserts. I changed the configuration file to make PostgreSQL as synchronous as possible, and even then the time spent in system calls is very low. 

When I run the perf tool to check where the majority time is being spent, I see that there is a function called pglz_compress() where a lot of time is getting spent and also there is a function called heap_compute_data_size() where there is significant time spent. 

Could I know why so much time is being spent in user space, and how I can make PostgreSQL more I/O bound than it is now?

Thanks!
- Rohan

Re: Regarding postgreSQL performance on DRAM

От
Jerry Sievers
Дата:
Rohan Kadekodi <kadekodirohan@gmail.com> writes:

> Hello,
>
> I am a researcher in the Storage and Systems Lab at UT Austin. I am
> working on analyzing the performance of PostgreSQL on Persistent
> Memory. To be more specific, I am trying to analyze the performance
> of PostgreSQL-10.5 on a ramdisk device mounted with the ext4 file
> system.
>
> My workload is simple. I  insert 1 million rows into a table with 100
> columns, where each column is 256 bytes in length, and every 10
> inserts are batched into a transaction. 

Your test workload qualifies for TOASTing due to the $unrealistically
long physical tuple size.

Thusly, by default compression is also in-scope and the heavy CPU impact
showing up in your performance metrics.

Revising the test to substantially reduce or eliminate the burden of
toasting and especially compression should help in the I/O measurements.

HTH

>
> On running strace on all the PostgreSQL processes spawned by the
> server, I am observing that the total time taken in system calls is
> less than 5% of the total time it takes to do the 1 million inserts.
> I changed the configuration file to make PostgreSQL as synchronous as
> possible, and even then the time spent in system calls is very low. 
>
> When I run the perf tool to check where the majority time is being
> spent, I see that there is a function called pglz_compress() where a
> lot of time is getting spent and also there is a function called
> heap_compute_data_size() where there is significant time spent. 
>
> Could I know why so much time is being spent in user space, and how I
> can make PostgreSQL more I/O bound than it is now?
>
> Thanks!
> - Rohan
>
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net


Re: Regarding postgreSQL performance on DRAM

От
Thomas Kellerer
Дата:
Jerry Sievers schrieb am 20.02.2019 um 21:19:
>> My workload is simple. I  insert 1 million rows into a table with 100
>> columns, where each column is 256 bytes in length, and every 10
>> inserts are batched into a transaction.
> 
> Your test workload qualifies for TOASTing due to the $unrealistically
> long physical tuple size.

Hmm. I though TOAST is only applied to single values, not the entire tuple (row)?

As each column is substantially shorter than the TOAST threshold, I would not expect toasting to kick in here.

Or am I missing something?




Re: Regarding postgreSQL performance on DRAM

От
Tom Lane
Дата:
Thomas Kellerer <spam_eater@gmx.net> writes:
> Jerry Sievers schrieb am 20.02.2019 um 21:19:
>>> My workload is simple. I insert 1 million rows into a table with 100
>>> columns, where each column is 256 bytes in length, and every 10
>>> inserts are batched into a transaction.

>> Your test workload qualifies for TOASTing due to the $unrealistically
>> long physical tuple size.

> Hmm. I though TOAST is only applied to single values, not the entire tuple (row)?
> As each column is substantially shorter than the TOAST threshold, I would not expect toasting to kick in here.

Well, the entire tuple would be 25600 bytes plus some overhead, which
cannot fit on a Postgres page (8K, unless the OP changed compile options
without mentioning it).  So something has to be done to make it fit, and
that something is going to be toasting any fields that can be toasted.

From memory, our threshold for trying to make tuples narrower is only
a quarter-page anyway, so that the toaster will be trying to get this
down to 2K if it can.  That's certainly going to involve compressing
every field, and I wouldn't be surprised if a lot of them get shoved
out-of-line too.

The OP might care to read

https://www.postgresql.org/docs/current/storage-toast.html

            regards, tom lane


Re: Regarding postgreSQL performance on DRAM

От
Thomas Kellerer
Дата:
Tom Lane schrieb am 20.02.2019 um 22:04:
>> Hmm. I though TOAST is only applied to single values, not the entire tuple (row)?
>> As each column is substantially shorter than the TOAST threshold, I would not expect toasting to kick in here.
> 
> Well, the entire tuple would be 25600 bytes plus some overhead, which
> cannot fit on a Postgres page (8K, unless the OP changed compile options
> without mentioning it).  So something has to be done to make it fit, and
> that something is going to be toasting any fields that can be toasted.

Ah, obviously.
Thanks



Re: Regarding postgreSQL performance on DRAM

От
Rohan Kadekodi
Дата:
Thank you for the insights regarding TOASTing!

I reduced the number of columns to 4, which makes my tuple size ~1KB. 

With this, the compression overhead went away, but even now <25% of the time is being spent in system calls when I analyze strace. 

Perf tool shows that there is a non-trivial amount of time spent in DropCachedPlan and AllocSetAlloc. Does this hint to some sort of caching being done by PostgreSQL, and is there a way to disable this caching?

Any other insights would be really helpful!

Thanks in advance,
Rohan

On Wed, 20 Feb 2019 at 15:04, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Thomas Kellerer <spam_eater@gmx.net> writes:
> Jerry Sievers schrieb am 20.02.2019 um 21:19:
>>> My workload is simple. I insert 1 million rows into a table with 100
>>> columns, where each column is 256 bytes in length, and every 10
>>> inserts are batched into a transaction.

>> Your test workload qualifies for TOASTing due to the $unrealistically
>> long physical tuple size.

> Hmm. I though TOAST is only applied to single values, not the entire tuple (row)?
> As each column is substantially shorter than the TOAST threshold, I would not expect toasting to kick in here.

Well, the entire tuple would be 25600 bytes plus some overhead, which
cannot fit on a Postgres page (8K, unless the OP changed compile options
without mentioning it).  So something has to be done to make it fit, and
that something is going to be toasting any fields that can be toasted.

From memory, our threshold for trying to make tuples narrower is only
a quarter-page anyway, so that the toaster will be trying to get this
down to 2K if it can.  That's certainly going to involve compressing
every field, and I wouldn't be surprised if a lot of them get shoved
out-of-line too.

The OP might care to read

https://www.postgresql.org/docs/current/storage-toast.html

                        regards, tom lane

Precompressed data in TOASTed columns (was Re: Regarding postgreSQLperformance on DRAM)

От
Ron
Дата:
On 2/20/19 3:04 PM, Tom Lane wrote:
[snip]
> Well, the entire tuple would be 25600 bytes plus some overhead, which
> cannot fit on a Postgres page (8K, unless the OP changed compile options
> without mentioning it).  So something has to be done to make it fit, and
> that something is going to be toasting any fields that can be toasted.
>
>  From memory, our threshold for trying to make tuples narrower is only
> a quarter-page anyway, so that the toaster will be trying to get this
> down to 2K if it can.  That's certainly going to involve compressing
> every field, and I wouldn't be surprised if a lot of them get shoved
> out-of-line too.
>
> The OP might care to read
>
> https://www.postgresql.org/docs/current/storage-toast.html

Based on that and https://www.postgresql.org/docs/9.6/sql-altertable.html 
should I think about doing a SET STORAGE=EXTERNAL on columns with PDF and 
compressed TIFF data?

Thanks

-- 
Angular momentum makes the world go 'round.


Re: Regarding postgreSQL performance on DRAM

От
Peter Eisentraut
Дата:
On 2019-02-20 22:50, Rohan Kadekodi wrote:
> Perf tool shows that there is a non-trivial amount of time spent in
> DropCachedPlan and AllocSetAlloc. Does this hint to some sort of caching
> being done by PostgreSQL, and is there a way to disable this caching?

AllocSetAlloc() is memory allocation, which, well, you need some of. ;-)
 DropCachedPlan() has to do with how statements are prepared and
released.  You can probably reduce some of that overhead by using
prepared statements consistently.  Hard to tell without more details
about your test script.  Have you looked at pgbench?

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services