Thread: Tuning resource parameters for a logging database.
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). 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. 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. This is what I have in postgresql.conf right now.. shared_buffers = 64MB work_mem = 128MB maintenance_work_mem = 256MB max_fsm_pages = 614400 max_fsm_relations = 10000 Can anyone give me some insight as to what I should set these to or if there are others I should be using that I'm missing? Thanks, Alex -- Alex Thurlow Blastro Networks http://www.blastro.com http://www.roxwel.com http://www.yallwire.com
On Thu, May 21, 2009 at 3:13 PM, Alex Thurlow <alex@blastro.com> 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 Are you using partitioning on this table? Your use case is literally the exact example everyone uses to show how to do partitioning on tables. Since you mostly scan on date, this will speed up your queries significantly.
On Thu, May 21, 2009 at 1:13 PM, Alex Thurlow <alex@blastro.com> 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.
I should also specify how my inserts are happening I guess. I'm actually doing the logs to flat files and then inserting them into the database on an hourly basis using COPY, so I don't need to worry as much about the log insert speed as I do the reporting.
On 5/21/2009 2:36 PM, Scott Marlowe wrote:
On 5/21/2009 2:36 PM, Scott Marlowe wrote:
On Thu, May 21, 2009 at 1:13 PM, Alex Thurlow <alex@blastro.com> 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 = 64MBSmall 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 = 128MBBigger than needed for logging, good for reporting. You can probably just leave it.maintenance_work_mem = 256MB max_fsm_pages = 614400If 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.
-- Alex Thurlow Blastro Networks http://www.blastro.com http://www.roxwel.com http://www.yallwire.com
On Thu, May 21, 2009 at 1:36 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > Below, I meant with a logging / reporting database... > 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. > >> work_mem = 128MB > > Bigger than needed for logging, good for reporting. You can probably > just leave it. Note that you can set work_mem per user, so have the reporting users log in with a different user and you can crank this up a bit, say 512M to 1G if you're only ever running 1 or 2 reports. Careful about running the machine out of memory, work_mem is a foot gun if you set it too high and run a lot of queries at once.
On Thu, May 21, 2009 at 1:39 PM, Alex Thurlow <alex@blastro.com> wrote: > I should also specify how my inserts are happening I guess. I'm actually > doing the logs to flat files and then inserting them into the database on an > hourly basis using COPY, so I don't need to worry as much about the log > insert speed as I do the reporting. Cool. Then definitely look at partitioning, and also start running explain analyze on your longer running queries. You'll often find some part of the plan that makes no sense (usually a difference between estimated and actual returned rows is a clue). You can earn a lot of performance by tuning your queries in this way.
I was hoping to not have to change all my code to automate the partitioning table creation stuff, but if that's really the best way, I'll check it out. Thanks for the advice.
On 5/21/2009 2:24 PM, Vick Khera wrote:
Alex Thurlow Blastro Networks http://www.blastro.com http://www.roxwel.com http://www.yallwire.com
On 5/21/2009 2:24 PM, Vick Khera wrote:
On Thu, May 21, 2009 at 3:13 PM, Alex Thurlow <alex@blastro.com> 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 columnsAre you using partitioning on this table? Your use case is literally the exact example everyone uses to show how to do partitioning on tables. Since you mostly scan on date, this will speed up your queries significantly.
On Thu, May 21, 2009 at 3:37 PM, Alex Thurlow <alex@blastronetworks.com> wrote: > I was hoping to not have to change all my code to automate the partitioning > table creation stuff, but if that's really the best way, I'll check it out. > Thanks for the advice. About a 18 months ago we split a large table with 300+ million rows into 100 partitions. The query speed was improved by at least 2 orders of magnitude. Postgres is exceptionally good at dealing with tables in the 10 million row range, and that's what we gave it. Our primary queries on the data were able to go directly to the right partition, but using constraint exclusion was still nearly just as fast. It was totally worth the 10 days or so it took to set up, test (on a replica!) and migrate the data. In your case you could have a natural migration by just adding the child tables and inserting your new data there and deleting old data from your main table. After 30 days, your main table will be empty and you just truncate it, freeing up all the space.
Just wanted to let everyone know that I set up partitioning and got my 30 days of data in. This sucker is FAST. And it wasn't nearly as complicated to make happen as I thought it would be. I didn't use the triggers or rules to make sure data goes in the right table on insert, as there's only one script that inserts, so I just generate the correct table name there.
On 5/22/2009 9:56 AM, Vick Khera wrote:
Alex Thurlow Blastro Networks http://www.blastro.com http://www.roxwel.com http://www.yallwire.com
On 5/22/2009 9:56 AM, Vick Khera wrote:
On Thu, May 21, 2009 at 3:37 PM, Alex Thurlow <alex@blastronetworks.com> wrote:I was hoping to not have to change all my code to automate the partitioning table creation stuff, but if that's really the best way, I'll check it out. Thanks for the advice.About a 18 months ago we split a large table with 300+ million rows into 100 partitions. The query speed was improved by at least 2 orders of magnitude. Postgres is exceptionally good at dealing with tables in the 10 million row range, and that's what we gave it. Our primary queries on the data were able to go directly to the right partition, but using constraint exclusion was still nearly just as fast. It was totally worth the 10 days or so it took to set up, test (on a replica!) and migrate the data. In your case you could have a natural migration by just adding the child tables and inserting your new data there and deleting old data from your main table. After 30 days, your main table will be empty and you just truncate it, freeing up all the space.