Re: Really really slow select count(*)

On Sun, Feb 6, 2011 at 3:48 AM, felix <crucialfelix@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 this memory setting you recommend is not
> possible without reconfiguring the kernel.

SNIP

> and the website is down for the next 6 hours while I sleep.
> total disaster

Let's review:
1: No test or staging system used before production
2: DB left in an unknown state (trying to shut down, not able)
3: No monitoring software to tell you when the site is down
4: I'm gonna just go ahead and guess no backups were taken either, or
are regularly taken.

This website can't be very important, if that's the way you treat it.
Number 1 up there becomes even worse because it was your first time
trying to make this particular change in Postgresql.  If it is
important, you need to learn how to start treating it that way.  Even
the most junior of sys admins or developers I work with know we test
it a couple times outside of production before just trying it there.
And my phone starts complaining a minute after the site stops
responding if something does go wrong the rest of the time.  Do not
lay this at anyone else's feet.

> From my perspective: the defaults for postgres 8.3 result in a database that
> does not scale and fails dramatically after 6 months.

Agreed.  Welcome to using shared memory and the ridiculously low
defaults on most flavors of unix or linux.

>  changing that default
> is brutally difficult and can only really be done by adjusting something in
> the kernel.

Please, that's a gross exaggeration.  The sum totoal to changing them is:

run sysctl -a|grep shm
copy out proper lines to cahnge
edit sysctl.conf
put new lines in there with changes
sudo sysctl -p  # applies changes
edit the appropriate postgresql.conf, make changes
sudo /etc/init.d/postgresql-8.3 stop
sudo /etc/init.d/postgresql-8.3 start

> 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.

> I still don't know if this bloat due to the small free space map has
> anything to do with why the table is performing like this.

Since you haven't show us what changes, if any, have happened to the
table, neither do we :)