Thread: Fwd: Relatively high planner overhead on partitions?

Fwd: Relatively high planner overhead on partitions?

From
Skarsol
Date:
I tried sending this a couple days ago but I wasn't a member of the group so I think it's in limbo. Apologies if a 2nd copy shows up at some point.

We recently migrated a 1.3TB database from 8.4 to 9.2.2 on a new server. As part of this migration we added partitions to the largest tables so we could start removing old data to an archive database. Large queries perform much better due to not hitting the older data as expected. Small queries served from records in memory are suffering a much bigger performance hit than anticipated due to the partitioning.

I'm able to duplicate this issue on our server trivially with these commands: http://pgsql.privatepaste.com/7223545173

Running the queries from the command line 10k times (time psql testdb < test1.sql >/dev/null) results in a 2x slowdown for the queries not using testtable_90 directly. (~4s vs ~2s).

Running a similar single record select on a non-partitioned table averages 10k in 2s.

Running "select 1;" 10k times in the same method averages 1.8 seconds.

This matches exactly what I'm seeing in our production database. The numbers are different, but the 2x slowdown persists. Doing a similar test on another table on production with 7 children and 3 check constraints per child results in a 3x slowdown.

I'm aware that partitioning has an impact on the planner, but doubling the time of in memory queries with only 5 partitions with 1 check each is much greater than anticipated. Are my expectations off and this is normal behavior or is there something I can do to try and speed these in memory queries up? I was unable to find any information online as to the expected planner impact of X # of partitions.

Database information follows:

Red Hat Enterprise Linux Server release 6.4 (Santiago)
Linux hostname.domainname 2.6.32-358.6.1.el6.x86_64 #1 SMP Fri Mar 29 16:51:51 EDT 2013 x86_64 x86_64 x86_64 GNU/Linux
PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit

Server info:
4x Intel(R) Xeon(R) CPU E5-4620 0 @ 2.20GHz
128gb RAM

 DateStyle                       | ISO, MDY                                                                                 | configuration file
 default_statistics_target       | 5000                                                                                     | configuration file
 default_text_search_config      | pg_catalog.english                                                                       | configuration file
 effective_cache_size            | 64000MB                                                                                  | configuration file
 effective_io_concurrency        | 2                                                                                        | configuration file
 fsync                           | on                                                                                       | configuration file
 lc_messages                     | C                                                                                        | configuration file
 lc_monetary                     | C                                                                                        | configuration file
 lc_numeric                      | C                                                                                        | configuration file
 lc_time                         | C                                                                                        | configuration file
 max_connections                 | 500                                                                                      | configuration file
 max_stack_depth                 | 2MB                                                                                      | environment
 shared_buffers                  | 32000MB                                                                                  | configuration file
 synchronous_commit              | on                                                                                       | configuration file
 TimeZone                        | CST6CDT                                                                                  | configuration file
 wal_buffers                     | 16MB                                                                                     | configuration file
 wal_level                       | archive                                                                                  | configuration file
 wal_sync_method                 | fdatasync                                                                                | configuration file



Re: Fwd: Relatively high planner overhead on partitions?

From
Pavel Stehule
Date:
Hello



2013/7/19 Skarsol <skarsol@gmail.com>:
> I tried sending this a couple days ago but I wasn't a member of the group so
> I think it's in limbo. Apologies if a 2nd copy shows up at some point.
>
> We recently migrated a 1.3TB database from 8.4 to 9.2.2 on a new server. As
> part of this migration we added partitions to the largest tables so we could
> start removing old data to an archive database. Large queries perform much
> better due to not hitting the older data as expected. Small queries served
> from records in memory are suffering a much bigger performance hit than
> anticipated due to the partitioning.
>
> I'm able to duplicate this issue on our server trivially with these
> commands: http://pgsql.privatepaste.com/7223545173
>
> Running the queries from the command line 10k times (time psql testdb <
> test1.sql >/dev/null) results in a 2x slowdown for the queries not using
> testtable_90 directly. (~4s vs ~2s).

if all data in your test living in memory - then bottleneck is in CPU
- and any other node in execution plan is significant.

It is not surprise, because OLTP relation databases are not well
optimized for this use case. A designers expected much more
significant impact of IO operations, and these databases are designed
to minimize bottleneck in IO - with relative low memory using. This
use case is better solved in OLAP databases (read optimized databases)
designed after 2000 year - like monetdb, verticadb, or last year cool
db HANA.

Regards

Pavel


>
> Running a similar single record select on a non-partitioned table averages
> 10k in 2s.
>
> Running "select 1;" 10k times in the same method averages 1.8 seconds.
>
> This matches exactly what I'm seeing in our production database. The numbers
> are different, but the 2x slowdown persists. Doing a similar test on another
> table on production with 7 children and 3 check constraints per child
> results in a 3x slowdown.
>
> I'm aware that partitioning has an impact on the planner, but doubling the
> time of in memory queries with only 5 partitions with 1 check each is much
> greater than anticipated. Are my expectations off and this is normal
> behavior or is there something I can do to try and speed these in memory
> queries up? I was unable to find any information online as to the expected
> planner impact of X # of partitions.
>
> Database information follows:
>
> Red Hat Enterprise Linux Server release 6.4 (Santiago)
> Linux hostname.domainname 2.6.32-358.6.1.el6.x86_64 #1 SMP Fri Mar 29
> 16:51:51 EDT 2013 x86_64 x86_64 x86_64 GNU/Linux
> PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6
> 20120305 (Red Hat 4.4.6-4), 64-bit
>
> Server info:
> 4x Intel(R) Xeon(R) CPU E5-4620 0 @ 2.20GHz
> 128gb RAM
>
>  DateStyle                       | ISO, MDY
> | configuration file
>  default_statistics_target       | 5000
> | configuration file
>  default_text_search_config      | pg_catalog.english
> | configuration file
>  effective_cache_size            | 64000MB
> | configuration file
>  effective_io_concurrency        | 2
> | configuration file
>  fsync                           | on
> | configuration file
>  lc_messages                     | C
> | configuration file
>  lc_monetary                     | C
> | configuration file
>  lc_numeric                      | C
> | configuration file
>  lc_time                         | C
> | configuration file
>  max_connections                 | 500
> | configuration file
>  max_stack_depth                 | 2MB
> | environment
>  shared_buffers                  | 32000MB
> | configuration file
>  synchronous_commit              | on
> | configuration file
>  TimeZone                        | CST6CDT
> | configuration file
>  wal_buffers                     | 16MB
> | configuration file
>  wal_level                       | archive
> | configuration file
>  wal_sync_method                 | fdatasync
> | configuration file
>
>
>