On Tue, Feb 08, 2011 at 03:52:31PM -0600, Kevin Grittner wrote:
Scott Marlowe scott.marl...@gmail.com wrote:
Greg Smith g...@2ndquadrant.com 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
/11, Ross J. Reedstrom reeds...@rice.edu wrote:
From: Ross J. Reedstrom reeds...@rice.edu
Subject: Re: [PERFORM] Really really slow select count(*)
big snip
Following you off topic, I know of one admin type who has
stated I don't
care what sort of fine the power company wants to give me
On Tue, Feb 8, 2011 at 3:23 PM, Shaun Thomas stho...@peak6.com 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
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
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 the database
Maciek Sakrejda msakre...@truviso.com 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
On Tue, Feb 8, 2011 at 18:36, Kevin Grittner
kevin.gritt...@wicourts.gov 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
On Tue, Feb 8, 2011 at 9:50 AM, Marti Raudsepp ma...@juffo.org wrote:
On Tue, Feb 8, 2011 at 18:36, Kevin Grittner
kevin.gritt...@wicourts.gov 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
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 indication of
Marti Raudsepp ma...@juffo.org 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
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't
On Tue, Feb 8, 2011 at 22:09, Greg Smith g...@2ndquadrant.com 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
Marti Raudsepp ma...@juffo.org wrote:
Greg Smith g...@2ndquadrant.com 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.
On Tue, Feb 8, 2011 at 1:09 PM, Greg Smith g...@2ndquadrant.com 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
Scott Marlowe scott.marl...@gmail.com wrote:
Greg Smith g...@2ndquadrant.com 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
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
On Tue, Feb 8, 2011 at 3:08 PM, Greg Smith g...@2ndquadrant.com 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
On Mon, Feb 7, 2011 at 05:03, Craig Ringer cr...@postnewspapers.com.au 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
+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 ma...@juffo.org wrote:
On Mon, Feb 7, 2011 at
On Mon, Feb 7, 2011 at 8:05 AM, felix crucialfe...@gmail.com 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,
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
On 02/07/2011 06:30 PM, Marti Raudsepp wrote:
On Mon, Feb 7, 2011 at 05:03, Craig Ringercr...@postnewspapers.com.au 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
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
On Mon, Feb 7, 2011 at 6:05 AM, Shaun Thomas stho...@peak6.com 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
On Mon, Feb 7, 2011 at 8:17 PM, felix crucialfe...@gmail.com wrote:
On Mon, Feb 7, 2011 at 6:05 AM, Shaun Thomas stho...@peak6.com 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
BRUTAL
http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html
max_fsm_pages
See Section
17.4.1http://www.postgresql.org/docs/8.3/interactive/kernel-resources.html#SYSVIPC
for
information on how to adjust those parameters, if necessary.
I see absolutely nothing in there
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
On Sun, Feb 6, 2011 at 3:48 AM, felix crucialfe...@gmail.com 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
On Fri, Feb 4, 2011 at 1:14 PM, felix crucialfe...@gmail.com wrote:
schema_name | table_name | row_count | mb_used |
total_mb_used
-+--+-+-+---
public | django_session |
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
On Sun, Feb 6, 2011 at 12:19 PM, Pierre C li...@peufeu.com 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
On Sun, Feb 6, 2011 at 4:23 PM, Scott Marlowe scott.marl...@gmail.comwrote:
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
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 li...@peufeu.com wrote:
I have
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, but
On Sun, Feb 6, 2011 at 6:52 PM, felix crucialfe...@gmail.com wrote:
On Sun, Feb 6, 2011 at 4:23 PM, Scott Marlowe scott.marl...@gmail.com
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
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
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 shut
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
On Fri, 04 Feb 2011 21:37:56 +0100, Shaun Thomas stho...@peak6.com 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
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(*)
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) (actual
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
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)
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.
sorry, reply was meant to go to the list.
-- Forwarded message --
From: felix crucialfe...@gmail.com
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 stho...@peak6.com
reply was meant for the list
-- Forwarded message --
From: felix crucialfe...@gmail.com
Date: Fri, Feb 4, 2011 at 4:39 PM
Subject: Re: [PERFORM] Really really slow select count(*)
To: Greg Smith g...@2ndquadrant.com
On Fri, Feb 4, 2011 at 3:56 PM, Greg Smith g
On Fri, Feb 04, 2011 at 05:20:27PM +0100, felix wrote:
reply was meant for the list
-- Forwarded message --
From: felix crucialfe...@gmail.com
Date: Fri, Feb 4, 2011 at 4:39 PM
Subject: Re: [PERFORM] Really really slow select count(*)
To: Greg Smith g...@2ndquadrant.com
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
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
On Fri, Feb 4, 2011 at 5:35 PM, Shaun Thomas stho...@peak6.com 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
On Fri, Feb 4, 2011 at 10:38 AM, felix crucialfe...@gmail.com wrote:
On Fri, Feb 4, 2011 at 5:35 PM, Shaun Thomas stho...@peak6.com 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,
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:
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's
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 ?
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
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
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
On Fri, Feb 4, 2011 at 11:38 AM, Greg Smith g...@2ndquadrant.com 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
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
On Fri, Feb 4, 2011 at 7:34 PM, Shaun Thomas stho...@peak6.com 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
On Fri, Feb 4, 2011 at 12:26 PM, felix crucialfe...@gmail.com 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
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
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 scott.marl...@gmail.comwrote:
On Fri, Feb 4, 2011 at 12:26 PM, felix
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
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 ON
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 better
66 matches
Mail list logo
