Re: Slow Postgresql server

Hi all,

Wow! That's a lot to respond to. Let me go through some of the
ideas... First, I just turned on autovacuum, I forgot to do that. I'm
not seeing a major impact though. Also, I know that it's not optimal
for a dedicated server. It's not just for postgres, it's also got our
apache server on it. We're just getting started and didn't want to
make the major investment right now in getting the most expensive
server we can get. Within the next year, as our traffic grows, we
will most likely upgrade, but for now when we're in the beginning
phases of our project, we're going to work with this server.

In terms of RAID not helping speed-wise (only making an impact in
data integrity) - I was under the impression that even a mirrored
disk set improves speed, because read requests can be sent to either
of the disk controllers. Is this incorrect?

I turned on logging again, only logging queries > 5ms. and it caused
the same problems. I think it might be an issue within the OS's
logging facilities, since it's going through stderr.

Some of the queries are definitely making an impact on the speed. We
are constantly trying to improve performance, and part of that is
reassessing our indexes and denormalizing data where it would help.
We're also doing work with memcached to cache the results of some of
the more expensive operations.

Thanks for all your help guys - it's really fantastic to see the
community here! I've got a lot of database experience (mostly with ms
sql and mysql) but this is my first time doing serious work with
postgres and it's really a great system with great people too.

Jason

On Apr 12, 2007, at 11:35 AM, Jeff Frost wrote:

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