Re: [PERFORM] Really really slow select count(*)

On Fri, Feb 4, 2011 at 8:46 AM, felix wrote: > > I am having huge performance problems with a table. Performance deteriorates > every day and I have to run REINDEX and ANALYZE on it every day.  auto > vacuum is on.  yes, I am reading the other thread about count(*) :) > but obviously I'm doing som

Re: [PERFORM] Really really slow select count(*)

Lunney --- On Wed, 2/16/11, Ross J. Reedstrom wrote: > From: Ross J. Reedstrom > Subject: Re: [PERFORM] Really really slow select count(*) <> > > Following you off topic, I know of one admin type who has > stated "I don't > care what sort of fine the power

Re: [PERFORM] Really really slow select count(*)

On Tue, Feb 08, 2011 at 03:52:31PM -0600, Kevin Grittner wrote: > Scott Marlowe wrote: > > Greg Smith wrote: > > >> Kevin and I both suggested a "fast plus timeout then immediate" > >> behavior is what many users seem to want. > > > Are there any settings in postgresql.conf that would make it

Re: [PERFORM] Really really slow select count(*)

On Tue, Feb 8, 2011 at 3:23 PM, Shaun Thomas wrote: > > With 300k rows, count(*) isn't a good test, really. That's just on the edge > of big-enough that it could be > 1-second to fetch from the disk controller, > 1 second you say ? excellent, sign me up 70 seconds is way out of bounds I don'

Re: [PERFORM] Really really slow select count(*)

On Tue, Feb 8, 2011 at 3:08 PM, Greg Smith wrote: > Scott Marlowe wrote: >> >> Are there any settings in postgresql.conf that would make it unsafe to >> use -m immediate? >> > > Two concerns: > > -Clients will be killed without any review, and data related to them lost > > -The server will have to

Re: [PERFORM] Really really slow select count(*)

Scott Marlowe wrote: Are there any settings in postgresql.conf that would make it unsafe to use -m immediate? Two concerns: -Clients will be killed without any review, and data related to them lost -The server will have to go through recovery to start back up again, which could potentiall

Re: [PERFORM] Really really slow select count(*)

Scott Marlowe wrote: > Greg Smith wrote: >> Kevin and I both suggested a "fast plus timeout then immediate" >> behavior is what many users seem to want. > Are there any settings in postgresql.conf that would make it > unsafe to use -m immediate? I don't think so. There could definitely be

Re: [PERFORM] Really really slow select count(*)

On Tue, Feb 8, 2011 at 1:09 PM, Greg Smith wrote: > Marti Raudsepp wrote: >> >> I couldn't find any past discussions about changing the default to "fast". >> Are there any reasons why that cannot be done in a future release? >> > Kevin and I both suggested a "fast plus timeout then immediate" beha

Re: [PERFORM] Really really slow select count(*)

Marti Raudsepp wrote: > Greg Smith wrote: >> Kevin and I both suggested a "fast plus timeout then immediate" >> behavior is what many users seem to want. > Seems that a better solution would be implementing a new -m option > that does this transparently? Maybe. Another option might be to use

Re: [PERFORM] Really really slow select count(*)

On Tue, Feb 8, 2011 at 22:09, Greg Smith wrote: > Kevin and I both suggested a "fast plus timeout then immediate" behavior is > what many users seem to want.  My comments were at > http://archives.postgresql.org/pgsql-hackers/2009-09/msg01145.php ; for an > example of how fast shutdown can fail se

Re: [PERFORM] Really really slow select count(*)

Marti Raudsepp wrote: I couldn't find any past discussions about changing the default to "fast". Are there any reasons why that cannot be done in a future release? Well, it won't actually help as much as you might think. It's possible for clients to be in a state where fast shutdown doesn'

Re: [PERFORM] Really really slow select count(*)

Marti Raudsepp wrote: > I couldn't find any past discussions about changing the default to > "fast". It's not entirely unrelated to the "Linux LSB init script" in August and September of 1009: http://archives.postgresql.org/pgsql-hackers/2009-08/msg01843.php http://archives.postgresql.org/

Re: [PERFORM] Really really slow select count(*)

>> I couldn't find any past discussions about changing the default to "fast". >> Are there any reasons why that cannot be done in a future release? > > Or at least throw a hint the user's way that -m fast might be needed. I think there are several issues here: 1. Does pg_ctl give a clear indicati

Re: [PERFORM] Really really slow select count(*)

On Tue, Feb 8, 2011 at 9:50 AM, Marti Raudsepp wrote: > On Tue, Feb 8, 2011 at 18:36, Kevin Grittner > wrote: >> Yeah, current behavior with that shutdown option is the opposite of >> smart for any production environment I've seen.  (I can see where it >> would be handy in development, though.)  

Re: [PERFORM] Really really slow select count(*)

On Tue, Feb 8, 2011 at 18:36, Kevin Grittner wrote: > Yeah, current behavior with that shutdown option is the opposite of > smart for any production environment I've seen.  (I can see where it > would be handy in development, though.)  What's best in production > is the equivalent of the fast opti

Re: [PERFORM] Really really slow select count(*)

Maciek Sakrejda wrote: >>> Well it said "Failed to shutdown ..." and then >>> returned control. and then proceeded to run for about an hour. >>> I'm not sure how graceful that is. >> >> Ah, but that was just the control script that sends the database >> the command to shut down. T

Re: [PERFORM] Really really slow select count(*)

>> Well it said "Failed to shutdown ..." and then >> returned control. and then proceeded to run for about an hour. I'm >> not sure how graceful that is. > > Ah, but that was just the control script that sends the database the command > to shut down. The 'graceful' part, is that th

Re: [PERFORM] Really really slow select count(*)

On 02/07/2011 09:17 PM, felix wrote: Well it said "Failed to shutdown ..." and then returned control. and then proceeded to run for about an hour. I'm not sure how graceful that is. Ah, but that was just the control script that sends the database the command to shut down. The

Re: [PERFORM] Really really slow select count(*)

On Mon, Feb 7, 2011 at 8:17 PM, felix wrote: > > On Mon, Feb 7, 2011 at 6:05 AM, Shaun Thomas wrote: >> >> That’s one of the things I talked about. To be safe, PG will start to shut >> down but disallow new connections, and *that’s all*. Old connections are >> grandfathered in until they disconne

Re: [PERFORM] Really really slow select count(*)

On Mon, Feb 7, 2011 at 6:05 AM, Shaun Thomas wrote: > > That’s one of the things I talked about. To be safe, PG will start to shut > down but disallow new connections, and **that’s all**. Old connections are > grandfathered in until they disconnect, and when they all go away, it shuts > down grac

Re: [PERFORM] Really really slow select count(*)

On 02/08/2011 03:05 AM, Greg Smith wrote: Accordingly I would expect any serious attempt to add some auto-reduction behavior to be beset with argument, and I'd never consider writing such a thing as a result. Too many non-controversial things I could work on instead. Yep. I expressed my own do

Re: [PERFORM] Really really slow select count(*)

On 02/07/2011 06:30 PM, Marti Raudsepp wrote: On Mon, Feb 7, 2011 at 05:03, Craig Ringer wrote: What would possibly help would be if Pg could fall back to lower shared_buffers automatically, screaming about it in the logs but still launching. OTOH, many people don't check the logs, so they'd th

Re: [PERFORM] Really really slow select count(*)

Craig Ringer wrote: What would possibly help would be if Pg could fall back to lower shared_buffers automatically, screaming about it in the logs but still launching. This is exactly what initdb does when it produces an initial setting for shared_buffers that goes into the postgresql.conf file

Re: [PERFORM] Really really slow select count(*)

On Mon, Feb 7, 2011 at 8:05 AM, felix wrote: > +1 > this is exactly what I was looking for at the time:  a -t (configtest) > option to pg_ctl > and I think it should fall back to lower shared buffers and log it. > SHOW ALL; would show the used value however, much like apache, this might not have

Re: [PERFORM] Really really slow select count(*)

+1 this is exactly what I was looking for at the time: a -t (configtest) option to pg_ctl and I think it should fall back to lower shared buffers and log it. SHOW ALL; would show the used value On Mon, Feb 7, 2011 at 11:30 AM, Marti Raudsepp wrote: > On Mon, Feb 7, 2011 at 05:03, Craig Rin

Re: [PERFORM] Really really slow select count(*)

On Mon, Feb 7, 2011 at 05:03, Craig Ringer wrote: > What would possibly help would be if Pg could fall back to lower > shared_buffers automatically, screaming about it in the logs but still > launching. OTOH, many people don't check the logs, so they'd think their > new setting had taken effect an

Re: [PERFORM] Really really slow select count(*)

felix wrote: So is it normal for postgres to report that it failed to shut down, operate for an hour and then go ahead and restart itself ? You've already gotten a few explanations for why waiting for connections can cause this. I'll only add that it is critical to be watching the database l

Re: [PERFORM] Really really slow select count(*)

> I checked the site, everything was normal. I went in via psql and tried some > queries for about half an hour and continued to monitor the site. then I went > to bed at 7am (EU time). > > Why did it shutdown so much later ? That’s one of the things I talked about. To be safe, PG will start to

Re: [PERFORM] Really really slow select count(*)

You really got screwed by the default settings. You don’t actually need to “hack” the kernel, but you do have to make these changes, because the amount of memory PG has on your system is laughable. That might actually be the majority of your problem. In your /etc/sysctl.conf, you need these lin

Re: [PERFORM] Really really slow select count(*)

On Sun, Feb 6, 2011 at 6:52 PM, felix wrote: > On Sun, Feb 6, 2011 at 4:23 PM, Scott Marlowe > wrote: >> >> Let's review: >> >> 1: No test or staging system used before production > > no, I do not have a full ubuntu machine replicating the exact memory and > application load of the production ser

Re: [PERFORM] Really really slow select count(*)

On 07/02/11 09:52, felix wrote: > So is it normal for postgres to report that it failed to shut down, > operate for an hour and then go ahead and restart itself ? That's pretty wacky. Did you shut it down via pg_ctl or using an init script / "service" command in your OS? It shouldn't matter, bu

Re: [PERFORM] Really really slow select count(*)

yeah, it already uses memcached with db save. nothing important in session anyway the session table is not the issue and I never clustered that one or ever will thanks for the tip, also the other one about HOT On Sun, Feb 6, 2011 at 8:19 PM, Pierre C wrote: > > I have clustered that table,

Re: [PERFORM] Really really slow select count(*)

On Sun, Feb 6, 2011 at 4:23 PM, Scott Marlowe wrote: > Let's review: > 1: No test or staging system used before production > no, I do not have a full ubuntu machine replicating the exact memory and application load of the production server. this was changing one configuration parameter. someth

Re: [PERFORM] Really really slow select count(*)

On Sun, Feb 6, 2011 at 12:19 PM, Pierre C wrote: > >>> I have clustered that table, its still unbelievably slow. >> >> Did you actually delete the old entries before clustering it?  if it's >> still got 4G of old sessions or whatever in it, clustering ain't gonna >> help. > > Also, IMHO it is a lo

Re: [PERFORM] Really really slow select count(*)

I have clustered that table, its still unbelievably slow. Did you actually delete the old entries before clustering it? if it's still got 4G of old sessions or whatever in it, clustering ain't gonna help. Also, IMHO it is a lot better to store sessions in something like memcached, rather

Re: [PERFORM] Really really slow select count(*)

On Fri, Feb 4, 2011 at 1:14 PM, felix wrote: >  schema_name |            table_name            |  row_count  | mb_used | > total_mb_used > -+--+-+-+--- >  public      | django_session                   | 1.47843e+07 |    4

Re: [PERFORM] Really really slow select count(*)

On Sun, Feb 6, 2011 at 3:48 AM, felix wrote: > BRUTAL > SNIP > OK, my mistake.   probably I have to disconnect all clients.  I don't want > to do a "planned maintenance" right now. > so I go to sleep > the server restarts itself an hour later. > but no, it fails to restart because this memory set

Re: [PERFORM] Really really slow select count(*)

On Sun, Feb 06, 2011 at 11:48:50AM +0100, felix wrote: > BRUTAL > Did the changes work in your test environment? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Really really slow select count(*)

BRUTAL http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html max_fsm_pages See Section 17.4.1 for information on how to adjust those parameters, if necessary. I see absolutely nothing in there a

Re: [PERFORM] Really really slow select count(*)

On Fri, 04 Feb 2011 21:37:56 +0100, Shaun Thomas wrote: On 02/04/2011 02:14 PM, felix wrote: oh and there in the footnotes to django they say "dont' forget to run the delete expired sessions management every once in a while". thanks guys. Oh Django... :) it won't run now because its too b

Re: [PERFORM] Really really slow select count(*)

On 02/04/2011 02:14 PM, felix wrote: oh and there in the footnotes to django they say "dont' forget to run the delete expired sessions management every once in a while". thanks guys. Oh Django... :) it won't run now because its too big, I can delete them from psql though You might be bette

Re: [PERFORM] Really really slow select count(*)

it probably has good reason to hate me. ns=> SELECT n.nspname AS schema_name, c.relname AS table_name, ns-> c.reltuples AS row_count, ns-> c.relpages*8/1024 AS mb_used, ns-> pg_total_relation_size(c.oid)/1024/1024 AS total_mb_used ns-> FROM pg_class c ns-> JOIN pg_namespace n

Re: [PERFORM] Really really slow select count(*)

On 02/04/2011 01:59 PM, felix wrote: still no advice on the pages I think it just hates you. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_dis

Re: [PERFORM] Really really slow select count(*)

ah right, duh. yes, I did it as -U postgres, verified as a superuser just now did it from inside psql as postgres \c djns4 vacuum verbose analyze; still no advice on the pages On Fri, Feb 4, 2011 at 8:34 PM, Scott Marlowe wrote: > On Fri, Feb 4, 2011 at 12:26 PM, felix wrote: > > I just sud

Re: [PERFORM] Really really slow select count(*)

On 02/04/2011 01:26 PM, felix wrote: because I asked it to: -W on the production server I need to enter password and I'm testing on dev first. Right. I'm just surprised it threw up the prompt so many times. I just sudo tried it but still no report Nono... you have to run the vacuum command

Re: [PERFORM] Really really slow select count(*)

On Fri, Feb 4, 2011 at 12:26 PM, felix wrote: > I just sudo tried it but still no report It's not about who you are in Unix / Linux, it's about who you are in Postgresql. \du will show you who is a superusr. psql -U username will let you connect as that user. -- Sent via pgsql-performance mai

Re: [PERFORM] Really really slow select count(*)

On Fri, Feb 4, 2011 at 7:34 PM, Shaun Thomas wrote: > Why is it asking for the password over and over again? It shouldn't be > doing that. > because I asked it to: -W on the production server I need to enter password and I'm testing on dev first. I just sudo tried it but still no report and

Re: [PERFORM] Really really slow select count(*)

On 02/04/2011 01:01 PM, Scott Marlowe wrote: begin; select * into temporaryholdingtable order by somefield; truncate oldtable; insert into oldtables select * from temporaryholdingtable; commit; That's usually how I do it, except for larger tables, I also throw in a DROP INDEX for all the inde

Re: [PERFORM] Really really slow select count(*)

On Fri, Feb 4, 2011 at 11:38 AM, Greg Smith wrote: > You don't turn it on; it's a one time operation that does a cleanup.  It is > by far the easiest way to clean up the mess you have right now.  Moving > forward, if you have max_fsm_pages set to an appropriate number, you > shouldn't end up back

Re: [PERFORM] Really really slow select count(*)

felix wrote: and do you agree that I should turn CLUSTER ON ? I have no problem to stop all tasks to this table at night and just reload it You don't turn it on; it's a one time operation that does a cleanup. It is by far the easiest way to clean up the mess you have right now. Moving forw

Re: [PERFORM] Really really slow select count(*)

On 02/04/2011 12:14 PM, felix wrote: do you think its possible that it just doesn't have anything to complain about ? or the password is affecting it ? Why is it asking for the password over and over again? It shouldn't be doing that. And also, are you running this as a user with superuser p

Re: [PERFORM] Really really slow select count(*)

You can run vacuum verbose on just the postgres database and get the global numbers at the end. gotta be a superuser as well. # \c postgres postgres postgres=# vacuum verbose; lots deleted. DETAIL: A total of 7664 page slots are in use (including overhead). 7664 page slots are required to t

Re: [PERFORM] Really really slow select count(*)

vacuumdb -a -v -z -U postgres -W &> vacuum.log Password: Password: Password: Password: Password: Password: Password: Password: Password: Password: Password: cruxnu:nsbuildout crucial$ do you think its possible that it just doesn't have anything to complain about ? or the password is affecting it ?

Re: [PERFORM] Really really slow select count(*)

On 02/04/2011 11:38 AM, felix wrote: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.fastadder_fastadderstatus" INFO: "fastadder_fastadderstatus": scanned 2492 of 2492 pages, containing 154378 live rows and 0 dead rows; 3 rows in sample, 154378 estimated total rows and there'

Re: [PERFORM] Really really slow select count(*)

vacuumdb -a -v -z -U postgres -W &> vacuum.log that's all, isn't it ? it did each db 8.3 in case that matters the very end: There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.seo_partnerlinkcategory" INFO: "seo_partner

Re: [PERFORM] Really really slow select count(*)

On Fri, Feb 4, 2011 at 10:38 AM, felix wrote: > > > On Fri, Feb 4, 2011 at 5:35 PM, Shaun Thomas wrote: >> >> >> vacuumdb -a -v -z &>vacuum.log >> >> And at the end of the log, it'll tell you how many pages it wants, and how >> many pages were available. > > this is the dev, not live. but this is

Re: [PERFORM] Really really slow select count(*)

On Fri, Feb 4, 2011 at 5:35 PM, Shaun Thomas wrote: > > > vacuumdb -a -v -z &>vacuum.log > > And at the end of the log, it'll tell you how many pages it wants, and how > many pages were available. > this is the dev, not live. but this is after it gets done with that table: CPU 0.00s/0.00u sec e

Re: [PERFORM] Really really slow select count(*)

On 02/04/2011 10:03 AM, felix wrote: max_fsm_pages | 153600 | Sets the maximum number of disk pages for which free space is tracked. max_fsm_relations | 1000 | Sets the maximum number of tables and indexes for which free space is trac

Re: [PERFORM] Really really slow select count(*)

On 02/04/2011 10:17 AM, felix wrote: > How big is this table when it's acting all bloated and ugly? 458MB Wow! There's no way a table with 300k records should be that big unless it's just full of text. 70-seconds seems like a really long time to read half a gig, but that might be because it

Re: [PERFORM] Really really slow select count(*)

On Fri, Feb 04, 2011 at 05:20:27PM +0100, felix wrote: > reply was meant for the list > > -- Forwarded message -- > From: felix > Date: Fri, Feb 4, 2011 at 4:39 PM > Subject: Re: [PERFORM] Really really slow select count(*) > To: Greg Smith > > >

[PERFORM] Really really slow select count(*)

reply was meant for the list -- Forwarded message -- From: felix Date: Fri, Feb 4, 2011 at 4:39 PM Subject: Re: [PERFORM] Really really slow select count(*) To: Greg Smith On Fri, Feb 4, 2011 at 3:56 PM, Greg Smith wrote: > PostgreSQL version? If you're running o

Fwd: [PERFORM] Really really slow select count(*)

sorry, reply was meant to go to the list. -- Forwarded message -- From: felix Date: Fri, Feb 4, 2011 at 5:17 PM Subject: Re: [PERFORM] Really really slow select count(*) To: stho...@peak6.com On Fri, Feb 4, 2011 at 4:00 PM, Shaun Thomas wrote: > How big is this table w

Re: [PERFORM] Really really slow select count(*)

On 02/04/2011 08:56 AM, Greg Smith wrote: PostgreSQL version? If you're running on 8.3 or earlier, I would be suspicous that your Free Space Map has been overrun. That's my first inclination. If he says autovacuum is running, there's no way it should be bloating the table that much. Felix,

Re: [PERFORM] Really really slow select count(*)

On 02/04/2011 08:46 AM, felix wrote: explain analyze select count(*) from fastadder_fastadderstatus; Aggregate (cost=62458.73..62458.74 rows=1 width=0) (actual time=77130.000..77130.000 rows=1 loops=1) -> Seq Scan on fastadder_fastadderstatus (cost=0.00..61701.18 rows=303018 width=0) (ac

Re: [PERFORM] Really really slow select count(*)

felix wrote: explain analyze select count(*) from fastadder_fastadderstatus; Aggregate (cost=62458.73..62458.74 rows=1 width=0) (actual time=77130.000..77130.000 rows=1 loops=1) -> Seq Scan on fastadder_fastadderstatus (cost=0.00..61701.18 rows=303018 width=0) (actual time=50.000..76930.

Re: [PERFORM] Really really slow select count(*)

On Fri, Feb 04, 2011 at 03:46:35PM +0100, felix wrote: > directly after REINDEX and ANALYZE: > > Aggregate (cost=62348.70..62348.71 rows=1 width=0) (actual > time=15830.000..15830.000 rows=1 loops=1) >-> Seq Scan on fastadder_fastadderstatus (cost=0.00..61613.16 > rows=294216 width=0) (act

[PERFORM] Really really slow select count(*)

I am having huge performance problems with a table. Performance deteriorates every day and I have to run REINDEX and ANALYZE on it every day. auto vacuum is on. yes, I am reading the other thread about count(*) :) but obviously I'm doing something wrong here explain analyze select count(*) fro