Re: [PERFORM] Massive I/O spikes during checkpoint
On Jul 9, 2012, at 10:51 PM, Maxim Boguk wrote: But what appears to be happening is that all of the data is being written out at the end of the checkpoint. This happens at every checkpoint while the system is under load. I get the feeling that this isn't the correct behavior and i've done something wrong. It's not an actual checkpoints. It's is a fsync after checkpoint which create write spikes hurting server. You should set sysctl vm.dirty_background_bytes and vm.dirty_bytes to reasonable low values So use bla_bytes instead of bla_ratio? (for 512MB raid controller with cache I would suggest to sometning like vm.dirty_background_bytes = 33554432 vm.dirty_bytes = 268435456 32MB and 256MB respectively) I'll take a look. If youre server doesn't have raid with BBU cache - then you should tune these values to much lower values. Please read http://blog.2ndquadrant.com/tuning_linux_for_low_postgresq/ and related posts. yeah, I saw that I guess I didn't put 2+2 together. thanks.
Re: [PERFORM] Massive I/O spikes during checkpoint
On Tue, Jul 10, 2012 at 4:03 PM, David Kerr d...@mr-paradox.net wrote: On Jul 9, 2012, at 10:51 PM, Maxim Boguk wrote: But what appears to be happening is that all of the data is being written out at the end of the checkpoint. This happens at every checkpoint while the system is under load. I get the feeling that this isn't the correct behavior and i've done something wrong. It's not an actual checkpoints. It's is a fsync after checkpoint which create write spikes hurting server. You should set sysctl vm.dirty_background_bytes and vm.dirty_bytes to reasonable low values So use bla_bytes instead of bla_ratio? Yes because on 256GB server echo 10 /proc/sys/vm/dirty_ratio is equivalent to 26Gb dirty_bytes and echo 5 /proc/sys/vm/dirty_background_ratio is equivalent to 13Gb dirty_background_bytes It is really huge values. So kernel doesn't start write any pages out in background before it has at least 13Gb dirty pages in kernel memory. And at end of the checkpoint kernel trying flush all dirty pages to disk. Even echo 1 /proc/sys/vm/dirty_background_ratio is too high value for contemporary server. That is why *_bytes controls added to kernel. -- Maxim Boguk Senior Postgresql DBA http://www.postgresql-consulting.com/
Re: [PERFORM] Massive I/O spikes during checkpoint
On 7/9/2012 11:14 PM, Maxim Boguk wrote: On Tue, Jul 10, 2012 at 4:03 PM, David Kerr d...@mr-paradox.net mailto:d...@mr-paradox.net wrote: On Jul 9, 2012, at 10:51 PM, Maxim Boguk wrote: But what appears to be happening is that all of the data is being written out at the end of the checkpoint. This happens at every checkpoint while the system is under load. I get the feeling that this isn't the correct behavior and i've done something wrong. It's not an actual checkpoints. It's is a fsync after checkpoint which create write spikes hurting server. You should set sysctl vm.dirty_background_bytes and vm.dirty_bytes to reasonable low values So use bla_bytes instead of bla_ratio? Yes because on 256GB server echo 10 /proc/sys/vm/dirty_ratio is equivalent to 26Gb dirty_bytes and echo 5 /proc/sys/vm/dirty_background_ratio is equivalent to 13Gb dirty_background_bytes It is really huge values. sigh yeah, I never bothered to think that through. So kernel doesn't start write any pages out in background before it has at least 13Gb dirty pages in kernel memory. And at end of the checkpoint kernel trying flush all dirty pages to disk. Even echo 1 /proc/sys/vm/dirty_background_ratio is too high value for contemporary server. That is why *_bytes controls added to kernel. Awesome, Thanks. -- 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] Create tables performance
Yes, you're right ! The process checks if all these tables exist before creating them. So it might be the SELECT that takes time. To check existence, I use the following query : select * from pg_tables where tablename='the_table'; May be it's not the best way. And I launch a query per table ! Not good at all. Thank you all, I will optimize this. Sylvain - Mail original - On Sun, Jul 8, 2012 at 11:49 PM, Sylvain CAILLET scail...@alaloop.com wrote: Hi, Thank you all for your help. @Jeff : my daemon creates these tables at start time so it doesn't do anything else at the same time. The CPU is loaded between 20% and 25%. How does it decide which tables to create? Is it querying the existing tables to figure out what new ones to make? Is the rest of the time going to IO wait? Cheers, Jeff
[PERFORM] Custom function in where clause
Hi, I have searched solution to my problem a few days. On my query, there is big performance problem. It seems to me, that problem is on where-part of sql and it's function. My sql is: select count(*) from table_h where level = get_level_value(11268,id,area) and (date1 = '2011-1-1' or date2='2011-1-1') and (date1 = '2012-07-09' or date2='2012-07-09') This takes about 40sek. select count(*) from table_h where (date1 = '2011-1-1' or date2='2011-1-1') and (date1 = '2012-07-09' or date2='2012-07-09') when ignoring function, it takes 1sek. Function is: CREATE OR REPLACE FUNCTION get_level_value(_user integer, _id, _area) RETURNS integer AS $$ DECLARE found integer; BEGIN SELECT 1 INTO found FROM table_o WHERE userid=_user AND id=_id AND area=_area; IF (found) THEN return 3; ELSE return 1; END IF; END; $$ LANGUAGE plpgsql; On explain, it seems to me that this function is on filter and it will execute on every row. Total resultset contains 1 700 000 rows. QUERY PLAN Aggregate (cost=285543.89..285543.90 rows=1 width=0) (actual time=32391.380..32391.380 rows=1 loops=1) - Bitmap Heap Scan on table_h (cost=11017.63..284987.40 rows=222596 width=0) (actual time=326.946..31857.145 rows=631818 loops=1) Recheck Cond: ((date1 = '2011-01-01'::date) OR (date2 = '2011-01-01'::date)) Filter: (((date1 = '2012-07-09'::date) OR (date2 = '2012-07-09'::date)) AND (level = get_level_value(11268, id, area))) - BitmapOr (cost=11017.63..11017.63 rows=669412 width=0) (actual time=321.635..321.635 rows=0 loops=1) - Bitmap Index Scan on date1 (cost=0.00..10626.30 rows=652457 width=0) (actual time=84.555..84.555 rows=647870 loops=1) Index Cond: (date1 = '2011-01-01'::date) - Bitmap Index Scan on date2_table_h (cost=0.00..280.03 rows=16955 width=0) (actual time=237.074..237.074 rows=15222 loops=1) Index Cond: (date2 = '2011-01-01'::date) How should I handle this situation and use function? -- kupen -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! http://www.wippies.com/ -- 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] Custom function in where clause
Hi and thank's guys! First trying this Brendan's recommendation. It seems only a small difference between sql and PL/pgSQL. from 40--37. Not so good yet. I will try Maxim's little later and you all know. -- kupen Brendan Jurd [dire...@gmail.com] kirjoitti: On 10 July 2012 18:36, Pena Kupen ku...@wippies.fi wrote: Hi, I have searched solution to my problem a few days. On my query, there is big performance problem. It seems to me, that problem is on where-part of sql and it's function. How should I handle this situation and use function? I would start by rewriting your function in plain SQL rather than PL/pgSQL. As a general rule, don't write a function in PL/pgSQL unless you really need procedurality. This function does not. For example: CREATE OR REPLACE FUNCTION get_level_value(_user integer, _id, _area) RETURNS integer AS $$ -- Return 3 if there are matching records in table_o, otherwise return 1. SELECT CASE WHEN EXISTS ( SELECT id FROM table_o WHERE userid=_user AND id=_id AND area=_area ) THEN 3 ELSE 1 END; $$ LANGUAGE sql STABLE; Cheers, BJ -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! http://www.wippies.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Fw: Re: [PERFORM] Custom function in where clause
Hello again, Seems to be ok, by adding normal outer join and some fields on where-part. Previous, I use to used with Oracle and Sybase databases as much as possible functions/procedures. There ware something to do with performance: Do it on server, not in client. Typically all programs were c/s, maybe that or am I missing something? -- kupen Maxim Boguk [maxim.bo...@gmail.com] kirjoitti: On Tue, Jul 10, 2012 at 6:36 PM, Pena Kupen ku...@wippies.fi wrote: Hi, I have searched solution to my problem a few days. On my query, there is big performance problem. It seems to me, that problem is on where-part of sql and it's function. My sql is: select count(*) from table_hwhere level = get_level_value(11268,id,area) and (date1 = '2011-1-1' or date2='2011-1-1') and (date1 = '2012-07-09' or date2='2012-07-09') This takes about 40sek. select count(*) from table_hwhere (date1 = '2011-1-1' or date2='2011-1-1') and (date1 = '2012-07-09' or date2='2012-07-09') when ignoring function, it takes 1sek. Function is: CREATE OR REPLACE FUNCTION get_level_value(_user integer, _id, _area) RETURNS integer AS $$ DECLARE found integer; BEGIN SELECT 1 INTO found FROM table_o WHERE userid=_user AND id=_id AND area=_area; IF (found) THEN return 3; ELSE return 1; END IF; END; $$ LANGUAGE plpgsql; On explain, it seems to me that this function is on filter and it will execute on every row. Total resultset contains 1 700 000 rows. QUERY PLAN Aggregate (cost=285543.89..285543.90 rows=1 width=0) (actual time=32391.380..32391.380 rows=1 loops=1) - Bitmap Heap Scan on table_h (cost=11017.63..284987.40 rows=222596 width=0) (actual time=326.946..31857.145 rows=631818 loops=1) Recheck Cond: ((date1 = '2011-01-01'::date) OR (date2 = '2011-01-01'::date)) Filter: (((date1 = '2012-07-09'::date) OR (date2 = '2012-07-09'::date)) AND (level = get_level_value(11268, id, area))) - BitmapOr (cost=11017.63..11017.63 rows=669412 width=0) (actual time=321.635..321.635 rows=0 loops=1) - Bitmap Index Scan on date1 (cost=0.00..10626.30 rows=652457 width=0) (actual time=84.555..84.555 rows=647870 loops=1) Index Cond: (date1 = '2011-01-01'::date) - Bitmap Index Scan on date2_table_h (cost=0.00..280.03 rows=16955 width=0) (actual time=237.074..237.074 rows=15222 loops=1) Index Cond: (date2 = '2011-01-01'::date) How should I handle this situation and use function? You could not have good performance using function in case where direct JOIN is only way to have reasonable performance. Stop using function and write join with table_o instead, or put whole query with join inside a function. -- Maxim Boguk Senior Postgresql DBA http://www.postgresql-consulting.ru/ http://www.postgresql-consulting.com/ Phone RU: +7 910 405 4718 Phone AU: +61 45 218 5678 Skype: maxim.boguk Jabber: maxim.bo...@gmail.com 9@C3: http://mboguk.moikrug.ru/ People problems are solved with people. If people cannot solve the problem, try technology. People will then wish they'd listened at the first stage. -- Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti! http://www.wippies.com/ -- 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] Massive I/O spikes during checkpoint
On Tuesday, July 10, 2012 08:14:00 AM Maxim Boguk wrote: On Tue, Jul 10, 2012 at 4:03 PM, David Kerr d...@mr-paradox.net wrote: On Jul 9, 2012, at 10:51 PM, Maxim Boguk wrote: But what appears to be happening is that all of the data is being written out at the end of the checkpoint. This happens at every checkpoint while the system is under load. I get the feeling that this isn't the correct behavior and i've done something wrong. It's not an actual checkpoints. It's is a fsync after checkpoint which create write spikes hurting server. You should set sysctl vm.dirty_background_bytes and vm.dirty_bytes to reasonable low values So use bla_bytes instead of bla_ratio? Yes because on 256GB server echo 10 /proc/sys/vm/dirty_ratio is equivalent to 26Gb dirty_bytes and echo 5 /proc/sys/vm/dirty_background_ratio is equivalent to 13Gb dirty_background_bytes It is really huge values. So kernel doesn't start write any pages out in background before it has at least 13Gb dirty pages in kernel memory. And at end of the checkpoint kernel trying flush all dirty pages to disk. Thast not entirely true. The kernel will also writeout pages which haven't been written to for dirty_expire_centisecs. But yes, adjusting dirty_* is definitely a good idea. Andres -- Andres Freund http://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] Massive I/O spikes during checkpoint
On Tue, Jul 10, 2012 at 5:44 AM, Andres Freund and...@2ndquadrant.com wrote: On Tuesday, July 10, 2012 08:14:00 AM Maxim Boguk wrote: So kernel doesn't start write any pages out in background before it has at least 13Gb dirty pages in kernel memory. And at end of the checkpoint kernel trying flush all dirty pages to disk. Thast not entirely true. The kernel will also writeout pages which haven't been written to for dirty_expire_centisecs. There seems to be many situations in which it totally fails to do that. Although I've never been able to categorize just what those situations are. Cheers, Jeff -- 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] Massive I/O spikes during checkpoint
On Tuesday, July 10, 2012 03:36:35 PM Jeff Janes wrote: On Tue, Jul 10, 2012 at 5:44 AM, Andres Freund and...@2ndquadrant.com wrote: On Tuesday, July 10, 2012 08:14:00 AM Maxim Boguk wrote: So kernel doesn't start write any pages out in background before it has at least 13Gb dirty pages in kernel memory. And at end of the checkpoint kernel trying flush all dirty pages to disk. Thast not entirely true. The kernel will also writeout pages which haven't been written to for dirty_expire_centisecs. There seems to be many situations in which it totally fails to do that. Totally as in diry pages sitting around without any io activity? Or just not agressive enough? Currently its a bit hard to speculate about all without specifying the kernel because there have been massive rewrites of all that stuff in several kernels in the last two years... Andres -- Andres Freund http://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] The need for clustered indexes to boost TPC-V performance
On Thu, Jul 5, 2012 at 10:33 PM, Reza Taheri rtah...@vmware.com wrote: Just to be clear, we have a number of people from different companies working on the kit. This is not a VMware project, it is a TPC project. But I hear you regarding coming in from the cold and asking for a major db engine feature. I know that I have caused a lot of rolling eyes. Believe me, I have had the same (no, worse!) reaction from every one of the commercial database companies in response to similar requests over the past 25 years. No rolling of eyes from me. Clustered indexes work and if your table access mainly hits the table through that index you'll see enormous reductions in i/o. Index only scans naturally are a related optimization in the same vein. Denying that is just silly. BTW, putting postgres through a standard non trivial benchmark suite over reasonable hardware, reporting results, identifying bottlenecks, etc. is incredibly useful. Please keep it up, and don't be afraid to ask for help here. (one thing I'd love to see is side by side results comparing 8.4 to 9.1 to 9.2). merlin -- 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] The need for clustered indexes to boost TPC-V performance
Hi Merlin, We are moving up to a larger testbed, and are planning to use 9.2. But the results will not comparable to our 8.4 results due to differences in hardware. But that comparison is a useful one. I'll try for a quick test on the new hardware with 8.4 before moving to 9.2. Thanks, Reza -Original Message- From: Merlin Moncure [mailto:mmonc...@gmail.com] Sent: Tuesday, July 10, 2012 12:06 PM To: Reza Taheri Cc: Greg Smith; pgsql-performance@postgresql.org Subject: Re: [PERFORM] The need for clustered indexes to boost TPC-V performance On Thu, Jul 5, 2012 at 10:33 PM, Reza Taheri rtah...@vmware.com wrote: Just to be clear, we have a number of people from different companies working on the kit. This is not a VMware project, it is a TPC project. But I hear you regarding coming in from the cold and asking for a major db engine feature. I know that I have caused a lot of rolling eyes. Believe me, I have had the same (no, worse!) reaction from every one of the commercial database companies in response to similar requests over the past 25 years. No rolling of eyes from me. Clustered indexes work and if your table access mainly hits the table through that index you'll see enormous reductions in i/o. Index only scans naturally are a related optimization in the same vein. Denying that is just silly. BTW, putting postgres through a standard non trivial benchmark suite over reasonable hardware, reporting results, identifying bottlenecks, etc. is incredibly useful. Please keep it up, and don't be afraid to ask for help here. (one thing I'd love to see is side by side results comparing 8.4 to 9.1 to 9.2). merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] DELETE vs TRUNCATE explanation
Hi After seeing a few discussions here and on Stack Overflow I've put together a quick explanation of why DELETE FROM table; may be faster than TRUNCATE table for people doing unit testing on lots of tiny tables, people who're doing this so often they care how long it takes. I'd love it if a few folks who know the guts were to take a look and verify its correctness: http://stackoverflow.com/a/11423886/398670 -- Craig Ringer -- 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] DELETE vs TRUNCATE explanation
On Tue, Jul 10, 2012 at 5:37 PM, Craig Ringer ring...@ringerc.id.au wrote: Hi After seeing a few discussions here and on Stack Overflow I've put together a quick explanation of why DELETE FROM table; may be faster than TRUNCATE table for people doing unit testing on lots of tiny tables, people who're doing this so often they care how long it takes. I'd love it if a few folks who know the guts were to take a look and verify its correctness: I haven't said this before, but think it every time someone asks me about this, so I'll say it now: This is a papercut that should be solved with improved mechanics. TRUNCATE should simply be very nearly the fastest way to remove data from a table while retaining its type information, and if that means doing DELETE without triggers when the table is small, then it should. The only person who could thwart me is someone who badly wants their 128K table to be exactly 8 or 0K, which seems unlikely given the 5MB of catalog anyway. Does that sound reasonable? As in, would anyone object if TRUNCATE learned this behavior? -- fdr -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
