Re: [GENERAL] How to reduce impact of a query.

2008-11-19 Thread Howard Cole
Craig Ringer wrote: Howard Cole wrote: Unfortunately I am on a windows platform. Plus I am running windows software raid so there is little tweaking allowed. Don't write the possibility off too quickly. The driver may well accept parameters for readahead settings etc, either through a

Re: [GENERAL] How to reduce impact of a query.

2008-11-18 Thread Craig Ringer
Howard Cole wrote: > Unfortunately I am on a windows platform. Plus I am running windows > software raid so there is little tweaking allowed. Don't write the possibility off too quickly. The driver may well accept parameters for readahead settings etc, either through a custom configuration applet

Re: [GENERAL] How to reduce impact of a query.

2008-11-18 Thread Howard Cole
Craig Ringer wrote: If I reduce maintenance_work_mem then the database dump/restore is slower but there is less overall impact on the server. There could be more impact, rather than less, if it forces a sort that'd be done in memory out to disk instead. If you have dedicated storage on sep

Re: [GENERAL] How to reduce impact of a query.

2008-11-17 Thread Craig Ringer
Scott Marlowe wrote: > On Mon, Nov 17, 2008 at 11:10 PM, Craig Ringer > <[EMAIL PROTECTED]> wrote: > >> I also think it's a wee bit of a pity that there's no way to tell Pg >> that a job isn't important, so data shouldn't be permitted to push much >> else out of shared_buffers or the OS's cache. T

Re: [GENERAL] How to reduce impact of a query.

2008-11-17 Thread Scott Marlowe
On Mon, Nov 17, 2008 at 11:10 PM, Craig Ringer <[EMAIL PROTECTED]> wrote: > I also think it's a wee bit of a pity that there's no way to tell Pg > that a job isn't important, so data shouldn't be permitted to push much > else out of shared_buffers or the OS's cache. The latter can be ensured > to

Re: [GENERAL] How to reduce impact of a query.

2008-11-17 Thread Craig Ringer
Howard Cole wrote: > Thanks for the input Scott. You are correct - I am IO bound, but only > for the query described. 99% of the time, my IO runs at 3% or less, even > during peak times, only this one query, which happens approximately 10 > times a day grinds the system to a halt. If your I/O is

Re: [GENERAL] How to reduce impact of a query.

2008-11-17 Thread Craig Ringer
Howard Cole wrote: > If I reduce maintenance_work_mem > then the database dump/restore is slower but there is less overall > impact on the server. There could be more impact, rather than less, if it forces a sort that'd be done in memory out to disk instead. If you have dedicated storage on separ

Re: [GENERAL] How to reduce impact of a query.

2008-11-17 Thread Howard Cole
Scott Marlowe wrote: Best of luck on this. Thanks Scott. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to reduce impact of a query.

2008-11-17 Thread Scott Marlowe
On Mon, Nov 17, 2008 at 9:36 AM, Howard Cole <[EMAIL PROTECTED]> wrote: > Scott Marlowe wrote: >> >> The problem is most likely you're I/O bound. If one query is hitting >> a table it can pull in data (sequentially) at 40 to 80 megabytes per >> second. Since most of your queries are small, they d

Re: [GENERAL] How to reduce impact of a query.

2008-11-17 Thread Howard Cole
Scott Marlowe wrote: The problem is most likely you're I/O bound. If one query is hitting a table it can pull in data (sequentially) at 40 to 80 megabytes per second. Since most of your queries are small, they don't run into each other a lot, so to speak. As soon as your big reporting query hit

Re: [GENERAL] How to reduce impact of a query.

2008-11-17 Thread Scott Marlowe
On Mon, Nov 17, 2008 at 8:42 AM, Howard Cole <[EMAIL PROTECTED]> wrote: > Scott Marlowe wrote: >> >> Your entire disk io subsystem is a pair of hard drives. I'm assuming >> software RAID. > > Correct. >>> >>> The time that this query takes is not the issue, rather it is the impact >>> that it has

Re: [GENERAL] How to reduce impact of a query.

2008-11-17 Thread Howard Cole
Scott Marlowe wrote: Your entire disk io subsystem is a pair of hard drives. I'm assuming software RAID. Correct. The time that this query takes is not the issue, rather it is the impact that it has on the server - effectively killing it for the 40 seconds due to the heavy disk access.

Re: [GENERAL] How to reduce impact of a query.

2008-11-17 Thread Scott Marlowe
On Mon, Nov 17, 2008 at 8:17 AM, Howard Cole <[EMAIL PROTECTED]> wrote: > Teodor Sigaev wrote: >>> >>> The machine in question is a 1GB Ram, AMD 64 with Raid 1 Sata disks. Non Your entire disk io subsystem is a pair of hard drives. I'm assuming software RAID. > The time that this query takes is

Re: [GENERAL] How to reduce impact of a query.

2008-11-17 Thread Howard Cole
Teodor Sigaev wrote: The machine in question is a 1GB Ram, AMD 64 with Raid 1 Sata disks. Non standard parts of my postgresql.conf are as follows: max_connections=100 shared_buffers=128MB work_mem=4MB maintenance_work_mem=256MB max_fsm_pages=204800 max_fsm_relations=1500 Any tips appreciated.

Re: [GENERAL] How to reduce impact of a query.

2008-11-17 Thread Teodor Sigaev
The machine in question is a 1GB Ram, AMD 64 with Raid 1 Sata disks. Non standard parts of my postgresql.conf are as follows: max_connections=100 shared_buffers=128MB work_mem=4MB maintenance_work_mem=256MB max_fsm_pages=204800 max_fsm_relations=1500 Any tips appreciated. Pls, show 1) effectiv

Re: [GENERAL] How to reduce impact of a query.

2008-11-17 Thread Isak Hansen
On Mon, Nov 17, 2008 at 1:15 PM, Howard Cole <[EMAIL PROTECTED]> wrote: > Hi, > > I am running multiple 8.2 databases on a not-so-powerful W2K3 server - and > it runs great - for the majority of time. However I have some monster > tsearch queries which take a lot of processing and hog system resour