Re: [PERFORM] Unexpected pgbench result
On 12/19/2013 04:06 PM, Dave Johansen wrote: Right now, we're running a RAID 1 for pg_clog, pg_log and pg_xlog and then a RAID 1+0 with 12 disks for the data. Would there be any benefit to running a separate RAID 1+0 with a tablespace for the indexes? Not really. PostgreSQL doesn't currently support parallel backend fetches, aggregation, or really anything. It's looking like 9.4 will get us a lot closer to that, but right now, everything is serial. Serial or not, separate backends will have separate read concerns, and PostgreSQL 9.2 and above *do* support index only scans. So theoretically, you might actually see some benefit there. If it were me and I had spindles available, I would just increase the overall size of the pool. It's a lot easier than managing multiple tablespaces. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Regarding Hardware Tuning
On 12/19/2013 06:37 PM, Scott Marlowe wrote: The ebook edition is on sale for $5.00 which is a STEAL. Wow, I guess I should pay better attention to all those annoying emails Packt sends me. That'll make a good portable copy since I tend to keep the real version on my bookshelf at home. :) This is good advice, by the way. Greg's book is great, especially for newly minted DBAs who might have trouble deciding on where to start. Though from what I've been seeing on the list recently, they need to have him update it for 9.2 and 9.3 with all of the changes in the last couple versions. There are also a ton of considerations regarding new Linux kernel settings. Greg, go tell Packt they need to pay you to write the second edition. ;) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow query - will CLUSTER help?
On 12/19/2013 03:24 PM, Sergey Konoplev wrote: 2. You are limited with IO I would also suggest you to upgrade your storage in this case. I think this is the case. If I recall correctly, his setup includes a single RAID-1 for everything, and he only has 32GB of RAM. In fact, the WAL traffic from those inserts alone are likely saturating the write IO, especially if it starts a checkpoint while the load is still going on. I wouldn't want to be around for that. Even with a fairly selective index, just the fetches necessary to identify the rows and verify the data pages will choke a RAID-1 with almost every query. Any table with several hundred million rows is also too big to fit in cache if any significant portion of it is fetched on a regular basis. The cache turnover is probably extremely high, too. That workload is just too high for a system of that description. It would be fine for a prototype, development, or possibly a QA system, but if that's intended to be a production resource, it needs more memory and IO. Also since I can't see part of this conversation and it doesn't seem anyone else mentioned it, the WAL directory *must* be moved to a separate set of disks for a workload of this volume. The amount of writes here will constantly degrade read IO and further increase fetch times. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Unexpected pgbench result
On Fri, Dec 20, 2013 at 7:10 AM, Shaun Thomas stho...@optionshouse.comwrote: On 12/19/2013 04:06 PM, Dave Johansen wrote: Right now, we're running a RAID 1 for pg_clog, pg_log and pg_xlog and then a RAID 1+0 with 12 disks for the data. Would there be any benefit to running a separate RAID 1+0 with a tablespace for the indexes? Not really. PostgreSQL doesn't currently support parallel backend fetches, aggregation, or really anything. It's looking like 9.4 will get us a lot closer to that, but right now, everything is serial. Serial or not, separate backends will have separate read concerns, and PostgreSQL 9.2 and above *do* support index only scans. So theoretically, you might actually see some benefit there. If it were me and I had spindles available, I would just increase the overall size of the pool. It's a lot easier than managing multiple tablespaces. Ok, that makes sense. Is there a benefit to having the WAL and logs on the separate RAID 1? Or is just having them be part of the larger RAID 1+0 just as good?
Re: [PERFORM] Unexpected pgbench result
Dave Johansen davejohan...@gmail.com wrote: Is there a benefit to having the WAL and logs on the separate RAID 1? Or is just having them be part of the larger RAID 1+0 just as good? I once accidentally left the pg_xlog directory on the 40-spindle RAID with most of the data instead of moving it. Results with graph here: http://www.postgresql.org/message-id/4b71358e02250002f...@gw.wicourts.gov -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Unexpected pgbench result
On Fri, Dec 20, 2013 at 8:22 AM, Kevin Grittner kgri...@ymail.com wrote: Dave Johansen davejohan...@gmail.com wrote: Is there a benefit to having the WAL and logs on the separate RAID 1? Or is just having them be part of the larger RAID 1+0 just as good? I once accidentally left the pg_xlog directory on the 40-spindle RAID with most of the data instead of moving it. Results with graph here: http://www.postgresql.org/message-id/4b71358e02250002f...@gw.wicourts.gov That's very helpful information. Thanks for sharing it, Dave
Re: [PERFORM] Recommendations for partitioning?
On Thu, Dec 19, 2013 at 10:27 AM, Scott Marlowe scott.marl...@gmail.comwrote: On Thu, Dec 19, 2013 at 9:53 AM, Dave Johansen davejohan...@gmail.com wrote: On Sat, Dec 7, 2013 at 1:37 PM, Scott Marlowe scott.marl...@gmail.com wrote: I'll add that you can use assymetric partitioning if you tend to do a lot of more fine grained queries on recent data and more big roll up on older ones. I.e. partition by month except for the last 30 days, do it by day etc. Then at the end of the month roll all the days into a month partition and delete them. This sounds like a great solution for us. Is there some trick to roll the records from one partition to another? Or is the only way just a SELECT INTO followed by a DELETE? That's pretty much it. What I did was to create the new month table and day tables, alter my triggers to reflect this, then move the data with insert into / select from query for each old day partition. Then once their data is moved you can just drop them. Since you changed the triggers first those tables are no long taking input so it's usually safe to drop them now. It would be nice if there was just a move command, but that seems like the type of model that we want and we'll probably move to that. On a semi-related note, I was trying to move from the single large table to the partitions and doing INSERT INTO SELECT * FROM WHERE ... was running very slow (I believe because of the same index issue that we've been running into), so then I tried creating a BEFORE INSERT trigger that was working and using pg_restore on an -Fc dump. The documentation says that triggers are executed as part of a COPY FROM ( http://www.postgresql.org/docs/8.4/static/sql-copy.html ), but it doesn't appear that the trigger was honored because all of the data was put into the base table and all of the partitions are empty. Is there a way that I can run pg_restore that will properly honor the trigger? Or do I just have to create a new INSERTs dump? Thanks, Dave
Re: [PERFORM] Recommendations for partitioning?
On Fri, Dec 20, 2013 at 7:52 AM, Dave Johansen davejohan...@gmail.comwrote: It would be nice if there was just a move command, but that seems like the type of model that we want and we'll probably move to that. I haven't been following this thread, but this comment caught my eye. Are you after the NO INHERIT command? http://www.postgresql.org/docs/current/static/sql-altertable.html Search for the NO INHERIT clause -- it will allow you to detach a child table from an inherited parent which can then archive or copy into another table. Inserting into the rolled-up partition was already mentioned upthread I see.
Re: [PERFORM] slow query - will CLUSTER help?
On 12/20/2013 09:57 AM, Sev Zaslavsky wrote: There is a separate RAID-1 for WAL, another for tablespace and another for operating system. I tend to stick to DB-size / 10 as a minimum, but I also have an OLTP system. For a more OLAP-type, the ratio is negotiable. The easiest way to tell is to monitor your disk IO stats. If you're seeing a READ-based utilization percentage over 50% consistently, you need more RAM. On our system, we average 10% through the day except for maintenance and loading phases. Of course, that's only for the current DB size. A good trick is to monitor your DB size changes on a daily basis, plot the growth percentage for a week, and apply compounding growth to estimate the size in three years. Most companies I've seen are on a 3-year replacement cycle, so that gives you how much you'll have to buy in order to avoid another spend until the next iteration. For example, say you have a 800GB database, and it grows at 10GB per week, so that's 40GB per month. In three years, you could need up to: 800 * (1 + 40/800)^36 = 4632GB of space, which translates to roughly 480-512 GB of RAM. You can probably find a comfortable middle ground with 240GB. Of course, don't forget to buy modules in multiples of four, otherwise you're not taking advantage of all the CPU's memory channels. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Recommendations for partitioning?
Dave Johansen escribió: On Thu, Dec 19, 2013 at 10:27 AM, Scott Marlowe scott.marl...@gmail.comwrote: That's pretty much it. What I did was to create the new month table and day tables, alter my triggers to reflect this, then move the data with insert into / select from query for each old day partition. Then once their data is moved you can just drop them. Since you changed the triggers first those tables are no long taking input so it's usually safe to drop them now. It would be nice if there was just a move command, but that seems like the type of model that we want and we'll probably move to that. Eh. Why can't you just do something like WITH moved AS ( DELETE FROM src WHERE .. RETURNING * ) INSERT INTO dst SELECT * FROM moved; -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Recommendations for partitioning?
On 12/20/2013 09:59 AM, Alvaro Herrera wrote: WITH moved AS ( DELETE FROM src WHERE .. RETURNING * ) INSERT INTO dst SELECT * FROM moved; I know that's effectively an atomic action, but I'd feel a lot more comfortable reversing that logic so the delete is based on the results of the insert. WITH saved AS ( INSERT INTO dst SELECT * FROM src WHERE ... RETURNING * ) DELETE FROM src WHERE ...; I'll admit yours is cleaner, though. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Recommendations for partitioning?
On Fri, Dec 20, 2013 at 9:18 AM, Shaun Thomas stho...@optionshouse.comwrote: On 12/20/2013 09:59 AM, Alvaro Herrera wrote: WITH moved AS ( DELETE FROM src WHERE .. RETURNING * ) INSERT INTO dst SELECT * FROM moved; I know that's effectively an atomic action, but I'd feel a lot more comfortable reversing that logic so the delete is based on the results of the insert. WITH saved AS ( INSERT INTO dst SELECT * FROM src WHERE ... RETURNING * ) DELETE FROM src WHERE ...; I'll admit yours is cleaner, though. :) That is a good idea. I didn't even realize that there was such a command, so I'll definitely use those.
Re: [PERFORM] Recommendations for partitioning?
On Fri, Dec 20, 2013 at 8:52 AM, Dave Johansen davejohan...@gmail.comwrote: On Thu, Dec 19, 2013 at 10:27 AM, Scott Marlowe scott.marl...@gmail.comwrote: On Thu, Dec 19, 2013 at 9:53 AM, Dave Johansen davejohan...@gmail.com wrote: On Sat, Dec 7, 2013 at 1:37 PM, Scott Marlowe scott.marl...@gmail.com wrote: I'll add that you can use assymetric partitioning if you tend to do a lot of more fine grained queries on recent data and more big roll up on older ones. I.e. partition by month except for the last 30 days, do it by day etc. Then at the end of the month roll all the days into a month partition and delete them. This sounds like a great solution for us. Is there some trick to roll the records from one partition to another? Or is the only way just a SELECT INTO followed by a DELETE? That's pretty much it. What I did was to create the new month table and day tables, alter my triggers to reflect this, then move the data with insert into / select from query for each old day partition. Then once their data is moved you can just drop them. Since you changed the triggers first those tables are no long taking input so it's usually safe to drop them now. It would be nice if there was just a move command, but that seems like the type of model that we want and we'll probably move to that. On a semi-related note, I was trying to move from the single large table to the partitions and doing INSERT INTO SELECT * FROM WHERE ... was running very slow (I believe because of the same index issue that we've been running into), so then I tried creating a BEFORE INSERT trigger that was working and using pg_restore on an -Fc dump. The documentation says that triggers are executed as part of a COPY FROM ( http://www.postgresql.org/docs/8.4/static/sql-copy.html ), but it doesn't appear that the trigger was honored because all of the data was put into the base table and all of the partitions are empty. Is there a way that I can run pg_restore that will properly honor the trigger? Or do I just have to create a new INSERTs dump? It turns out that this was an error on my part. I was using an old script to do the restore and it had --disable-triggers to prevent the foreign keys from being checked and that was the actual source of my problem.
Re: [PERFORM] slow query - will CLUSTER help?
On 21/12/13 05:11, Shaun Thomas wrote: [...] . Of course, don't forget to buy modules in multiples of four, otherwise you're not taking advantage of all the CPU's memory channels. :) Note some processors have 3 (three) memory channels! And I know of some with 4 memory channels. So it is important to check your processor mother board. The desktop I got when I joined a university on contract had 12GB about 2 years ago. Cheers, Gavin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] DATE_TRUNC() and GROUP BY?
On Fri, Dec 20, 2013 at 1:35 PM, Dave Johansen davejohan...@gmail.comwrote:
I just ran into an interesting issue on Postgres 8.4. I have a database
with about 3 months of data and when I do following query:
SELECT DATE_TRUNC('day', time) AS time_t, COUNT(*) FROM mytable GROUP BY
time_t;
EXPLAIN shows that it's doing a sort and then a GroupAggregate. There will
only be ~90 outputs, so is there a way I can hint/force the planner to just
do a HashAggregate?
Just to see if it would change the plan, I tried increasing the work_mem
up to 1GB and it still did the same plan.
PostgreSQL does not really have any stats on the selectivity of
date_trunc('day', time) so my guess is that it can only assume that it has
the same selectivity as the time column by itself... Which is very untrue
in this case.
The group aggregate plan is chosen here as PostgreSQL thinks the the hash
table is going to end up pretty big and decides that the group aggregate
will be the cheaper option.
I mocked up your data and on 9.4 I can get the hash aggregate plan to run
if I set the n_distinct value to 90 then analyze the table again.. Even if
you could do this on 8.4 I'd not recommend it as it will probably cause
havoc with other plans around the time column. I did also get the hash
aggregate plan to run if I created a functional index on date_trunc('day',
time) then ran analyze again. I don't have a copy of 8.4 around to see if
the planner will make use of the index in the same way.
What would be really nice is if we could create our own statistics on what
we want, something like:
CREATE STATISTICS name ON table (date_trunc('day', time));
That way postgres could have a better idea of the selectivity in this
situation.
I'd give creating the function index a try, but keep in mind the overhead
that it will cause with inserts, updates and deletes.
Regards
David Rowley
Thanks,
Dave
