Speedup Store on Postgres

This might be interesting for everybody running Store on a Postgres DB
under Linux:

After increasing shared memory to 80MB (was: 30MB) and tweaking with
postgresql.conf, my local Store performed like mad. Load times shrunk
dramatically even though it's an ancient 700MHz box with only 256MB RAM.

I changed this in postgresql.conf
max_connections = 8
shared_buffers = 4096
sort_mem = 2048
effective_cache_size = 8192

And added this to /etc/rc.d/rc.local and rebooted
echo 80000000 > /proc/sys/kernel/shmmax

This is for Redhat Linux. Other Unixes might use different means to set
shared memory size. Please consult the postgres configuration docs. The
above settings might not work for all types of system configuration.

On my Store databases, I use extra indexes. Whether they are really used
by the postmaster, I haven't tested. It was a fire and forget solution
and I no longer bothered with it, as it did what I wanted it to do.
Those who are worried about secondary storage footprint might stick to
the middle two indexes only (see comment)

CREATE INDEX tw_blessing_ix1 ON tw_blessing (trace);
CREATE INDEX tw_bundle_ix1 ON tw_bundle (trace);
CREATE INDEX tw_class_ix1 ON tw_classrecord (definitionid);
CREATE INDEX tw_class_ix2 ON tw_classrecord (trace);
CREATE INDEX tw_method_ix1 ON tw_method (trace);

-- these have reportedly the most impact:
CREATE INDEX tw_methods_ix1 ON tw_methods (packageref);
CREATE INDEX tw_methods_ix2 ON tw_methods (methodref);

CREATE INDEX tw_package_ix1 ON tw_package (trace);
CREATE INDEX tw_packages_ix1 ON tw_packages (bundleref);
CREATE INDEX tw_packages_ix2 ON tw_packages (packageref);
CREATE INDEX tw_pkgclasses_ix1 ON tw_pkgclasses (packageref);
CREATE INDEX tw_pkgclasses_ix2 ON tw_pkgclasses (classref);

Maybe this could also speedup the public repository.

Andre

Thanks. The public Store actually has a good few indexes on it already. It has the ones defined in StoreGlorp. That doesn't index trace, but it does index quite a few other things, including names. Most of the link tables have an index on both the "parent" and "child" reference fields, but Postgresql seems to like it if there's also a separate index on the "child".

I don't think we've played around with any of the shared memory or buffer parameters on it, though.

At 05:44 AM 11/8/2007, Andre Schnoor wrote:

This might be interesting for everybody running Store on a Postgres DB under Linux:

After increasing shared memory to 80MB (was: 30MB) and tweaking with postgresql.conf, my local Store performed like mad. Load times shrunk dramatically even though it's an ancient 700MHz box with only 256MB RAM.

I changed this in postgresql.conf
max_connections = 8
shared_buffers = 4096
sort_mem = 2048
effective_cache_size = 8192

And added this to /etc/rc.d/rc.local and rebooted
echo 80000000 > /proc/sys/kernel/shmmax

This is for Redhat Linux. Other Unixes might use different means to set shared memory size. Please consult the postgres configuration docs. The above settings might not work for all types of system configuration.

On my Store databases, I use extra indexes. Whether they are really used by the postmaster, I haven't tested. It was a fire and forget solution and I no longer bothered with it, as it did what I wanted it to do. Those who are worried about secondary storage footprint might stick to the middle two indexes only (see comment)

CREATE INDEX tw_blessing_ix1 ON tw_blessing (trace);
CREATE INDEX tw_bundle_ix1 ON tw_bundle (trace);
CREATE INDEX tw_class_ix1 ON tw_classrecord (definitionid);
CREATE INDEX tw_class_ix2 ON tw_classrecord (trace);
CREATE INDEX tw_method_ix1 ON tw_method (trace);

-- these have reportedly the most impact:
CREATE INDEX tw_methods_ix1 ON tw_methods (packageref);
CREATE INDEX tw_methods_ix2 ON tw_methods (methodref);

CREATE INDEX tw_package_ix1 ON tw_package (trace);
CREATE INDEX tw_packages_ix1 ON tw_packages (bundleref);
CREATE INDEX tw_packages_ix2 ON tw_packages (packageref);
CREATE INDEX tw_pkgclasses_ix1 ON tw_pkgclasses (packageref);
CREATE INDEX tw_pkgclasses_ix2 ON tw_pkgclasses (classref);

Maybe this could also speedup the public repository.

Andre