On Thu, 17 Jul 2014 11:28:04 -0700 Prabhjot Sheena <prabhjot.sheena@xxxxxxxxxxxxxx> wrote: > Thanks i have changed that to 64 and reloaded it. > > When i had load issue today there was this exact same query that hits the > db like 50 to 60 times from different machines in 3 to 4 minutes and was > taking long time to execute and was holding up the database. i did recreate > an index and it started performing better. My question is why it is not > fetching the result from the memory since its the same query that runs > again and again. > > This is the actual query i m taking about: > > SELECT account.id, account.organization_id, run.application_id, > work_unit.script, work_unit.id, work_unit.start_time, run.id, > work_unit.priority FROM work_unit, run, account WHERE work_unit.status = > 3 AND work_unit.run_id = run.id AND work_unit.type != 1 AND run.status = 1 > AND run.account_id = account.id > > Pls suggest if i can do something to fix this Provide a lot more information if you want anyone on the list to be able to help: such as explain output while the problem is happening, and some information about the makeup of the tables (column types/indexes/# rows). Guessing, based on the little information you've provided, it's likely that you have something else going on at the same time that you're not aware of, and this particular query is only a symptom. I'm saying that because SELECTs don't generally create any WAL traffic, so there were probably some INSERT/UPDATE/DELETE running at the same time that both pushed those 3 tables out of memory and/or saturated disk activity to the point that accessing everything becomes slow for a short while, and it's just those queries that you noticed. Are you making the mistake where you set log_min_duration to 1s and only worry about queries that take longer than 1s? Because I've seen (on multiple occasions) where many 1000s of queries, each running less than 1s, are the actual cause of the problem. pgBadger is particularly helpful in tracking down situations like that. > On Thu, Jul 17, 2014 at 11:06 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > > > Potentialtech <wmoran@xxxxxxxxxxxxxxxxx> writes: > > > If the warning isn't happening too often, I would try increasing it only > > a > > > little and see if it helps. If it's not enough you can then increase it > > some > > > more. Various sources around the Internet suggest that you don't want > > to go > > > much larger than 256 for this (if only because it's uncommon to do so > > and is > > > probably indicative of other tuning that you need to do). > > Unfortunatley, you > > > need to restart PG for the change to take effect, so you have to balance > > > experimenting with your tuning against how often you can get away with a > > server > > > restart. > > > > Huh? You don't need a restart, just a reload (SIGHUP) to change that. > > > > regards, tom lane > > -- Potentialtech <wmoran@xxxxxxxxxxxxxxxxx>
