On Nov 26, 2007 2:48 PM, Bill Moran <wmoran@potentialtech.com> wrote:

In response to "Khalid Baheyeldin" <kb@2bits.com>:

...

On Nov 26, 2007 1:52 PM, Bill Moran <wmoran@potentialtech.com> wrote:

...

My point is: a) Let me send you a tweaked postgresql.conf file and well find out whether the performance difference is simply a matter of untuned configuration and ...

I will post them for all to see, maybe someone will point out something else.

These are the non comment lines in postgresql.conf

hba_file = '/etc/postgresql/8.2/main/pg_hba.conf' ident_file = '/etc/postgresql/8.2/main/pg_ident.conf' external_pid_file = '/var/run/postgresql/8.2-main.pid' listen_addresses = 'localhost' port = 5432 max_connections = 100 unix_socket_directory = '/var/run/postgresql' shared_buffers = 24MB max_fsm_pages = 153600 log_line_prefix = '%t ' stats_row_level = on autovacuum = on datestyle = 'iso, dmy' lc_messages = 'en_CA.UTF-8' lc_monetary = 'en_CA.UTF-8' lc_numeric = 'en_CA.UTF-8' lc_time = 'en_CA.UTF-8'

Oh yeah ... there's lots of room for improvement here.

How much RAM does this system have, and how much of it is "free" memory during the test?

2GB of RAM. Here is the output from free: total used free shared buffers cached Mem: 2060032 1813748 246284 0 576896 439828 -/+ buffers/cache: 797024 1263008 Swap: 3903712 260 3903452 And here is /proc/meminfo MemTotal: 2060032 kB MemFree: 245736 kB Buffers: 577000 kB Cached: 439912 kB SwapCached: 16 kB Active: 777476 kB Inactive: 550212 kB SwapTotal: 3903712 kB SwapFree: 3903452 kB Dirty: 96 kB Writeback: 0 kB AnonPages: 310812 kB Mapped: 32764 kB Slab: 457012 kB SReclaimable: 425856 kB SUnreclaim: 31156 kB PageTables: 9352 kB NFS_Unstable: 0 kB Bounce: 0 kB CommitLimit: 4933728 kB Committed_AS: 526896 kB VmallocTotal: 34359738367 kB VmallocUsed: 2724 kB VmallocChunk: 34359735611 kB We can carve off some memory from cache and buffers (1016 MB combined).

Khalid ... are you willing to work with me to investigate this? If not, I'll try to get my own test rig set up, but it probably won't happen until the new year.

I am willing to, time permitting of course.

Off the top of my head, I'd start out with 2 things: *) Bump shared_buffers to 1/4 the total memory on the system *) "vacuum analyze" the database after the load but before the perf test.

What do you mean after the load? You mean after loading data into the database but before running ab?

I'll look this over a bit and send you a recommended config file later this evening.

Will look forward to it. I guess we can take this conversation off the list too, so as not to spam others. -- Khalid M. Baheyeldin 2bits.com http://2bits.com Drupal optimization, development, customization and consulting.