[GENERAL] pg_dump with low priority?

[Bryan Field-Elliot]

We have a huge database which must be backed up every day with pg_dump. The problem is, it takes around half an hour to produce the dump file, and all other processes on the same box are starved for cycles (presumably due to I/O) during the dump. It's not just an inconvenience, it's now evolved into a serious problem that needs to be addressed.

Is there any mechanism for running pg_dump with a lower priority? I don't mind if the backup takes two hours instead of half an hour, as long as other processes were getting their fair share of cycles.

Thank you for any advice,

Bryan

[GENERAL]

[Bryan Field-Elliot]

subscribe

[GENERAL] How to optimize select count(*)..group by?

[Bryan Field-Elliot]

We have this simple query:

select status, count(*) from customer group by status;

There is already a btree index on status, but, the customer table is huge, and this query must be executed very frequently... an explain on this query shows that it is quite costly (and we notice it runs slowly)...

Can someone recommend the best technique to optimize this? We can create new indices, we can re-write this query.. But we'd rather not add new tables or columns if possible (not just to solve this problem).

Thank you,

Bryan

[GENERAL] Stuck processes, viewing the queries inside

[Bryan Field-Elliot]

We have a running server (v7.3) which, over time, seems to accumulate a
bunch of stuck PostgreSQL instances which appear to be dead inside of
a transaction. For example, ps reveals many of these:


postgres 17683  0.0  0.4  7376 1032 ?SNov13   0:21 postgres:
bryan sourceid 127.0.0.1 idle in transaction

As admin, I'd like to run psql and peer inside the system (using the
Statistics Collector) to see which queries have been run inside some of
these stuck transactions. It will help me debug what's going wrong.

This page:

http://www.postgresql.org/docs/7.3/static/monitoring-stats.html

gives lots of intereseting methods for pulling information on the
running state, but I can't see a query for given a process ID currently
in a transaction, show me all the queries which have been run in it so
far.

Any pointers would be appreciated,

Bryan



---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] Show triggers in psql?

[Bryan Field-Elliot]

Forgive me if I am missing the obvious, but can someone please tell me how
to show a list of triggers, or the code in a specific trigger, via the pgsql
utility?

Thank you,
Bryan

[GENERAL] Benchmarking PGSQL against Microsoft SQL 7.0?

[Bryan Field-Elliot]

Please forgive my newbieness but I am extremely new to both Postgresql AND
Linux. I've been a consultant for 6 years working mostly on Microsoft
platforms (NT, MSSQL). I have developed many data-driven websites using
WinNT/IIS/ASP/MSSQL. Now, getting sick of MS and being enamored with the
whole Open Source movement, I wish to learn (and develop a project or two)
on Linux/Apache/PHP/PGSQL.

I was wondering if anyone has done any benchmarking (even roughly),
comparing PGSQL to Microsoft SQL Server 7.0 or even the older MSSQL 6.5.
Specifically I just want the reassurance that the two products are in the
same ballpark as far as performance goes. Has anyone done this recently?

Thank you,
Bryan