Re: Really really slow select count(*)
| От | felix |
|---|---|
| Тема | Re: Really really slow select count(*) |
| Дата | |
| Msg-id | AANLkTimnFc-hKG+PFEfwBPLAoK6q9kx=nc_4Fr++C_QB@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Really really slow select count(*) (Scott Marlowe <scott.marlowe@gmail.com>) |
| Ответы |
Re: Really really slow select count(*)
Re: Really really slow select count(*) Re: Really really slow select count(*) Re: Really really slow select count(*) |
| Список | pgsql-performance |
On Sun, Feb 6, 2011 at 4:23 PM, Scott Marlowe <scott.marlowe@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 application load of the production server.
this was changing one configuration parameter. something I was advised to do, read about quite a bit, tested on my development server (mac) and then proceeded to do at 6 am on Sunday morning, our slowest time.
2: DB left in an unknown state (trying to shut down, not able)
what ?
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 ?
I have never restarted postgres before, so this was all new to me. I apologize that I wasn't born innately with such knowledge.
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 ?
3: No monitoring software to tell you when the site is down
of course I have monitoring software. both external and internal. but it doesn't come and kick me out of bed. yes, I need an automated cel phone call. that was the first thing I saw to afterwards.
4: I'm gonna just go ahead and guess no backups were taken either, or
are regularly taken.
WTF ? of course I have backups. I just went through a very harsh down period event. I fail to see why it is now necessary for you to launch such an attack on me.
Perhaps the tone of my post sounded like I was blaming you, or at least you felt that way. Why do you feel that way ?
Why not respond with: "ouch ! did you check this ... that...." say something nice and helpful. correct my mistakes
This website can't be very important, if that's the way you treat it.
just to let you know, that is straight up offensive
This is high traffic real estate site. Downtime is unacceptable. I had less downtime than this when I migrated to the new platform.
I spent rather a large amount of time reading and questioning here. I asked many questions for clarification and didn't do ANYTHING until I was sure it was the correct solution. I didn't just pull some shit off a blog and start changing settings at random.
I double checked opinions against different people and I searched for more docs on that param. Amazingly none of the ones I found commented on the shared memory issue and I didn't even understand the docs discussing shared memory because it didn't seem to apply to what I was doing. that's my misunderstanding. I come her to share my misunderstanding.
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.
I didn't. There is not even the slightest hint of that in my post.
I came here and posted the details of where I went wrong and what confused me about the documentation that I followed. That's so other people can follow it and so somebody here can comment on it.
> changing that defaultPlease, that's a gross exaggeration. The sum totoal to changing them is:
> is brutally difficult and can only really be done by adjusting something in
> the kernel.
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
Considering how splendidly the experiment with changing fsm_max_pages went, I think you can understand that I have no desire to experiment with kernel settings.
It is easy for you because you ALREADY KNOW everything involved. I am not a sysadmin and we don't have one. My apologies for that.
so does the above mean that I don't have to restart the entire server, just postgres ? I assumed that changing kernel settings means rebooting the server.
> 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.
its a different table. the problem one has only 300k rows
the problem is not the size, the problem is the speed is catastrophic
> I still don't know if this bloat due to the small free space map hasSince you haven't show us what changes, if any, have happened to the
> anything to do with why the table is performing like this.
table, neither do we :)
sorry, it didn't seem to be the most important topic when I got out of bed
В списке pgsql-performance по дате отправления: