On Wed, Sep 06, 2006 at 04:33:27PM -0500, Frank Wiles wrote: > On Wed, 06 Sep 2006 14:02:56 -0400 > Perrin Harkins <[EMAIL PROTECTED]> wrote: > > > You can usually increase your performance greatly just by tuning your > > existing SQL and database. Run Apache::DProf or the DBI profiler, > > find out where the time is being spent, and work on it. There are > > many resources for database performance tuning. Work on the actual > > queries and schema structure, not on the database configuration. You > > always get more from the former than the latter. > > Agree with everything else you said, but have to disagree with the > last statement. Specifically with PostgreSQL the default server > configuration is a really low end config, setup to basically run > on any old hardware you have around. > > Configuring it for your particular hardware ( memory size > specifically ) can reap HUGE performance gains. In case anyone > is wondering the two most useful tweaks are shared_buffers > and effective_cache_size which need to be increased on all but > the lowliest of systems. > > PostgreSQL may be the only one where this is true however... >
Most databases install with setting to run on the minimum hardware spec required. A good DBA tunes the database so that it can internally cache as much as possible while preventing the process from ever having to swap out to disc. There are basically three areas of memory usage that need to be balanced. Sort area - how much memory the DB uses to process sort/joins etcs. Data cache - how much memory the DB uses to prefetch/cache data Query/Compile area - how much area the DB uses to compile and execute queries/stored proceedures. If the Sort area is too small the DB has to do the sort on the disc (v.slow) If the Data cache is too small then too much disc IO is required (v.slow) If the Query/Compile area is too small common queries have to be reanalyised and compiled each time. Some DBs dynamically mange these settings them selves other require them to be tweeked but all require that you specify how much memory the DB is allowed to use. This depends on the DB, your server and whether it is dedicated or not. The next useful setting relates to concurrency. A 64 processor machine will do a lot more in parallel than a single CPU box but most default settings are based on the assumption you have a single CPU. Some databases have significant connect setup overheads. Apache::DBI's ability to have persistant connections save you from these each time. Hope it helps Paddy
