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