Hello 2013/7/19 Skarsol <skarsol@xxxxxxxxx>: > 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 > > > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
