Re: Opteron/FreeBSD/PostgreSQL performance poor

On Fri, 7 Jul 2006, andy rost wrote:

>> is that 100k context switches over 10 seconds or one second? that
>> might be something to check out. pg 8.1 is regarded as the solution
>> to any cs problem, though.
>
> According to man top, that's 100K per second. I'm interested in your
> recommendation but am not sure what "pg 8.1" references

pg 8.1 means PostgreSQL 8.1.x (preferably 8.1.4) which is said to resolve many
context switch issues.

>>
>> can you please approximate roughly how many transactions per second
>> your server is handling while you are getting the 20 load condition
>> (and, if possible, broken down into read and write transactions)?
>
> Do you have any suggestions on how I might obtain these metrics?
>

Every five minutes do:

select sum(xact_commit) + sum(xact_rollback) as transactions from pg_stat_database;

and then divide the difference by 300 and that's your transactions per second:

select sum(xact_commit) + sum(xact_rollback) as transactions from
pg_stat_database;

transactions
--------------
231894522
(1 row)

<wait 300 seconds>

select sum(xact_commit) + sum(xact_rollback) as transactions from
pg_stat_database;

transactions
--------------
231907346
(1 row)

(231907346-231894522)/300 = 42.74666666666666666666 TPS

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954