Have you tried clustering tables based on the most-frequently used indexes to improve locality? http://www.postgresql.org/docs/8.2/static/sql-cluster.html -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Bill Moran Sent: Wednesday, September 26, 2007 11:24 AM To: James Williams Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: Help tuning a large table off disk and into RAM In response to "James Williams" <james.wlms@xxxxxxxxxxxxxx>: > I'm stuck trying to tune a big-ish postgres db and wondering if anyone > has any pointers. > > I cannot get Postgres to make good use of plenty of available RAM and > stop thrashing the disks. > > One main table. ~30 million rows, 20 columns all integer, smallint or > char(2). Most have an index. It's a table for holding webserver > logs. The main table is all foreign key ids. Row size is ~100bytes. > > The typical query is an aggregate over a large number of rows (~25% say). > > SELECT COUNT(*), COUNT(DISTINCT user_id) > FROM table > WHERE epoch > ... > AND epoch < ... > AND country = ... > > The box has 4 x Opterons, 4Gb RAM & five 15k rpm disks, RAID 5. We > wanted fast query/lookup. We know we can get fast disk IO. > > Running a typical query like above seems to: > > * hardly tax a single CPU > * plenty of RAM free > * disks thrash about > > The last is based mostly on the observation that another tiddly > unrelated mysql db which normally runs fast, grinds to a halt when > we're querying the postgres db (and cpu, memory appear to have spare > capacity). > > We've currently got these settings, and have tried doubling/halving > them, restarted and benchmarked a test query. They don't appear to > materially alter our query time. > > shared_buffers = 128MB shared_buffers = 1.5GB Unless you've got a lot of stuff other than PostgreSQL on this machine. > temp_buffers = 160MB > work_mem = 200MB > max_stack_depth = 7MB These look reasonable, although I can't be sure without more details. > > We're less concerned about insert speed. Typically 1 or 2 users, but > want fast queries. > > Perhaps a little extreme, but I'm trying to find a way to express this > in a way that Postgres understands: > > * Load this table, and one or two indexes (epoch, user_id) into RAM. Give it enough shared_buffers and it will do that. You're estimating the size of your table @ 3G (try a pg_relation_size() on it to get an actual size) If you really want to get _all_ of it in all the time, you're probably going to need to add RAM to the machine. With 8G, you could allocate about 3G to shared_buffers, but that would be ignoring the size of indexes. However, I think you'll be surprised how much performance improves with 1.5G of shared_buffers. You may not need any more. 128M is really forcing PG to work within limited space. > * All of the table, all of those indexes. > * Keep them there, but keep a disk based backup for integrity. > * Run all selects against the in RAM copy. Always. This is what PG does if you allocate enough shared_buffers. -- Bill Moran http://www.potentialtech.com ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ Confidentiality Notice. This message may contain information that is confidential or otherwise protected from disclosure. If you are not the intended recipient, you are hereby notified that any use, disclosure, dissemination, distribution, or copying of this message, or any attachments, is strictly prohibited. If you have received this message in error, please advise the sender by reply e-mail, and delete the message and any attachments. Thank you. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
