[PERFORM] Usage of pg_stat_database
If I read the xact_commit field returned by "Select * from pg_stat_database" multiple times, and then average the difference between consecutive values, would this give an approx idea about the transactions per second in my database? Does this figure include the number of select statements being executed in the db? With regards Amitabh
[PERFORM] Choosing between Intel 320, Intel 510 or OCZ Vertex 3 SSD for db server
Hello I need to choose between Intel 320 , Intel 510 and OCZ Vertex 3 SSD's for my database server. From recent reading in the list and other places, I have come to understand that OCZ Vertex 3 should not be used, Intel 510 uses a Marvel controller while Intel 320 had a nasty bug which has been rectified. So the list narrows down to only 510 and 320, unless I have understood the OCZ Vertex reviews incorrectly. The server would itself be built along these lines: Dual CPU Xeon 5620, 32 or 48 GB RAM, 2 SAS 10K disk in RAID 1 for OS, 2 SAS 10K disk in RAID 1 for pg_xlog and 4 SSD in RAID 10 for data directory (overkill??). OS would be FreeBSD 8.2 (I would be tuning the sysctl variables). PG version would be 9.1 with replication set to another machine (Dual CPU Xeon 54xx, 32 GB RAM, 6 15K SAS 146 GB: 4 in RAID 10 for data and 2 in RAID 1 for OS + pg_xlog). The second machine hosts my current db , and there is not much of an issue with the performance. We need better redundancy now(current was to take a dump/backup every 12 hours), so the new machine. My database itself is not very big, approx 40 GB as of now, and would not grow beyond 80 GB in the next year or two. There are some tables where insert & updates are fairly frequent. From what I could gather, we are not doing more than 300-400 tps at the moment, and the growth should not be very high in the short term. Hope someone can give some pointers to which SSD I should go for at the moment. Amitabh
Re: [PERFORM] Choosing between Intel 320, Intel 510 or OCZ Vertex 3 SSD for db server
A few quick thoughts: 1. 320 would be the only SSD I'd trust from your short-list. It's the only one with proper protection from unexpected power loss. 2. Multiple RAID'ed SSDs sounds like (vast) overkill for your workload. A single SSD should be sufficient (will get you several thousand TPS on pgbench for your DB size). 3. Consider not using the magnetic disks at all (saves on space, power and the cost of the RAID controller for them). 4. Consider using Intel 710 series rather than 320 (pay for them with the money saved from #3 above). Those devices have much, much higher specified endurance than the 320s and since your DB is quite small you only need to buy one of them. On 10/24/2011 8:09 AM, Amitabh Kant wrote: Hello I need to choose between Intel 320 , Intel 510 and OCZ Vertex 3 SSD's for my database server. From recent reading in the list and other places, I have come to understand that OCZ Vertex 3 should not be used, Intel 510 uses a Marvel controller while Intel 320 had a nasty bug which has been rectified. So the list narrows down to only 510 and 320, unless I have understood the OCZ Vertex reviews incorrectly. The server would itself be built along these lines: Dual CPU Xeon 5620, 32 or 48 GB RAM, 2 SAS 10K disk in RAID 1 for OS, 2 SAS 10K disk in RAID 1 for pg_xlog and 4 SSD in RAID 10 for data directory (overkill??). OS would be FreeBSD 8.2 (I would be tuning the sysctl variables). PG version would be 9.1 with replication set to another machine (Dual CPU Xeon 54xx, 32 GB RAM, 6 15K SAS 146 GB: 4 in RAID 10 for data and 2 in RAID 1 for OS + pg_xlog). The second machine hosts my current db , and there is not much of an issue with the performance. We need better redundancy now(current was to take a dump/backup every 12 hours), so the new machine. My database itself is not very big, approx 40 GB as of now, and would not grow beyond 80 GB in the next year or two. There are some tables where insert & updates are fairly frequent. From what I could gather, we are not doing more than 300-400 tps at the moment, and the growth should not be very high in the short term. Hope someone can give some pointers to which SSD I should go for at the moment. Amitabh -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] : PG9.0 - Checkpoint tuning and pg_stat_bgwriter
Thanks Greg ! Sorry for delayed response. We are actually waiting to change the checkpoint_segments in our production systems (waiting for the downtime). Thanks VB On Wed, Oct 5, 2011 at 11:02 AM, Greg Smith wrote: > On 10/04/2011 07:50 PM, Venkat Balaji wrote: > >> I was thinking to increase checkpoint_segments to around 16 or 20. >> >> I think 50 is a bit higher. >> >> > Don't be afraid to increase that a lot. You could set it to 1000 and that > would be probably turn out fine; checkpoints will still happen every 5 > minutes. > > Checkpoints represent a lot of the I/O in a PostgreSQL database. The main > downside to making them less frequent is that recovery after a crash will > take longer; a secondary one is that WAL files in pg_xlog will take up more > space. Most places don't care much about either of those things. The > advantage to making them happen less often is that you get less total > writes. People need to be careful about going a long *time* between > checkpoints. But there's very few cases where you need to worry about the > segment count going too high before another one is triggered. > > > -- > Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD > PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us > >
Re: [PERFORM] Tsearch2 - bad performance with concatenated ts-vectors
On Tue, Aug 2, 2011 at 2:22 AM, Jan Wielgus wrote:
> So, there is apparently a problem with vector concatenating - the indexes
> don't work then. I tried to use the vectors separately and to make 'OR'
> comparison between single vector @@ ts_query checks,
> but it didn't help very much (performance was better, but still over 20 sec):
> ...
> (participant.participant_tsv @@
> to_tsquery('simple',to_tsquerystring('Abigail'))) OR (person.person_tsv @@
> to_tsquery('simple',to_tsquerystring('Abigail')))
> ...
>
> Is there a way to make this work with better performance? Or is it necessary
> to create a single vector that contains data from multiple tables and then
> add an index on it? It would be so far problematic for us,
> because we are using multiple complex queries with variable number of
> selected columns. I know that another solution might be an union among
> multiple queries, every of which uses a single vector,
> but this solution is inconvenient too.
Only something of the form 'indexed-column indexable-operator value'
is going to be indexable. So when you concatenate two columns from
different tables - as you say - not indexable.
In general, OR-based conditions that cross table boundaries tend to be
expensive, because they have to be applied only after performing the
join. You can't know for sure looking only at a row from one table
whether or not it will be needed, so you have to join them all and
then filter the results.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Choosing between Intel 320, Intel 510 or OCZ Vertex 3 SSD for db server
On Mon, Oct 24, 2011 at 11:53 AM, David Boreham wrote: > > A few quick thoughts: > > 1. 320 would be the only SSD I'd trust from your short-list. It's the only > one with proper protection from unexpected power loss. yeah. > 2. Multiple RAID'ed SSDs sounds like (vast) overkill for your workload. A > single SSD should be sufficient (will get you several thousand TPS on > pgbench for your DB size). Also, raid controllers interfere with TRIM. > 3. Consider not using the magnetic disks at all (saves on space, power and > the cost of the RAID controller for them). Agree. If one SSD did not deliver the tps, I'd consider buying more and optimizing with jbod/tablespaces -- really doubt that's necessary however. Maybe a single large slow magnetic disk is a good idea for retaining backups though. > 4. Consider using Intel 710 series rather than 320 (pay for them with the > money saved from #3 above). Those devices have much, much higher specified > endurance than the 320s and since your DB is quite small you only need to > buy one of them. 710 is good idea if and only if you are worried about write durability (in which case it's a great idea). merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Choosing between Intel 320, Intel 510 or OCZ Vertex 3 SSD for db server
On Mon, Oct 24, 2011 at 6:31 PM, Merlin Moncure wrote: >> 2. Multiple RAID'ed SSDs sounds like (vast) overkill for your workload. A >> single SSD should be sufficient (will get you several thousand TPS on >> pgbench for your DB size). > > Also, raid controllers interfere with TRIM. What about redundancy? How do you swap an about-to-die SSD? Software RAID-1? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Choosing between Intel 320, Intel 510 or OCZ Vertex 3 SSD for db server
On 10/24/2011 3:31 PM, Merlin Moncure wrote: 4. Consider using Intel 710 series rather than 320 (pay for them with the > money saved from #3 above). Those devices have much, much higher specified > endurance than the 320s and since your DB is quite small you only need to > buy one of them. 710 is good idea if and only if you are worried about write durability (in which case it's a great idea). I disagree with this (that it is the only reason to select 710 series). The write endurance (specified at least) is orders of magnitude higher. Doing 100's of TPS constantly it is possible to burn through the 320's endurance lifetime in a year or two. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] : PG9.0 - Checkpoint tuning and pg_stat_bgwriter
On Oct 24, 2011, at 8:16 AM, Venkat Balaji wrote: > Thanks Greg ! > > Sorry for delayed response. > > We are actually waiting to change the checkpoint_segments in our production > systems (waiting for the downtime). That setting can be changed without downtime. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Choosing between Intel 320, Intel 510 or OCZ Vertex 3 SSD for db server
On 10/24/2011 4:47 PM, Claudio Freire wrote: What about redundancy? How do you swap an about-to-die SSD? Software RAID-1? The approach we take is that we use 710 series devices which have predicted reliability similar to all the other components in the machine, therefore the unit of replacement is the entire machine. We don't use trays for example (which saves quite a bit on data center space). If I were running short endurance devices such as 320 series I would be interested in replacing the drives before the machine itself is likely to fail, but I'd do so by migrating the data and load to another machine for the replacement to be done offline. Note that there are other operations procedures that need to be done and can not be done without downtime (e.g. OS upgrade), so some kind of plan to deliver service while a single machine is down for a while will be needed regardless of the storage device situation. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] : PG9.0 - Checkpoint tuning and pg_stat_bgwriter
Oh yes. Thanks a lot Robert ! Regards VB On Tue, Oct 25, 2011 at 7:47 AM, Robert Haas wrote: > On Oct 24, 2011, at 8:16 AM, Venkat Balaji wrote: > > Thanks Greg ! > > > > Sorry for delayed response. > > > > We are actually waiting to change the checkpoint_segments in our > production systems (waiting for the downtime). > > That setting can be changed without downtime. > > ...Robert
