pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Partition table in 9.0.x? 9+ messages / 3 participants [nested] [flat]
* Partition table in 9.0.x? @ 2013-01-04 21:31 AJ Weber <aweber@comcast.net> 2013-01-05 04:03 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> 0 siblings, 1 reply; 9+ messages in thread From: AJ Weber @ 2013-01-04 21:31 UTC (permalink / raw) To: pgsql-performance Hi all, I have a table that has about 73mm rows in it and growing. Running 9.0.x on a server that unfortunately is a little I/O constrained. Some (maybe) pertinent settings: default_statistics_target = 50 maintenance_work_mem = 512MB constraint_exclusion = on effective_cache_size = 5GB work_mem = 18MB wal_buffers = 8MB checkpoint_segments = 32 shared_buffers = 2GB The server has 12GB RAM, 4 cores, but is shared with a big webapp running in Tomcat -- and I only have a RAID1 disk to work on. Woes me... Anyway, this table is going to continue to grow, and it's used frequently (Read and Write). From what I read, this table is a candidate to be partitioned for performance and scalability. I have tested some scripts to build the "inherits" tables with their constraints and the trigger/function to perform the work. Am I doing the right thing by partitioning this? If so, and I can afford some downtime, is dumping the table via pg_dump and then loading it back in the best way to do this? Should I run a cluster or vacuum full after all is done? Is there a major benefit if I can upgrade to 9.2.x in some way that I haven't realized? Finally, if anyone has any comments about my settings listed above that might help improve performance, I thank you in advance. -AJ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partition table in 9.0.x? 2013-01-04 21:31 Partition table in 9.0.x? AJ Weber <aweber@comcast.net> @ 2013-01-05 04:03 ` Jeff Janes <jeff.janes@gmail.com> 2013-01-06 15:27 ` Re: Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 0 siblings, 1 reply; 9+ messages in thread From: Jeff Janes @ 2013-01-05 04:03 UTC (permalink / raw) To: AJ Weber <aweber@comcast.net>; +Cc: pgsql-performance On Friday, January 4, 2013, AJ Weber wrote: > Hi all, > > I have a table that has about 73mm rows in it and growing. How big is the table in MB? Its indexes? ... > > The server has 12GB RAM, 4 cores, but is shared with a big webapp running > in Tomcat -- and I only have a RAID1 disk to work on. Woes me... > > By a RAID1 disk, do you mean two disks in a RAID1 configuration, or a single RAID1 composed of an unspecified number of disks? Often spending many thousands of dollars in DBA time can save you from having to buy many hundreds of dollars in hard drives. :) On the other hand, often you end up having to buy the extra disks anyway afterall. > Anyway, this table is going to continue to grow, and it's used frequently > (Read and Write). Are all rows in the table read and written with equal vigor, or are there hot rows and cold rows that can be recognized based on the row's values? > From what I read, this table is a candidate to be partitioned for > performance and scalability. I have tested some scripts to build the > "inherits" tables with their constraints and the trigger/function to > perform the work. > > Am I doing the right thing by partitioning this? Probably not. Or at least, you haven't given us the information to know. Very broadly speaking, well-implemented partitioning makes bulk loading and removal operations take less IO, but makes normal operations take more IO, or if lucky leaves it unchanged. There are exceptions, but unless you can identify a very specific reason to think you might have one of those exceptions, then you probably don't. Do you have a natural partitioning key? That is, is there a column (or expression) which occurs as a selective component in the where clause of almost all of your most io consuming SQL and DML? If so, you might benefit from partitioning on it. (But in that case, you might be able to get most of the benefits of partitioning, without the headaches of it, just by revamping your indexes to include that column/expression as their leading field). If you don't have a good candidate partitioning key, then partitioning will almost surely make things worse. If so, and I can afford some downtime, is dumping the table via pg_dump > and then loading it back in the best way to do this? > To do efficient bulk loading into a partitioned table, you need to specifically target each partition, rather than targeting with a trigger. That pretty much rules out pg_dump, AFAIK, unless you are going to parse the dump file(s) and rewrite them. > Should I run a cluster or vacuum full after all is done? > Probably not. If a cluster after the partitioning would be beneficial, there would be a pretty good chance you could do a cluster *instead* of the partitioning and get the same benefit. If you do some massive deletes from the parent table as part of populating the children, then a vacuum full of the parent could be useful. But if you dump the parent table, truncate it, and reload it as partitioned tables, then vacuum full would probably not be useful. Really, you need to identify your most resource-intensive queries before you can make any reasonable decisions. > > Is there a major benefit if I can upgrade to 9.2.x in some way that I > haven't realized? > If you have specific queries that are misoptimized and so are generating more IO than they need to, then upgrading could help. On the other hand, it could also make things worse, if a currently well optimized query becomes worse. But, instrumentation has improved in 9.2 from 9.0, so upgrading would make it easier to figure out just which queries are really bad and have the most opportunity for improvement. A little well informed optimization might obviate the need for either partitioning or more hard drives. > Finally, if anyone has any comments about my settings listed above that > might help improve performance, I thank you in advance. > Your default statistics target seemed low. Without knowing the nature of your most resource intensive queries or how much memory tomcat is using, it is hard to say more. Cheers, Jeff ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partition table in 9.0.x? 2013-01-04 21:31 Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 2013-01-05 04:03 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> @ 2013-01-06 15:27 ` AJ Weber <aweber@comcast.net> 2013-01-08 15:26 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> 0 siblings, 1 reply; 9+ messages in thread From: AJ Weber @ 2013-01-06 15:27 UTC (permalink / raw) To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance All fair questions... Thank you for your detailed response! On 1/4/2013 11:03 PM, Jeff Janes wrote: > On Friday, January 4, 2013, AJ Weber wrote: > > Hi all, > > I have a table that has about 73mm rows in it and growing. > > > How big is the table in MB? Its indexes? Not sure on this. Will see if pgAdmin tells me. > > ... > > The server has 12GB RAM, 4 cores, but is shared with a big webapp > running in Tomcat -- and I only have a RAID1 disk to work on. > Woes me... > > > By a RAID1 disk, do you mean two disks in a RAID1 configuration, or a > single RAID1 composed of an unspecified number of disks? > > Often spending many thousands of dollars in DBA time can save you from > having to buy many hundreds of dollars in hard drives. :) On the > other hand, often you end up having to buy the extra disks anyway > afterall. > I mean I have two disks in a RAID1 configuration. The server is currently in a whitebox datacenter and I have zero control over the hardware, so adding disks is unfortunately out of the question. I completely understand the comment, and would love to have a larger SAN available to me that I could configure...I just don't and have no way of getting one anytime soon. > > Anyway, this table is going to continue to grow, and it's used > frequently (Read and Write). > > > Are all rows in the table read and written with equal vigor, or are > there hot rows and cold rows that can be recognized based on the row's > values? No, I could probably figure out a way to setup an "archive" or "older" section of the data that is updated much less frequently. Deletes are rare. Inserts/Updates "yes". Select on existing rows -- very frequent. > From what I read, this table is a candidate to be partitioned for > performance and scalability. I have tested some scripts to build > the "inherits" tables with their constraints and the > trigger/function to perform the work. > > Am I doing the right thing by partitioning this? > > > Probably not. Or at least, you haven't given us the information to > know. Very broadly speaking, well-implemented partitioning makes bulk > loading and removal operations take less IO, but makes normal > operations take more IO, or if lucky leaves it unchanged. There are > exceptions, but unless you can identify a very specific reason to > think you might have one of those exceptions, then you probably don't. I know you can't believe everything you read, but I thought I saw some metrics about when a table's size exceeds some fraction of available RAM, or when it approaches 100mm rows, it's a big candidate for partitioning. > > Do you have a natural partitioning key? That is, is there a column > (or expression) which occurs as a selective component in the where > clause of almost all of your most io consuming SQL and DML? If so, > you might benefit from partitioning on it. (But in that case, you > might be able to get most of the benefits of partitioning, without the > headaches of it, just by revamping your indexes to include that > column/expression as their leading field). > > If you don't have a good candidate partitioning key, then partitioning > will almost surely make things worse. > The table is a "detail table" to its master records. That is, it's like an order-details table where it will have a 1-n rows joined to the master ("order") table on the order-id. So I can partition it based on the order number pretty easily (which is a bigint, btw). > If so, and I can afford some downtime, is dumping the table via > pg_dump and then loading it back in the best way to do this? > > > To do efficient bulk loading into a partitioned table, you need to > specifically target each partition, rather than targeting with a > trigger. That pretty much rules out pg_dump, AFAIK, unless you are > going to parse the dump file(s) and rewrite them. > > > Should I run a cluster or vacuum full after all is done? > > > Probably not. If a cluster after the partitioning would be > beneficial, there would be a pretty good chance you could do a cluster > *instead* of the partitioning and get the same benefit. > I did try clustering the table on the PK (which is actually 4 columns), and it appeared to help a bit. I was hoping partitioning was going to help me even more. > If you do some massive deletes from the parent table as part of > populating the children, then a vacuum full of the parent could be > useful. But if you dump the parent table, truncate it, and reload it > as partitioned tables, then vacuum full would probably not be useful. > > Really, you need to identify your most resource-intensive queries > before you can make any reasonable decisions. > > > Is there a major benefit if I can upgrade to 9.2.x in some way > that I haven't realized? > > > If you have specific queries that are misoptimized and so are > generating more IO than they need to, then upgrading could help. On > the other hand, it could also make things worse, if a currently well > optimized query becomes worse. > Is there some new feature or optimization you're thinking about with this comment? If so, could you please just send me a link and/or feature name and I'll google it myself? > But, instrumentation has improved in 9.2 from 9.0, so upgrading would > make it easier to figure out just which queries are really bad and > have the most opportunity for improvement. A little well informed > optimization might obviate the need for either partitioning or more > hard drives. > This is interesting too. I obviously would like the best available options to tune the database and the application. Is this detailed in the release notes somewhere, and what tools could I use to take advantage of this? (Are there new/improved details included in the EXPLAIN statement or something?) > > > Finally, if anyone has any comments about my settings listed above > that might help improve performance, I thank you in advance. > > > Your default statistics target seemed low. Without knowing the nature > of your most resource intensive queries or how much memory tomcat is > using, it is hard to say more. Tomcat uses 4G of RAM, plus we have nginx in front using a little and some other, smaller services running on the server in addition to the usual Linux gamut of processes. > > Cheers, > > Jeff ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partition table in 9.0.x? 2013-01-04 21:31 Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 2013-01-05 04:03 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> 2013-01-06 15:27 ` Re: Partition table in 9.0.x? AJ Weber <aweber@comcast.net> @ 2013-01-08 15:26 ` Jeff Janes <jeff.janes@gmail.com> 2013-01-08 16:45 ` Re: Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 0 siblings, 1 reply; 9+ messages in thread From: Jeff Janes @ 2013-01-08 15:26 UTC (permalink / raw) To: AJ Weber <aweber@comcast.net>; +Cc: pgsql-performance On Sunday, January 6, 2013, AJ Weber wrote: > All fair questions... > > Thank you for your detailed response! > > > On 1/4/2013 11:03 PM, Jeff Janes wrote: > > On Friday, January 4, 2013, AJ Weber wrote: > >> Hi all, >> >> I have a table that has about 73mm rows in it and growing. > > > How big is the table in MB? Its indexes? > > Not sure on this. Will see if pgAdmin tells me. > It probably does, but from psql command line, you can do \d+ and \di+ > >> Anyway, this table is going to continue to grow, and it's used frequently >> (Read and Write). > > > Are all rows in the table read and written with equal vigor, or are > there hot rows and cold rows that can be recognized based on the row's > values? > > No, I could probably figure out a way to setup an "archive" or "older" > section of the data that is updated much less frequently. > So the data that deliniates this does not exist in that table, but it does exist someplace, either just in your head, or in the column of a higher level table? > Deletes are rare. Inserts/Updates "yes". Select on existing rows -- very > frequent. > If you have little control over your storage and are already IO bound, and the tables are growing rapidly, you may need to rethink that "deletes are rare" bit. So the inserts and updates do target a hot part, while the selects are evenly spread? In that case, it is very important to know if the slow part are the selects, or the insert and deletes. If the selects are slow, and the hot rows for selects can't be gathered together into a hot partition, then after clustering they will still be slow as the disk will still have to seek all over the place (massive data-mining type selects might be an exception to that, but I wouldn't count on it). > > > >> From what I read, this table is a candidate to be partitioned for >> performance and scalability. I have tested some scripts to build the >> "inherits" tables with their constraints and the trigger/function to >> perform the work. >> >> Am I doing the right thing by partitioning this? > > > Probably not. Or at least, you haven't given us the information to > know. Very broadly speaking, well-implemented partitioning makes bulk > loading and removal operations take less IO, but makes normal operations > take more IO, or if lucky leaves it unchanged. There are exceptions, but > unless you can identify a very specific reason to think you might have one > of those exceptions, then you probably don't. > > I know you can't believe everything you read, but I thought I saw some > metrics about when a table's size exceeds some fraction of available RAM, > or when it approaches 100mm rows, it's a big candidate for partitioning. > I think it is a matter of semantics. A small table is poor candidate for partitioning even if it has an excellent key to use for partitioning. A large table could be a good candidate up until you realize it doesn't have a good key to use, at which point it stops being a good candidate (in my opinion). > >> Should I run a cluster or vacuum full after all is done? >> > > Probably not. If a cluster after the partitioning would be beneficial, > there would be a pretty good chance you could do a cluster *instead* of the > partitioning and get the same benefit. > > I did try clustering the table on the PK (which is actually 4 columns), > and it appeared to help a bit. I was hoping partitioning was going to help > me even more. > Was the order_num (from the parent table) the leading field of the 4 column PK? If not, you might want to reorder the PK so that it is the leading field and cluster again. Or if reordering the PK columns is not convenient, make a new index on the order_num and cluster on that (perhaps dropping the index after the cluster, if it no longer serves a purpose) > >> Is there a major benefit if I can upgrade to 9.2.x in some way that I >> haven't realized? >> > > If you have specific queries that are misoptimized and so are generating > more IO than they need to, then upgrading could help. On the other hand, > it could also make things worse, if a currently well optimized query > becomes worse. > > Is there some new feature or optimization you're thinking about with > this comment? If so, could you please just send me a link and/or feature > name and I'll google it myself? > The main things I am thinking of are the "fudge factor" for large indexes, which is currently being discussed in both performance and hackers mailing lists, which was made overly aggressive in 9.2 and so can make it choose worse plans, and the "allow the planner to generate custom plans for specific parameter values even when using prepared statements" from the 9.2 release notes, which can allow it to choose better plans. But, surely there are other changes as well, which amount to corner cases and so are hard to discuss in the abstract. Which is why instrumentation is important. There isn't much point in worrying about possible changed plans until you've identified the queries that are important to worry about. > > But, instrumentation has improved in 9.2 from 9.0, so upgrading would > make it easier to figure out just which queries are really bad and have the > most opportunity for improvement. A little well informed optimization > might obviate the need for either partitioning or more hard drives. > > This is interesting too. I obviously would like the best available > options to tune the database and the application. Is this detailed in the > release notes somewhere, and what tools could I use to take advantage of > this? (Are there new/improved details included in the EXPLAIN statement or > something?) > track_io_timing is new, and it exposes new data into EXPLAIN (ANALYZE, BUFFERS) as well as into other places. You might not want to turn this on permanently, as it can affect performance (but you can test with pg_test_timing <https://mail.google.com/mail/mu/mp/635/pgtesttiming.html>as outlined in the docs to see how large probable affect it). Also, EXPLAIN displays the number row removed by filters, which may or may not be useful to you. Most exciting I think are the improvements to the contrib module pg_stat_statements. That would be my first recourse, to find out which of your statements are taking the most time (and/or IO). I try to install and configure this for all of my databases now as a matter of course. See the 9.2 release notes (with links therein to the rest of the documentation) for discussion of these. Cheers, Jeff > ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partition table in 9.0.x? 2013-01-04 21:31 Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 2013-01-05 04:03 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> 2013-01-06 15:27 ` Re: Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 2013-01-08 15:26 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> @ 2013-01-08 16:45 ` AJ Weber <aweber@comcast.net> 2013-01-08 17:21 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> 0 siblings, 1 reply; 9+ messages in thread From: AJ Weber @ 2013-01-08 16:45 UTC (permalink / raw) To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance > > It probably does, but from psql command line, you can do \d+ and \di+ \d+ doesn't appear to display any size information. > > If you have little control over your storage and are already IO bound, > and the tables are growing rapidly, you may need to rethink that > "deletes are rare" bit. So the inserts and updates do target a hot > part, while the selects are evenly spread? > > In that case, it is very important to know if the slow part are the > selects, or the insert and deletes. If the selects are slow, and the > hot rows for selects can't be gathered together into a hot partition, > then after clustering they will still be slow as the disk will still > have to seek all over the place (massive data-mining type selects > might be an exception to that, but I wouldn't count on it). Since order_num is sequential, I could partition on it in broad (sequential) ranges. That would put all recent/new rows in one table-partition that would be a fraction of the size of the overall (unpartitioned) table. I guess that would require manual maintenance over-time (to switch to another, new partition as each grows). > > I think it is a matter of semantics. A small table is poor candidate > for partitioning even if it has an excellent key to use for > partitioning. A large table could be a good candidate up until you > realize it doesn't have a good key to use, at which point it stops > being a good candidate (in my opinion). > My first idea to evenly-partition the table was to use the order_num and do a "mod" on it with the number of tables I wanted to use. That would yield a partition-table number of 0-mod, and all rows for the same order would stay within the same partition-table. However, you're right in thinking that a search for orders could -- really WOULD -- require retrieving details from multiple partitions, probably increasing IO. So maybe the sequential partitioning (if at all) is better, just more maintenance down-the-road. > > Was the order_num (from the parent table) the leading field of the 4 > column PK? If not, you might want to reorder the PK so that it is the > leading field and cluster again. Or if reordering the PK columns is > not convenient, make a new index on the order_num and cluster on that > (perhaps dropping the index after the cluster, if it no longer serves > a purpose) > Yes, the order_num is the first column in the PK, and our main browse queries use, at a minimum, the first 2-3 columns in that PK in their where-clause. Many thanks again for all the input! -AJ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partition table in 9.0.x? 2013-01-04 21:31 Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 2013-01-05 04:03 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> 2013-01-06 15:27 ` Re: Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 2013-01-08 15:26 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> 2013-01-08 16:45 ` Re: Partition table in 9.0.x? AJ Weber <aweber@comcast.net> @ 2013-01-08 17:21 ` Jeff Janes <jeff.janes@gmail.com> 2013-01-08 17:51 ` Re: Partition table in 9.0.x? Tom Lane <tgl@sss.pgh.pa.us> 2013-01-08 18:04 ` Re: Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 0 siblings, 2 replies; 9+ messages in thread From: Jeff Janes @ 2013-01-08 17:21 UTC (permalink / raw) To: AJ Weber <aweber@comcast.net>; +Cc: pgsql-performance On Tue, Jan 8, 2013 at 8:45 AM, AJ Weber <aweber@comcast.net> wrote: > >> >> It probably does, but from psql command line, you can do \d+ and \di+ > > \d+ doesn't appear to display any size information. It does if you use it without an argument, to display all the tables in the search path: jjanes=# \d+ List of relations Schema | Name | Type | Owner | Size | Description --------+------------------+-------+--------+---------+------------- public | pgbench_accounts | table | jjanes | 128 MB | public | pgbench_branches | table | jjanes | 40 kB | public | pgbench_history | table | jjanes | 0 bytes | public | pgbench_tellers | table | jjanes | 40 kB | (4 rows) It rather annoys me that you actually get less information (no size, no owner) when you use \d+ on a named table. I don't know if there is a reason for that feature, or if it was just an oversight. > > >> >> If you have little control over your storage and are already IO bound, and >> the tables are growing rapidly, you may need to rethink that "deletes are >> rare" bit. So the inserts and updates do target a hot part, while the >> selects are evenly spread? >> >> In that case, it is very important to know if the slow part are the >> selects, or the insert and deletes. If the selects are slow, and the hot >> rows for selects can't be gathered together into a hot partition, then after >> clustering they will still be slow as the disk will still have to seek all >> over the place (massive data-mining type selects might be an exception to >> that, but I wouldn't count on it). > > Since order_num is sequential, I could partition on it in broad (sequential) > ranges. That would put all recent/new rows in one table-partition that > would be a fraction of the size of the overall (unpartitioned) table. I > guess that would require manual maintenance over-time (to switch to another, > new partition as each grows). Yep. If your selects are concentrated in those recent/new, this could be very useful. But, if your selects are not concentrated on the recent/new rows, the benefit would be small. > > >> >> I think it is a matter of semantics. A small table is poor candidate for >> partitioning even if it has an excellent key to use for partitioning. A >> large table could be a good candidate up until you realize it doesn't have a >> good key to use, at which point it stops being a good candidate (in my >> opinion). >> > My first idea to evenly-partition the table was to use the order_num and do > a "mod" on it with the number of tables I wanted to use. That would yield a > partition-table number of 0-mod, The current constraint exclusion code is quite simple-minded and doesn't know how to make use of check constraints that use the mod function, so the indexes of all partitions would have to be searched for each order_num-driven query, even though we know the data could only exist in one of them. The constraint exclusion codes does understand check constraints that involve ranges. There could still be some benefit as the table data would be concentrated, even if the index data is not. > and all rows for the same order would stay > within the same partition-table. But usually a given order_num would only be of interest for a fraction of a second before moving on to some other order_num of interest, so by the time the relevant partition become fully cached, it would no longer be hot. Or, if the partitions were small enough, you could assume that all rows would be dragged into memory when the first one was requested because they lay so close to each other. But it is not feasible to have a large enough number of partitions to make that happen. But if the table is clustered, this is exactly what you would get--the trouble would be keeping it clustered. If most of the line-items are inserted at the same time as each other, they probably should be fairly well clustered to start with. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partition table in 9.0.x? 2013-01-04 21:31 Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 2013-01-05 04:03 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> 2013-01-06 15:27 ` Re: Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 2013-01-08 15:26 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> 2013-01-08 16:45 ` Re: Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 2013-01-08 17:21 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> @ 2013-01-08 17:51 ` Tom Lane <tgl@sss.pgh.pa.us> 1 sibling, 0 replies; 9+ messages in thread From: Tom Lane @ 2013-01-08 17:51 UTC (permalink / raw) To: Jeff Janes <jeff.janes@gmail.com>; +Cc: AJ Weber <aweber@comcast.net>; pgsql-performance Jeff Janes <jeff.janes@gmail.com> writes: > On Tue, Jan 8, 2013 at 8:45 AM, AJ Weber <aweber@comcast.net> wrote: >> >> \d+ doesn't appear to display any size information. > It does if you use it without an argument, to display all the tables > in the search path: > jjanes=# \d+ > List of relations > Schema | Name | Type | Owner | Size | Description > --------+------------------+-------+--------+---------+------------- > public | pgbench_accounts | table | jjanes | 128 MB | > public | pgbench_branches | table | jjanes | 40 kB | > public | pgbench_history | table | jjanes | 0 bytes | > public | pgbench_tellers | table | jjanes | 40 kB | > (4 rows) > It rather annoys me that you actually get less information (no size, > no owner) when you use \d+ on a named table. I don't know if there is > a reason for that feature, or if it was just an oversight. This is actually an abbreviation for \dtisv+, which is a completely different command from "\d table". You can use something like "\dt+ table-pattern" to get a display of the above form for a subset of tables. I agree it ain't too consistent. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partition table in 9.0.x? 2013-01-04 21:31 Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 2013-01-05 04:03 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> 2013-01-06 15:27 ` Re: Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 2013-01-08 15:26 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> 2013-01-08 16:45 ` Re: Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 2013-01-08 17:21 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> @ 2013-01-08 18:04 ` AJ Weber <aweber@comcast.net> 2013-01-14 18:24 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> 1 sibling, 1 reply; 9+ messages in thread From: AJ Weber @ 2013-01-08 18:04 UTC (permalink / raw) To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance > It does if you use it without an argument, to display all the tables > in the search path: > > jjanes=# \d+ > List of relations > Schema | Name | Type | Owner | Size | Description > --------+------------------+-------+--------+---------+------------- > public | pgbench_accounts | table | jjanes | 128 MB | > public | pgbench_branches | table | jjanes | 40 kB | > public | pgbench_history | table | jjanes | 0 bytes | > public | pgbench_tellers | table | jjanes | 40 kB | > (4 rows) > > It rather annoys me that you actually get less information (no size, > no owner) when you use \d+ on a named table. I don't know if there is > a reason for that feature, or if it was just an oversight. That is rather peculiar. Sorry for that. Table in question is 9284MB (Parent table is 621MB) > > The current constraint exclusion code is quite simple-minded and > doesn't know how to make use of check constraints that use the mod > function, so the indexes of all partitions would have to be searched > for each order_num-driven query, even though we know the data could > only exist in one of them. The constraint exclusion codes does > understand check constraints that involve ranges. Hmm. That's a bit of a limitation I didn't know about. I assume it doesn't understand the percent (mod operator) just the same as not understanding the MOD() function? Either way, I guess this strategy does not pan-out. > There could still be some benefit as the table data would be > concentrated, even if the index data is not. I'm reaching way, way back in my head, but I think _some_ RDBMS I worked with previously had a way to "cluster" the rows around a single one of the indexes on the table, thus putting the index and the row-data "together" and reducing the number of IO's to retrieve the row if that index was used. Am I understanding that PG's "cluster" is strictly to group like rows together logically -- table data only, not to coordinate the table row with the index upon which you clustered them? > >> and all rows for the same order would stay >> within the same partition-table. > But usually a given order_num would only be of interest for a fraction > of a second before moving on to some other order_num of interest, so > by the time the relevant partition become fully cached, it would no > longer be hot. Or, if the partitions were small enough, you could > assume that all rows would be dragged into memory when the first one > was requested because they lay so close to each other. But it is not > feasible to have a large enough number of partitions to make that > happen. But if the table is clustered, this is exactly what you would > get--the trouble would be keeping it clustered. If most of the > line-items are inserted at the same time as each other, they probably > should be fairly well clustered to start with. Does decreasing the fill to like 90 help keep it clustered in-between times that I could shutdown the app and perform a (re-) cluster on the overall table? Problem is, with a table that size, and the hardware I'm "blessed with", the cluster takes quite a bit of time. :( -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partition table in 9.0.x? 2013-01-04 21:31 Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 2013-01-05 04:03 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> 2013-01-06 15:27 ` Re: Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 2013-01-08 15:26 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> 2013-01-08 16:45 ` Re: Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 2013-01-08 17:21 ` Re: Partition table in 9.0.x? Jeff Janes <jeff.janes@gmail.com> 2013-01-08 18:04 ` Re: Partition table in 9.0.x? AJ Weber <aweber@comcast.net> @ 2013-01-14 18:24 ` Jeff Janes <jeff.janes@gmail.com> 0 siblings, 0 replies; 9+ messages in thread From: Jeff Janes @ 2013-01-14 18:24 UTC (permalink / raw) To: AJ Weber <aweber@comcast.net>; +Cc: pgsql-performance On Tue, Jan 8, 2013 at 10:04 AM, AJ Weber <aweber@comcast.net> wrote: >> >> The current constraint exclusion code is quite simple-minded and >> doesn't know how to make use of check constraints that use the mod >> function, so the indexes of all partitions would have to be searched >> for each order_num-driven query, even though we know the data could >> only exist in one of them. The constraint exclusion codes does >> understand check constraints that involve ranges. > > Hmm. That's a bit of a limitation I didn't know about. I assume it doesn't > understand the percent (mod operator) just the same as not understanding the > MOD() function? Either way, I guess this strategy does not pan-out. Yes, it doesn't detect either. It would use it if you formulate to every equality query with an extra restriction: "where id=1234567 and mod(id,100)=67" or whatever. (But I was surprised that % and mod() are not recognized as being equivalent. If you specify it one way in the check constraint, you need to use the same "spelling" in the where clause) >> There could still be some benefit as the table data would be >> concentrated, even if the index data is not. > > I'm reaching way, way back in my head, but I think _some_ RDBMS I worked > with previously had a way to "cluster" the rows around a single one of the > indexes on the table, thus putting the index and the row-data "together" and > reducing the number of IO's to retrieve the row if that index was used. In Oracle this is called in "index organized table" or IOT (or it was at one point, they have the habit of rename most of their features with each release). I don't know what other RDBMS call it. Supporting secondary indexes when the table data could move around was quite intricate/weird. PG doesn't have this index-organized-table feature--it has been discussed but I don't of any currently active effort to add it. There is another feature, sometimes called clustering, in which the rows from different tables can be mingled together in the same block. So both the parent order and the child order_line_item that have the same order_num (i.e. the join column) would be in the same block. So once you query for a specific order and did the necessary IO, the corresponding order_line_item rows would already be in memory. I thought this was interesting, but I don't know how often it was actually used. > Am > I understanding that PG's "cluster" is strictly to group like rows together > logically -- table data only, not to coordinate the table row with the index > upon which you clustered them? They are coordinated in a sense. Not as one single structure, but as two structures in parallel. >>> and all rows for the same order would stay >>> within the same partition-table. >> >> But usually a given order_num would only be of interest for a fraction >> of a second before moving on to some other order_num of interest, so >> by the time the relevant partition become fully cached, it would no >> longer be hot. Or, if the partitions were small enough, you could >> assume that all rows would be dragged into memory when the first one >> was requested because they lay so close to each other. But it is not >> feasible to have a large enough number of partitions to make that >> happen. But if the table is clustered, this is exactly what you would >> get--the trouble would be keeping it clustered. If most of the >> line-items are inserted at the same time as each other, they probably >> should be fairly well clustered to start with. > > Does decreasing the fill to like 90 help keep it clustered in-between times > that I could shutdown the app and perform a (re-) cluster on the overall > table? Problem is, with a table that size, and the hardware I'm "blessed > with", the cluster takes quite a bit of time. :( Probably not. If the data starts out clustered and gets updated a lot, lowering the fill factor might be able to prevent some de-clustering due to row migration. But when you insert new rows, PG makes no effort to put them near existing rows with the same key. (In a hypothetical future in which that did happen, lowering the fill factor would then probably help) Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2013-01-14 18:24 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2013-01-04 21:31 Partition table in 9.0.x? AJ Weber <aweber@comcast.net> 2013-01-05 04:03 ` Jeff Janes <jeff.janes@gmail.com> 2013-01-06 15:27 ` AJ Weber <aweber@comcast.net> 2013-01-08 15:26 ` Jeff Janes <jeff.janes@gmail.com> 2013-01-08 16:45 ` AJ Weber <aweber@comcast.net> 2013-01-08 17:21 ` Jeff Janes <jeff.janes@gmail.com> 2013-01-08 17:51 ` Tom Lane <tgl@sss.pgh.pa.us> 2013-01-08 18:04 ` AJ Weber <aweber@comcast.net> 2013-01-14 18:24 ` Jeff Janes <jeff.janes@gmail.com>
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