Re: Slow Postgresql server

On Thu, 12 Apr 2007, Jason Lustig wrote:

> 0 <-- BM starts here
> 10 0 180 700436 16420 91740 0 0 0 176 278 2923 59 41 0
> 0 0
> 11 0 180 696736 16420 91740 0 0 0 0 254 2904 57 43 0
> 0 0
> 12 0 180 691272 16420 91740 0 0 0 0 255 3043 60 39 1
> 0 0
> 9 0 180 690396 16420 91740 0 0 0 0 254 3078 63 36 2 0
> 0
>
> Obviously, I've turned off logging now but I'd like to get it running again
> (without bogging down the server) so that I can profile the system and find
> out which queries I need to optimize. My logging settings (with unnecessary
> comments taken out) were:

So what did you get in the logs when you had logging turned on? If you have
the statement logging, perhaps it's worth running through pgfouine to generate
a report.

>
> log_destination = 'syslog' # Valid values are combinations of
> redirect_stderr = off # Enable capturing of stderr into log
> log_min_duration_statement = 0 # -1 is disabled, 0 logs all
> statements
> silent_mode = on # DO NOT USE without syslog or
> log_duration = off
> log_line_prefix = 'user=%u,db=%d' # Special values:
> log_statement = 'none' # none, ddl, mod, all
>

Perhaps you just want to log slow queries > 100ms? But since you don't seem
to know what queries you're running on each web page, I'd suggest you just
turn on the following and run your benchmark against it, then turn it back
off:

log_duration = on
log_statement = 'all'

Then go grab pgfouine and run the report against the logs to see what queries
are chewing up all your time.

> So you know, we're using Postgres 8.2.3. The database currently is pretty
> small (we're just running a testing database right now with a few megabytes
> of data). No doubt some of our queries are slow, but I was concerned because
> no matter how slow the queries were (at most the worst were taking a couple
> of msecs anyway), I was getting ridiculously slow responses from the server.
> Outside of logging, our only other non-default postgresql.conf items are:
>
> shared_buffers = 13000 # min 128kB or max_connections*16kB
> work_mem = 8096 # min 64kB
>
> In terms of the server itself, I think that it uses software raid. How can I
> tell? Our hosting company set it up with the server so I guess I could ask
> them, but is there a program I can run which will tell me the information? I
> also ran bonnie++ and got this output:
>
> Version 1.03 ------Sequential Output------ --Sequential Input-
> --Random-
> -Per Chr- --Block-- -Rewrite- -Per Chr- --Block--
> --Seeks--
> Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec
> %CP
> pgtest 2000M 29277 67 33819 15 15446 4 35144 62 48887 5 152.7 0
> ------Sequential Create------ --------Random
> Create--------
> -Create-- --Read--- -Delete-- -Create-- --Read---
> -Delete--
> files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec
> %CP
> 16 17886 77 +++++ +++ +++++ +++ 23258 99 +++++ +++ +++++
> +++
>
> So I'm getting 33MB and 48MB write/read respectively. Is this slow? Is there
> anything I should be doing to optimize our RAID configuration?
>

It's not fast, but at least it's about the same speed as an average IDE drive
from this era. More disks would help, but since you indicate the DB fits in
RAM with plenty of room to spare, how about you update your
effective_cache_size to something reasonable. You can use the output of the
'free' command and take the cache number and divide by 8 to get a reasonable
value on linux. Then turn on logging and run your benchmark. After that, run
a pgfouine report against the log and post us the explain analyze from your
slow queries.

And if Ron is indeed local, it might be worthwhile to contact him. Someone
onsite would likely get this taken care of much faster than we can on the
mailing list.

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954