Обсуждение: Speeding up query
I have Server running on Windows XP using
PostgreSQL 8.1.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)
Db size is 862 MB
8 users
Bigger tables:
1 1214 pg_shdepend 775 MB
2 1232 pg_shdepend_depender_index 285 MB
3 19701 rid 234 MB
4 19301 bilkaib 170 MB
5 1233 pg_shdepend_reference_index 156 MB
6 19335 dok 146 MB
Sometimes simple query
explain SELECT dokumnr
FROM DOK
where dokumnr IN (110774)
AND (
dokumnr IN (SELECT dokumnr FROM bilkaib WHERE
alusdok='LY' AND masin LIKE 'a%') )
"Nested Loop IN Join (cost=0.00..8.51 rows=1 width=4)"
" -> Index Scan using dok_dokumnr_idx on dok (cost=0.00..4.81 rows=1
width=4)"
" Index Cond: (dokumnr = 110774)"
" -> Index Scan using bilkaib_dokumnr_idx on bilkaib (cost=0.00..44.26
rows=12 width=4)"
" Index Cond: (dokumnr = 110774)"
" Filter: ((alusdok = 'LY'::bpchar) AND (masin ~~ 'a%'::text))"
takes 34 seconds. Tables are indexed and logfile shows autovacuum running.
I ran VACUUM ANALYZE.
It returns
INFO: free space map contains 22501 pages in 77 relations
DETAIL: A total of 20000 page slots are in use (including overhead).
111216 page slots are required to track all free space.
Current limits are: 20000 page slots, 1000 relations, using 186 KB.
NOTICE: number of page slots needed (111216) exceeds max_fsm_pages (20000)
HINT: Consider increasing the configuration parameter "max_fsm_pages" to a
value over 111216.
Query returned successfully with no result in 201099 ms.
How to speed up this query ?
Should I set max_fsm_pages to a 113000 or other suggestions ?
Andrus.
On Wednesday 05 November 2008, "Andrus" <kobruleht2@hot.ee> wrote: > takes 34 seconds. Tables are indexed and logfile shows autovacuum > running. I ran VACUUM ANALYZE. > It returns > > INFO: free space map contains 22501 pages in 77 relations > DETAIL: A total of 20000 page slots are in use (including overhead). > 111216 page slots are required to track all free space. > Current limits are: 20000 page slots, 1000 relations, using 186 KB. > NOTICE: number of page slots needed (111216) exceeds max_fsm_pages > (20000) HINT: Consider increasing the configuration parameter > "max_fsm_pages" to a value over 111216. > Query returned successfully with no result in 201099 ms. > > How to speed up this query ? > > Should I set max_fsm_pages to a 113000 or other suggestions ? Undoubtedly (higher), to prevent table bloat, but you'll also need to post your table structures and an explain analyze for help speeding this particular query. -- Alan
On Wed, Nov 05, 2008 at 07:51:24PM +0200, Andrus wrote: > I have Server running on Windows XP using > PostgreSQL 8.1.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 > (mingw-special) You really could do with updating that; 8.1.4 is very old. 8.1.15 is the latest in the 8.1 series and has lots of bug fixes. > Db size is 862 MB > > Bigger tables: > 1 1214 pg_shdepend 775 MB > 2 1232 pg_shdepend_depender_index 285 MB > 5 1233 pg_shdepend_reference_index 156 MB those look scary, scary big to me. Have you been running without autovacuum for a while and creating *lots* of tables or something? > I ran VACUUM ANALYZE. > It returns > > INFO: free space map contains 22501 pages in 77 relations > DETAIL: A total of 20000 page slots are in use (including overhead). > 111216 page slots are required to track all free space. > Current limits are: 20000 page slots, 1000 relations, using 186 KB. > NOTICE: number of page slots needed (111216) exceeds max_fsm_pages (20000) > HINT: Consider increasing the configuration parameter "max_fsm_pages" to a > value over 111216. > Query returned successfully with no result in 201099 ms. It's saying that there's a lot of tables with unused space in them. If you've deleted lots of stuff from the database then this will be normal, but because you didn't mention many details I'd assume this probably isn't right. VACUUM FULL and then lots of REINDEXing would be one solution, but this probably isn't the easiest. > How to speed up this query ? > > Should I set max_fsm_pages to a 113000 or other suggestions ? Doing this will cause the error message to go away, but it's not going to solve the underlying problem. Your database looks quite bloated; if you can afford the downtime I'd be tempted to do a full backup and restore. This will reduce bloat a lot and also provide a good opportunity to update PG. The good thing about doing it from a restore is that you don't have to go through REINDEXing everything by hand and potentially miss lots of things out. If things are going to shrink a lot, restoring is normally quicker as well. A good way to test would be to do a backup and see how big the resulting file is. I'd expect the database to be three or four times the size of the plain text backup (depending on table design and index use it can vary quite a bit either way), so if the dump is less than a hundred MB you're probably better off doing a restore. Sam
> You really could do with updating that; 8.1.4 is very old. 8.1.15 is
> the latest in the 8.1 series and has lots of bug fixes.
Will update increase speed ?
Server is running for approx 4 years now and I havent encountered any bugs.
>> Db size is 862 MB
>>
>> Bigger tables:
>> 1 1214 pg_shdepend 775 MB
>> 2 1232 pg_shdepend_depender_index 285 MB
>> 5 1233 pg_shdepend_reference_index 156 MB
>
> those look scary, scary big to me. Have you been running without
> autovacuum for a while and creating *lots* of tables or something?
Log file shows many messages
autovacuum: processing database "mydb" every day.
So I expect it is running.
After VACUUM ANALYZE I ran
VACUUM FULL; REINDEX DATABASE mydb;REINDEX SYSTEM mydb
after that I got
1 1214 pg_shdepend 440 MB
2 1232 pg_shdepend_depender_index 285 MB
3 1233 pg_shdepend_reference_index 155 MB
4 19701 rid 103 MB
5 19301 bilkaib 93 MB
6 19335 dok 46 MB
> Your database looks quite bloated; if you can afford the downtime I'd be
> tempted to do a full backup and restore. This will reduce bloat a lot
> and also provide a good opportunity to update PG. The good thing about
> doing it from a restore is that you don't have to go through REINDEXing
> everything by hand and potentially miss lots of things out. If things
> are going to shrink a lot, restoring is normally quicker as well.
>
> A good way to test would be to do a backup and see how big the resulting
> file is. I'd expect the database to be three or four times the size of
> the plain text backup (depending on table design and index use it can
> vary quite a bit either way), so if the dump is less than a hundred MB
> you're probably better off doing a restore.
I have acces to this db only from port 5432
Thus Text backup takes a lot of time and server upgrade is not possible.
I ran
VACUUM FULL; REINDEX DATABASE mydb;REINDEX SYSTEM mydb
and hope this produces the same results and backup/restore.
Andrus.
On Wed, Nov 05, 2008 at 09:52:29PM +0200, Andrus wrote: > >You really could do with updating that; 8.1.4 is very old. 8.1.15 is > >the latest in the 8.1 series and has lots of bug fixes. > > Will update increase speed ? > Server is running for approx 4 years now and I havent encountered any bugs. Not much for speed I don't think, the main reason for upgrading is that it should fix a few cases where it can corrupt itself. Have a read through here: http://www.postgresql.org/docs/8.1/static/release.html > Log file shows many messages > > autovacuum: processing database "mydb" every day. > > So I expect it is running. A normal VACUUM only takes care of marking deleted/updated data as being available for reuse. If you insert lots of data and then delete it again, this space will remain marked for reuse (assuming your FSM settings are large enough) even though it's never going to be reused. In these cases it's good to do a FULL vacuum, but normally it's best to just leave the autovacuum doing its thing. Not sure what's going on with the "pg_shdepend" table+indexes at all though, or even if it's bad! > VACUUM FULL; REINDEX DATABASE mydb;REINDEX SYSTEM mydb Hum, I'd never noticed the REINDEX DATABASE command before! that should save a bit of fiddling. > after that I got > > 1 1214 pg_shdepend 440 MB > 2 1232 pg_shdepend_depender_index 285 MB > 3 1233 pg_shdepend_reference_index 155 MB these all still seem quite big, at least in my (somewhat limited) experience. If anyone else has experience of what would cause these to grow I'd be interested in finding out! > 4 19701 rid 103 MB > 5 19301 bilkaib 93 MB > 6 19335 dok 46 MB So, performance of these should be a bit better. A seqscan of half the data should take half the time... Sam
Sam Mason wrote: > On Wed, Nov 05, 2008 at 09:52:29PM +0200, Andrus wrote: > > after that I got > > > > 1 1214 pg_shdepend 440 MB > > 2 1232 pg_shdepend_depender_index 285 MB > > 3 1233 pg_shdepend_reference_index 155 MB > > these all still seem quite big, at least in my (somewhat limited) > experience. If anyone else has experience of what would cause these to > grow I'd be interested in finding out! Lots of grants maybe? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.