Re: Slow Postgresql server - Mailing list pgsql-performance

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@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954