[PERFORM] Why queries takes too much time to execute?
Hi. I´m new here and i´m not sure if this is the right email to solve my problem. Well, i have a very large database, with vary tables and very registers. Every day, too many operations are perfomed in that DB, with queries that insert, delete and update. Once a week some statistics are collected using vacuum analyze. The problem is after a period of time (one month, i think), the queries takes too much time to perform. A simple update can take 10 seconds or more to perform. If i make a backup, drop and recreate the DB, everything goes back normal. Could anyone give me any guidance?
Re: [PERFORM] Why queries takes too much time to execute?
Anderson Boechat Lopes wrote: Hi. I´m new here and i´m not sure if this is the right email to solve my problem. Well, i have a very large database, with vary tables and very registers. Every day, too many operations are perfomed in that DB, with queries that insert, delete and update. Once a week some statistics are collected using vacuum analyze. i guess you need to run it much more frequently than that. Thought you haven't given actual size of data etc., once or twice per day should be much better. The problem is after a period of time (one month, i think), the queries takes too much time to perform. A simple update can take 10 seconds or more to perform. You need to vacuum full once in a while and setup FSM parameters correctly. If i make a backup, drop and recreate the DB, everything goes back normal. Could anyone give me any guidance? Check following for basic performance tuning http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html HTH Shridhar ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Why queries takes too much time to execute?
Well, i have a very large database, with vary tables and very registers. Every day, too many operations are perfomed in that DB, with queries that insert, delete and update. Once a week some statistics are collected using vacuum analyze. Have vacuum analyze running once an HOUR if it's very busy. If you are using 7.4, run the pg_autovacuum daemon that's in contrib/pg_autovacuum. The problem is after a period of time (one month, i think), the queries takes too much time to perform. A simple update can take 10 seconds or more to perform. If running vacuum analyze once an hour doesn't help, try running a vacuum full once a week or something to fix the problem. Also, try upgrading to 7.4 which has indexes that won't suffer from bloat. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Why queries takes too much time to execute?
Hum... now i think i´m beginning to understand. The vacuum analyse is recommended to perform at least every day, after adding or deleting a large number of records, and not vacuum full analyse. I´ve performed the vacuum full analyse every day and after some time i´ve noticed the database was corrupted. I couldn´t select anything any more. Do you think if i perform vacuum analyse once a day and perform vacuum full analyse once a week, i will get to fix this problem? Thanks for help me, folks. PS: Sorry for my grammar mistakes. My writting is not so good. :) - Original Message - From: "Shridhar Daithankar" <[EMAIL PROTECTED]> To: "Anderson Boechat Lopes" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, May 10, 2004 11:21 AM Subject: Re: [PERFORM] Why queries takes too much time to execute? > Anderson Boechat Lopes wrote: > > Hi. > > > > I´m new here and i´m not sure if this is the right email to solve my > > problem. > > > > Well, i have a very large database, with vary tables and very > > registers. Every day, too many operations are perfomed in that DB, with > > queries that insert, delete and update. Once a week some statistics are > > collected using vacuum analyze. > > i guess you need to run it much more frequently than that. Thought you haven't > given actual size of data etc., once or twice per day should be much better. > > > > The problem is after a period of time (one month, i think), the > > queries takes too much time to perform. A simple update can take 10 > > seconds or more to perform. > > You need to vacuum full once in a while and setup FSM parameters correctly. > > > > If i make a backup, drop and recreate the DB, everything goes back > > normal. > > > > Could anyone give me any guidance? > > Check following for basic performance tuning > > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html > http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html > > HTH > > Shridhar > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] Configuring PostgreSQL to minimize impact of checkpoints
My company is developing a PostgreSQL application. We're using 7.3.4 but will soon upgrade to 7.4.x. Our OS is RedHat 9. Our production machines have 512 MB RAM and IDE disks. So far we've been using default configuration settings, but I have started to examine performance and to modify these settings. Our typical transaction involves 5-10 SELECT, INSERT or UPDATEs, (usually 1/2 SELECT and the remainder a mixture of INSERT and UPDATE). There are a few aggregation queries which need to scan an entire table. We observed highly uneven performance for the small transactions. A transaction usually runs in under 100 msec, but we would see spikes as high as 40,000 msec. These spikes occurred regularly, every 4-5 minutes, and I speculated that checkpointing might be the issue. I created a test case, based on a single table: create table test( id int not null, count int not null, filler varchar(200), primary key(id)) I loaded a database with 1,000,000 rows, with the filler column always filled with 200 characters. I then ran a test in which a random row was selected, and the count column incremented. Each transaction contained ten such updates. In this test, I set shared_buffers = 2000 checkpoint_segments = 40 checkpoint_timeout = 600 wal_debug = 1 I set checkpoint_segments high because I wanted to see whether the spikes correlated with checkpoints. Most transactions completed in under 60 msec. Approximately every 10th transaction, the time went up to 500-600 msec, (which is puzzling, but not my major concern). I did see a spike every 10 minutes, in which transaction time goes up to 5000-8000 msec. The spikes were correlated with checkpoint activity, occurring slightly before a log entry that looks like this: 2004-05-09 16:34:19 LOG: INSERT @ 2/C2A0F628: prev 2/C2A0F5EC; xprev 0/0; xid 0: XLOG - checkpoint: redo 2/C2984D4C; undo 0/0; sui 36; xid 1369741; oid 6321782; online Questions: 1. Can someone provide an overview of checkpoint processing, to help me understand the performance issues? 2. Is the spike due to the checkpoint process keeping the disk busy? Or is there some locking involved that blocks my application until the checkpoint completes? 3. The spikes are quite problematic for us. What can I do to minimize the impact of checkpointing on my application? I understand how checkpoint_segments and checkpoint_timeout determine when a checkpoint occurs; what can I do to lessen the impact of a checkpoint? 4. I understand that a "background writer" is being contemplated for 7.5. Will that replace or augment the checkpoint process? Any comments on how that work will apply to my problem would be appreciated. I wouldn't mind seeing the average performance, (without the spikes) go up -- let's say -- 10%, in exchange for more uniform performance. These spikes are a real problem. Jack Orenstein This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Configuring PostgreSQL to minimize impact of checkpoints
[EMAIL PROTECTED] wrote: > 4. I understand that a "background writer" is being contemplated for > 7.5. Will that replace or augment the checkpoint process? Any > comments on how that work will apply to my problem would be > appreciated. I wouldn't mind seeing the average performance, > (without the spikes) go up -- let's say -- 10%, in exchange for > more uniform performance. These spikes are a real problem. The background writer is designed to address your specific problem. We will stil checkpoint, but the spike should be greatly minimized. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Why queries takes too much time to execute?
[EMAIL PROTECTED] ("Anderson Boechat Lopes") writes:
> I´m new here and i´m not sure if this is the right email to
> solve my problem.
This should be OK...
> Well, i have a very large database, with vary tables and very
> registers. Every day, too many operations are perfomed in that DB,
> with queries that insert, delete and update. Once a week some
> statistics are collected using vacuum analyze.
>
> The problem is after a period of time (one month, i think), the
> queries takes too much time to perform. A simple update can take 10
> seconds or more to perform.
It seems fairly likely that two effects are coming in...
-> The tables that are being updated have lots of dead tuples.
-> The vacuums aren't doing much good because the number of dead
tuples is so large that you blow out the FSM (Free Space Map), and
thus they can't free up space.
-> Another possibility is that if some tables shrink to small size,
and build up to large size (we see this with the _rserv_log_1_
and _rserv_log_2_ tables used by the eRServ replication system),
the statistics may need to be updated a LOT more often.
You might want to consider running VACUUM a whole lot more often than
once a week. If there is any regular time that the system isn't
terribly busy, you might want to vacuum some or all tables at that
time.
pg_autovacuum might be helpful; it will automatically do vacuums on
tables when they have been updated heavily.
There may be more to your problem, but VACUUMing more would allow us
to get rid of "too many dead tuples around" as a cause.
--
"cbbrowne","@","acm.org"
http://cbbrowne.com/info/x.html
Would-be National Mottos:
USA: "There oughta' be a law!"
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PERFORM] Help how to tune-up my Database
scott.marlowe wrote: sort_mem might do with a small bump, especially if you're only handling a few connections at a time. Be careful, it's per sort, and measured in megs, so it's easy for folks to set it too high and make their machine start flushing too much kernel cache, which will slow down the other backends that have to go to disk for data. http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html (under "Memory"), it says that sort_mem is set in KB. Is this document wrong (or outdated)? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Why queries takes too much time to execute?
On Mon, 10 May 2004, Anderson Boechat Lopes wrote: > Hum... now i think i´m beginning to understand. > > The vacuum analyse is recommended to perform at least every day, after > adding or deleting a large number of records, and not vacuum full analyse. > I´ve performed the vacuum full analyse every day and after some time i´ve > noticed the database was corrupted. I couldn´t select anything any more. Hold it right there, full stop. If you've got corruption you've either found a rare corner case in postgresql (unlikely, corruption is not usually a big problem for postgresql) OR you have bad hardware. Test your RAM, CPUs, and hard drives before going any further. Data corruption, 99% of the time, is not the fault of postgresql but the fault of the hardware. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Help how to tune-up my Database
Sorry about that, I meant kbytes, not megs. My point being it's NOT measured in 8k blocks, like a lot of other settings. sorry for the mixup. On Fri, 7 May 2004, Bricklen wrote: > scott.marlowe wrote: > > sort_mem might do with a small bump, especially if you're only handling a > > few connections at a time. Be careful, it's per sort, and measured in > > megs, so it's easy for folks to set it too high and make their machine > > start flushing too much kernel cache, which will slow down the other > > backends that have to go to disk for data. > > http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html > (under "Memory"), it says that sort_mem is set in KB. Is this document > wrong (or outdated)? > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Configuring PostgreSQL to minimize impact of checkpoints
Bruce Momjian wrote: [EMAIL PROTECTED] wrote: 4. I understand that a "background writer" is being contemplated for 7.5. Will that replace or augment the checkpoint process? Any comments on how that work will apply to my problem would be appreciated. I wouldn't mind seeing the average performance, (without the spikes) go up -- let's say -- 10%, in exchange for more uniform performance. These spikes are a real problem. The background writer is designed to address your specific problem. We will stil checkpoint, but the spike should be greatly minimized. Thanks. Do you know when 7.5 is expected to be released? Until then, is a workaround known? Also, are the delays I'm seeing out of the ordinary? I'm looking at one case in which two successive transactions, each updating a handful of records, take 26 and 18 *seconds* (not msec) to complete. These transactions normally complete in under 30 msec. Jack Orenstein ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Configuring PostgreSQL to minimize impact of checkpoints
Jack Orenstein wrote: > Bruce Momjian wrote: > > [EMAIL PROTECTED] wrote: > > > >>4. I understand that a "background writer" is being contemplated for > >>7.5. Will that replace or augment the checkpoint process? Any > >>comments on how that work will apply to my problem would be > >>appreciated. I wouldn't mind seeing the average performance, > >>(without the spikes) go up -- let's say -- 10%, in exchange for > >>more uniform performance. These spikes are a real problem. > > > > > > The background writer is designed to address your specific problem. We > > will stil checkpoint, but the spike should be greatly minimized. > > > > Thanks. Do you know when 7.5 is expected to be released? 3-6 months. > Until then, is a workaround known? Also, are the delays I'm seeing out of the > ordinary? > I'm looking at one case in which two successive transactions, each updating a > handful of > records, take 26 and 18 *seconds* (not msec) to complete. These transactions > normally complete > in under 30 msec. Wow. Others might know the answer to that. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Configuring PostgreSQL to minimize impact of checkpoints
Jack Orenstein <[EMAIL PROTECTED]> writes: > I'm looking at one case in which two successive transactions, each > updating a handful of records, take 26 and 18 *seconds* (not msec) to > complete. These transactions normally complete in under 30 msec. I've seen installations in which it seemed that the "normal" query load was close to saturating the available disk bandwidth, and the extra load imposed by a background VACUUM just pushed the entire system's response time over a cliff. In an installation that has I/O capacity to spare, a VACUUM doesn't really hurt foreground query response at all. I suspect that the same observations hold true for checkpoints, though I haven't specifically seen an installation suffering from that effect. Already-committed changes for 7.5 include a background writer, which basically will "trickle" out dirty pages between checkpoints, thereby hopefully reducing the volume of I/O forced at a checkpoint. We have also got code in place that throttles the rate of I/O requests during VACUUM. It seems like it might be useful to similarly throttle the I/O request rate during a CHECKPOINT, though I'm not sure if there'd be any bad side effects from lengthening the elapsed time for a checkpoint. (Jan, any thoughts here?) None of this is necessarily going to fix matters for an installation that has no spare I/O capacity, though. And from the numbers you're quoting I fear you may be in that category. "Buy faster disks" may be the only answer ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
