Обсуждение: Lots o' I/O
I have a database that was populated about two months ago, and one particular table has begun causing problems. It's got about 20,000 records, all fixed length of about 1 kbytes. If we do any operation that involves a sequential scan of that table (e.g., select count(*)), it now takes about 20 seconds, and according to linux vmstat, reads 275000 disk blocks (275 mbytes). The database is vacuumed each night. I made a copy of the table (create table c2 select * from c), and a count on that table takes much less than a second. Any ideas on what the difference in these tables could be, and how I can avoid this problem? Thanks.
On Fri, 14 Feb 2003, Clarence Gardner wrote: > > I have a database that was populated about two months ago, and one > particular table has begun causing problems. It's got about 20,000 > records, all fixed length of about 1 kbytes. If we do any operation > that involves a sequential scan of that table (e.g., select count(*)), > it now takes about 20 seconds, and according to linux vmstat, reads > 275000 disk blocks (275 mbytes). The database is vacuumed each night. What does vacuum full verbose <table> show? And how big is the actual data file?
On Fri, 14 Feb 2003, Stephan Szabo wrote: > On Fri, 14 Feb 2003, Clarence Gardner wrote: > > > > > I have a database that was populated about two months ago, and one > > particular table has begun causing problems. It's got about 20,000 > > records, all fixed length of about 1 kbytes. If we do any operation > > that involves a sequential scan of that table (e.g., select count(*)), > > it now takes about 20 seconds, and according to linux vmstat, reads > > 275000 disk blocks (275 mbytes). The database is vacuumed each night. > > What does vacuum full verbose <table> show? And how big is the actual > data file? > The FULL made the difference -- the table now performs like the copy. Despite a nightly vacuum analyze, we've never done a vacuum full. The docs (http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-vacuum.html) almost, but not quite, come out against it.... We're going to do a full vacuum weekly now. Thanks, all.
On Fri, 14 Feb 2003, Clarence Gardner wrote: > On Fri, 14 Feb 2003, Stephan Szabo wrote: > > > On Fri, 14 Feb 2003, Clarence Gardner wrote: > > > > > > > > I have a database that was populated about two months ago, and one > > > particular table has begun causing problems. It's got about 20,000 > > > records, all fixed length of about 1 kbytes. If we do any operation > > > that involves a sequential scan of that table (e.g., select count(*)), > > > it now takes about 20 seconds, and according to linux vmstat, reads > > > 275000 disk blocks (275 mbytes). The database is vacuumed each night. > > > > What does vacuum full verbose <table> show? And how big is the actual > > data file? > > > > The FULL made the difference -- the table now performs like the copy. > Despite a nightly vacuum analyze, we've never done a vacuum full. The > docs (http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-vacuum.html) > almost, but not quite, come out against it.... > > We're going to do a full vacuum weekly now. Actually, if that did work and the locking of vacuum full is an issue, you may just need to raise your free space map settings and see if that helps your overall growth/performance. Basically, in short, when vacuum sees empty space it tries to record where that space is, but it only keeps a fixed amount of information on the empty space, so if you have alot of pages that end up with a little bit of empty space you can end up having alot of that empty space go to waste.