On Thu, May 21, 2009 at 1:13 PM, Alex Thurlow <alex@xxxxxxxxxxx> wrote: > I have a postgresql database that I'm using for logging of data. There's > basically one table where each row is a line from my log files. It's > getting to a size where it's running very slow though. There are about 10 > million log lines per day and I keep 30 days of data in it. All the columns > I filter on are indexed (mostly I just use date). **DING DING DING** you've just said the magic phrase that says that partitioning would be a help. > And I tend to pull one > day of data at a time with grouped counts by 1 or 2 other columns. There > also tends to be only 1 or 2 of these large queries running at any given > time, so a lot of resources can be thrown at each one. > > I'm wondering what my resource parameters should be for optimal speed of the > selects on this database, since I haven't seen a good example where someone > has done anything like this. With a logging database you're optimizing two often opposing actions. Lots of small inserts in a stream that HAVE to get processed and put in efficiently. This is often accomplished with minimum shared_buffers and work_mem, because there's no need for the overhead of large shared_buffers and insert queries for logging dbs don't need much work_mem. With a reporting database you run queries that chew up tons of memory both shared_buffers and work_mem for efficient operation. > The machine is an 8 core opteron (I know I won't really use those, but Dell > threw in the 2nd proc for free) with 8 Gb RAM. The database is on a RAID 10 > JFS partition. Yeah CPUs are cheap, might as well stock up on them. A reporting database can quickly go cpu bound if everything the users want to see fits in memory. > This is what I have in postgresql.conf right now.. > > shared_buffers = 64MB Small for reporting, just right for logging. I'd try something bigger but not insanely huge. Let the OS do the caching of 90% of the data, let the db cache a good sized working set. 256M to 1G is reasonable based on benchmarks of your own queries. > work_mem = 128MB Bigger than needed for logging, good for reporting. You can probably just leave it. > maintenance_work_mem = 256MB > max_fsm_pages = 614400 If you're not partitioning then this needs to be big enough to contain 1 days+ worth of dead rows. Look at lowering your random_page_cost, and increasing default stats target to 100 to 1000 depending on your data and explain analyze query testing. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
