[PERFORM] Usage of pg_stat_database

2011-10-24 Thread Amitabh Kant
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

2011-10-24 Thread Amitabh Kant
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

2011-10-24 Thread David Boreham


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

2011-10-24 Thread Venkat Balaji
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

2011-10-24 Thread Robert Haas
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

2011-10-24 Thread Merlin Moncure
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

2011-10-24 Thread Claudio Freire
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

2011-10-24 Thread David Boreham

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

2011-10-24 Thread Robert Haas
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

2011-10-24 Thread David Boreham

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

2011-10-24 Thread Venkat Balaji
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