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
