Re: [SQL] vacuum analyze slows sql query
On Tue, Nov 02, 2004 at 06:50:31PM -0800, patrick ~ wrote: > We have a nightly "garbage collection" process that runs and purges > any old data. After this process a 'vacuum analyze' is kicked off > (regardless of whether or not any data was actually purged). > > At this point I should mention that our customer sites are running > PostgreSQL 7.1.3; however, I am able to reproduce the issue on 7.4.2. A 7.1 system takes an exclusive lock on any VACUUM. It's the same as VACUUM FULL in 7.4. Nothing you can do to make that not be sluggish. You want to get those sites off 7.1 anyway. At the very least, you should be aware of xid exhaustion which can be prevented in 7.1 only with an initdb and complete restore. Failure to accommodate that will mean that one day your databases will just disappear. Current VACUUM certainly does impose a serious I/O load; this is the reason for the vacuum setting tweaks in 8.0. See the -hackers archives (from more than a year ago now) for (for instance) Jan Wieck's discussion of his feature and the subsequent debates. > I noticed that a freshly created db with freshly inserted data (from > a previous pg_dump) would result in quite fast results. However, > after running 'vacuum analyze' the very same query slowed down about > 1250x (Time: 1080688.921 ms vs Time: 864.522 ms). > My best guess is that there's something going on inside your function. I'd be looking for locks here, though. That makes no sense, given that you've only 78 rows being returned. BTW, this topic should probably be better pursued on -performance. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] vacuum analyze slows sql query
have you reindexes your tables. When I was running 7.1.4, I ran a vacuum and reindex
nightly. Otherwise your index
files will keep getting bigger and bigger (this has been fixed in 7.4).
Jim
-- Original Message ---
From: patrick ~ <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Sent: Tue, 2 Nov 2004 18:50:31 -0800 (PST)
Subject: [SQL] vacuum analyze slows sql query
> Greetings pgsql-sql,
>
> I have a very strange problem. Our production database is a fair
> sized db, structure wise, and quite huge data wise. We have a web/php
> based UI for our customer to manage the data in our application db.
> The customer complains that the UI is sluggish accessing certain
> pages and completely times-out on certain other pages.
>
> We have a nightly "garbage collection" process that runs and purges
> any old data. After this process a 'vacuum analyze' is kicked off
> (regardless of whether or not any data was actually purged).
>
> At this point I should mention that our customer sites are running
> PostgreSQL 7.1.3; however, I am able to reproduce the issue on 7.4.2.
> If it at all matters, customer is running the db on a Dell PowerEdge
> 2550 equiped with 1gig of ram. My personal test box is a 700Mhz Intel
> with 512mb ram.
>
> I have in the past made modifications to our SQL statements to make
> queries more efficient. At this point I have given up and set out
> to strip down our database and data enough to be able to post to
> the list and ask for help from more qualified SQL experts.
>
> In the process of "stripping down" our database I noticed some very
> strange behavior which I could not explain. I started to reformulate
> my original to-be post to to the list to ask assistence in explaining
> this strange behavior I was observing. Next I noticed yet another
> strange issue with PostgreSQL.
>
> I noticed that a freshly created db with freshly inserted data (from
> a previous pg_dump) would result in quite fast results. However,
> after running 'vacuum analyze' the very same query slowed down about
> 1250x (Time: 1080688.921 ms vs Time: 864.522 ms).
>
> Following is a paste from a psql shell after a dropdb, createdb
> and populate db. The query is fast. I next run 'explain' and 'explain
> verbose' on the query. Then you see a 'vacuum analyze' followed by
> the the 'explain', 'explain verbose' and lastly the query again which
> is now extremely slow!
>
> -- begin
> orig=# select offer_id, pkk_offer_has_pending_purch( offer_id ) from pkk_offer
> ;
>
> (618 rows)
>
> Time: 864.522 ms
> orig=# explain select offer_id, pkk_offer_has_pending_purch( offer_id ) from
> pkk_offer ;
> QUERY PLAN
> -
> Seq Scan on pkk_offer (cost=0.00..22.50 rows=1000 width=4)
> (1 row)
>
> Time: 24.251 ms
> orig=# explain verbose select offer_id, pkk_offer_has_pending_purch( offer_id )
> from pkk_offer ;
>
> QUERY PLAN
> -
> {SEQSCAN
> :startup_cost 0.00
> :total_cost 22.50
> :plan_rows 1000
> :plan_width 4
> :targetlist (
>{TARGETENTRY
>:resdom
> {RESDOM
> :resno 1
> :restype 23
> :restypmod -1
> :resname offer_id
> :ressortgroupref 0
> :resorigtbl 34965071
> :resorigcol 1
> :resjunk false
> }
>
>:expr
> {VAR
> :varno 1
> :varattno 1
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 1
> }
>}
>
>{TARGETENTRY
>:resdom
> {RESDOM
> :resno 2
> :restype 16
> :restypmod -1
> :resname pkk_offer_has_pending_purch
> :ressortgroupref 0
> :resorigtbl 0
> :resorigcol 0
> :resjunk false
> }
>
>:expr
> {FUNCEXPR
> :funcid 34965096
> :funcresulttype 16
> :funcretset false
> :funcformat 0
> :args (
> {VAR
> :varno 1
> :varattno 1
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 1
> }
> )
> }
>}
> )
>
> :qual <>
> :lefttree <>
> :righttree <>
> :initPlan <>
> :extParam ()
>
> :allParam ()
>
> :nParamExec 0
> :scanrelid 1
> }
>
> Seq Scan on pkk_offer (cost=0.00..22.50 rows=1000 width=4)
> (78 rows)
>
> Time: 10.915 ms
> orig=# vacuum analyze ;
> VACUUM
> Time: 504701.795 ms
> orig=# explain select offer_id, pkk_offer_has_pending_purch( offer_id ) from
> pkk_offer ;
> QUERY PLAN
> -
[SQL] trigger status
What's the easy way to tell if a trigger has fired??? thanks ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] trigger status
On Wed, Nov 03, 2004 at 08:57:35AM -0600, hook wrote: > What's the easy way to tell if a trigger has fired??? Tell from where? You could log messages from the trigger (e.g., with RAISE INFO in PL/pgSQL), but that might not be what you're talking about. What are you trying to do? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
[SQL] CREATE TYPE
I want to create a data type that have tow arguments in the defenition, like the varchar type: create table mytmp(name varchar(10)); I like to do it like this: create table mytmp(name myvarchar(10,"en_US")); whow can it be done by "CREATE TYPE" I want to implement the internal functions (compare, ...). I faunded the documentation but I can't see any thing that help me to create a type same as varchar( with argument) Notice: I don't mean using "CREATE TYPE myvarchar AS( lenght integer, collate char(10)) Thanks ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CREATE TYPE
On Nov 3, 2004, at 10:56 AM, Ameen - Etemady wrote: I like to do it like this: create table mytmp(name myvarchar(10,"en_US")); you can't unless you modify the parser. It has special cases to support varchar (and numeric) syntax. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(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: [SQL] vacuum analyze slows sql query
On Wed, Nov 03, 2004 at 10:31:33AM -0800, patrick ~ wrote: > Just to clarify, the sliggishness isn't only during the vacuum > period. There are more more serious issues during the vacuum, > but i am not touching on those. The sluggishness is persistant > throughout daily operations. Then I suspect you have two problems, not one. The locking in 7.1 will certainly cause the timeouts during vacuum, but won't explain the other items. Someone else suggested REINDEX, also, which is likely needed. But again, you'll get more useful remarks from the -performance list. > Yes, the plan is to upgrade them with new release of our product. > I didn't know about the xid exhaustion problem. I'll need to > search the mailing list archives. You can learn a bit about this in the _current_ version of the docs under regular maintenance. > Again to clarify, the output I pasted was from my standalone > PostgreSQL box. That is, it wasn't being used other than those > quries being executed. That's even worse. I certainly can't explain your results, then. You really want to move to the -performance list for that. > I don't know if you looked at my stored function, but there are > no locks in it (no explicit ones anyway). Foreign keys are one well-known area of locking surprises. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] vacuum analyze slows sql query
On Wed, Nov 03, 2004 at 07:01:00AM -0500, Andrew Sullivan wrote: > On Tue, Nov 02, 2004 at 06:50:31PM -0800, patrick ~ wrote: > > We have a nightly "garbage collection" process that runs and > > purges any old data. After this process a 'vacuum analyze' > > is kicked off (regardless of whether or not any data was > > actually purged). > > > > At this point I should mention that our customer sites are > > running PostgreSQL 7.1.3; however, I am able to reproduce > > the issue on 7.4.2. > > A 7.1 system takes an exclusive lock on any VACUUM. It's the > same as VACUUM FULL in 7.4. Nothing you can do to make that > not be sluggish. Just to clarify, the sliggishness isn't only during the vacuum period. There are more more serious issues during the vacuum, but i am not touching on those. The sluggishness is persistant throughout daily operations. > You want to get those sites off 7.1 anyway. At the very least, > you should be aware of xid exhaustion which can be prevented in > 7.1 only with an initdb and complete restore. Failure to > accommodate that will mean that one day your databases will just > disappear. Yes, the plan is to upgrade them with new release of our product. I didn't know about the xid exhaustion problem. I'll need to search the mailing list archives. > Current VACUUM certainly does impose a serious I/O load; this is > the reason for the vacuum setting tweaks in 8.0. See the -hackers > archives (from more than a year ago now) for (for instance) Jan > Wieck's discussion of his feature and the subsequent debates. I'll look into this. I don't think we are ready to touch 8.x yet. > > I noticed that a freshly created db with freshly inserted data > > (from a previous pg_dump) would result in quite fast results. > > However, after running 'vacuum analyze' the very same query > > slowed down about 1250x (Time: 1080688.921 ms vs Time: 864.522 > > ms). > > My best guess is that there's something going on inside your > function. I'd be looking for locks here, though. That makes no > sense, given that you've only 78 rows being returned. BTW, this > topic should probably be better pursued on -performance. Again to clarify, the output I pasted was from my standalone PostgreSQL box. That is, it wasn't being used other than those quries being executed. I don't know if you looked at my stored function, but there are no locks in it (no explicit ones anyway). Thanks for your reply, --patrick __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com ---(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: [SQL] vacuum analyze slows sql query
patrick ~ <[EMAIL PROTECTED]> writes: > I noticed that a freshly created db with freshly inserted data (from > a previous pg_dump) would result in quite fast results. However, > after running 'vacuum analyze' the very same query slowed down about > 1250x (Time: 1080688.921 ms vs Time: 864.522 ms). If it gets slower immediately after a single vacuum analyze then the problem is that one of the queries is getting planned wrong when stats are available. If it takes a while to slow down then it could be other problems such as index bloat etc. Don't use "explain verbose" use "explain analyze". I don't know if it was in 7.1 but you say you reproduced the problem with 7.4. It would be helpful to see the results of "explain analyze select ..." on the query before and after the vacuum analyze. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CREATE TYPE
Hello i note something related to this discussion > create table mytmp(name myvarchar(10,"en_US")); i meant that "en_US" is a locale name, then it means natural language and also character encoding -- those both things are not a matter of SQL-TYPE at all. It is wrong to represent application specific data in SQL-TYPE itself. ---(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
