Make tuples_per_page pr. table configureable. - Mailing list pgsql-hackers
From | Jesper Krogh |
---|---|
Subject | Make tuples_per_page pr. table configureable. |
Date | |
Msg-id | 4C98FCF4.6090200@krogh.cc Whole thread Raw |
Responses |
Re: Make tuples_per_page pr. table configureable.
|
List | pgsql-hackers |
Hi. This is a follow up and updated patch on several old discussions: http://archives.postgresql.org/pgsql-hackers/2009-07/msg01065.php http://archives.postgresql.org/pgsql-admin/2010-04/msg00164.php http://archives.postgresql.org/pgsql-hackers/2009-06/msg00831.php First patch: http://archives.postgresql.org/pgsql-hackers/2010-02/msg00096.php Currently the aim for the amount of tuples per page is 4 resulting in a target size of the individual tuple to be more than 2KB before the tupletoaster kicks in. This patch makes it tuneable on a per table basis. The main reasoning is that if people have knowledge about the usage pattern of their database, they can have huge benefit in tuning TOAST to be more or less aggressive. This is obviously true if: * The dataset isn't entirely memory cached and * columns stored in main (and just visibility checking) is more frequently done than accessing data columns stored in TOAST. But even in the case where the dataset is entirely memory cached this tuneable can transform the database to a widely different performance numbers than currently. This typically happens in cases where only visibillity checks are done (select count()) and when aggregates on stuff stored in main is used. I must admit that I have chosen a "poor" test data set, since based on the average length of the tuple the "sweet point" is just around the current default, but constructing a dataset with an average < 2.5KB tuple size would absolutely benefit. But I hope that people can see the benefit anyway. The dataset is 500.000 records in a table with: id serial, code text, (small text block) entry text (larger text block) where code is length(code) < 10 and entry: avg | max | min -----------------------+------+------ 3640.2042755914488171 | 8708 | 1468 The queries are run multiple time and numbers are based on runs where iowait was 0 while the query executed. So entirely memory and cpu-bound numbers: testdb=# select * from data order by tuples_per_page; time_sum_length | time_count | tuples_per_page | main_size | toast_size -----------------+------------+-----------------+-----------+------------ 5190.258 | 689.34 | 1 | 1981MB | 0MB 5478.519 | 660.841 | 2 | 1894MB | 0MB 9740.768 | 481.822 | 3 | 1287MB | 4MB 12875.479 | 73.895 |(default) 4 | 79MB | 1226MB 13082.768 | 58.023 | 8 | 29MB | 1276MB (5 rows) time_sum_length => select sum(length(entry)) from data; time_count => select count(*) from data; All timings are in ms. With this data Command to set "tuples_per_page" is: ALTER TABLE <tablename> set (tuples_per_page = X) where 1 <= X <= 32. The patch really need some feedback, I've tried to adress Tom Lane's earlier comment about fixing the place where it figure out wether it needs a toast table (and actually tested that it works). While there surely are more that can be done in order to improve the flexibillity in this area I do think that there is sufficient benefit. This is my second shot at coding C, so please let me know if I have been doing anything wrong. Comments are all welcome. Thanks. -- Jesper
Attachment
pgsql-hackers by date: