 | |
#1
 
| |||
| |||
|
Performance probs -
01-24-2007
, 08:04 AM
#2
| |||
| |||
|
|
Hi, we have several postgres sql 8.x machines here with about 4500 tables on each. Each table has about 150000 up to 5000000 rows. We saw, that a select count(*) on some tables takes about 120 secs. Other tables with same amount |
Quote:
|
of date answers within 50 msecs. there is an index on each table for a timestamp row. We thought, the index is corrupt and dropped them. We create the index new - same problem with count(*). |
Quote:
|
The problem is not related to the count of rows for that specific table. Second try was to dump table, drop table and play back the dump. Now the count is delivvered within millisecs. |
You have found the problem!
You table contains many dead rows, you should regulary do a vacuum.
end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
#3
| |||
| |||
|
|
begin peter stimpel schrieb: Hi, we have several postgres sql 8.x machines here with about 4500 tables on each. Each table has about 150000 up to 5000000 rows. We saw, that a select count(*) on some tables takes about 120 secs. Other tables with same amount Because of MVCC, a count(*) implies a full table scan. of date answers within 50 msecs. there is an index on each table for a timestamp row. We thought, the index is corrupt and dropped them. We create the index new - same problem with count(*). An index can't help in tis case. |
other DB products) a full index scan can help indeed because the index
has typically less leaf pages hence less IO.
Quote:
|
The problem is not related to the count of rows for that specific table. Second try was to dump table, drop table and play back the dump. Now the count is delivvered within millisecs. You have found the problem! You table contains many dead rows, you should regulary do a vacuum. |
docs about how the storage engine works.
Kind regards
robert
#4
| |||
| |||
|
|
of date answers within 50 msecs. there is an index on each table for a timestamp row. We thought, the index is corrupt and dropped them. We create the index new - same problem with count(*). An index can't help in tis case. Is that the case with Postgres? Theoretically (and also practically for other DB products) a full index scan can help indeed because the index has typically less leaf pages hence less IO. |
information_schema, but for this you need actual statistics.
Quote:
|
You have found the problem! You table contains many dead rows, you should regulary do a vacuum. That's interesting news. On one of those days I have to read Postgres docs about how the storage engine works. |
this. And yes: an update create also a dead row.
¹ a VACUUM makes this rows re-useable, a VACUUL FULL deletes records on
the disk.
end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
#5
| |||
| |||
|
|
The problem is not related to the count of rows for that specific table. Second try was to dump table, drop table and play back the dump. Now the count is delivvered within millisecs. You have found the problem! You table contains many dead rows, you should regulary do a vacuum. |
But I do a "vacuum analyze;" each night. So that shouldnt be the source of
the probhlem, I think.
I think, when psql accepts the vacuum statement, then it will be executed,
How can i check that vacuum run was successful?
Any other idea?
btw: the most data inside this table will only be inserted, not deleted nor
updated...
Peter
#6
| |||
| |||
|
|
begin Robert Klemme schrieb: of date answers within 50 msecs. there is an index on each table for a timestamp row. We thought, the index is corrupt and dropped them. We create the index new - same problem with count(*). An index can't help in tis case. Is that the case with Postgres? Theoretically (and also practically for other DB products) a full index scan can help indeed because the index has typically less leaf pages hence less IO. There are other solutions for PG, for instance, you can ask the information_schema, but for this you need actual statistics. |
Quote:
|
You have found the problem! You table contains many dead rows, you should regulary do a vacuum. That's interesting news. On one of those days I have to read Postgres docs about how the storage engine works. Deleted rows are marked as deleted, but not realy deleted. VACUUM¹ does this. And yes: an update create also a dead row. |
reused as long as no VACUUM was executed? That sounds scary to me.
Apparently others found that as well:
http://www.postgresql.org/docs/8.2/i...tml#AUTOVACUUM
Kind regards
robert
#7
| |||
| |||
|
|
Second try was to dump table, drop table and play back the dump. Now the count is delivvered within millisecs. You have found the problem! You table contains many dead rows, you should regulary do a vacuum. But I do a "vacuum analyze;" each night. So that shouldnt be the source of the probhlem, I think. |
When SELECT count(*) takes too long, try the following:
Run a VACUUM VERBOSE on the table from the command line.
See how many removable rows are reported!
Then try the SELECT count(*) again and see if things work better.
Maybe the nocturnal VACUUM fails for some reason?
Yours,
Laurenz Albe
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.