Re: [PERFORM] how could select id=xx so slow?
I have logged one day data and found the checkpoint is rather frequently(detail: https://gist.github.com/3088338). Not sure if it is normal, but the average time of checkpoint is about 100sec~200sec, it seems related with my settings: 574 checkpoint_segments = 64 575 wal_keep_segments = 5000 I set checkpoint_segments as a very large value which is because otherwise the slave server always can not follow the master, should I lower that value? or the slow query is about something else? thanks! On Tue, Jul 10, 2012 at 10:46 AM, Craig Ringer ring...@ringerc.id.auwrote: On 07/10/2012 10:25 AM, Yan Chunlu wrote: I didn't set log_min_duration_statement in the postgresql.conf, but execute *dbapi_con.cursor().execute(SET log_min_duration_statement to 30)* *for every connection.* OK, same effect: You're only logging slow statements. It's not at all surprising that BEGIN doesn't appear when a log_min_duration_statement is set. It's an incredibly fast operation. What's amazing is that it appears even once - that means your database must be in serious performance trouble, as BEGIN should take tenths of a millisecond on an unloaded system. For example my quick test here: LOG: statement: BEGIN; LOG: duration: 0.193 ms ... which is actually a lot slower than I expected, but hardly slow statement material. The frequent appearance of slow (multi-second) COMMIT statements in your slow statement logs suggests there's enough load on your database that there's real contention for disk, and/or that checkpoints are stalling transactions. First, you need to set log_min_messages = 'info' to allow Pg to complain about things like checkpoint frequency. Now temporarily set log_checkpoints = on to record when checkpoints happen and how long they take. Most likely you'll find you need to tune checkpoint behaviour. Some information, albeit old, on that is here: http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm Basically you might want to try increasing your checkpoint_completion_target and making the bgwriter more aggressive - assuming that your performance issues are in fact checkpoint related. It's also possible that they're just overall load, especially if you have lots and lots (hundreds) of connections to the database all trying to do work at once without any kind of admission control or pooling/queuing. In that case, introducing a connection pool like PgBouncer may help. -- Craig Ringer
Re: [PERFORM] Paged Query
Понеділок, 9 липня 2012 р. користувач Misa Simic misa.si...@gmail.com написав: 2012/7/9 Gregg Jaskiewicz gryz...@gmail.com Use cursors. By far the most flexible. offset/limit have their down sides. Well, I am not aware what down sides there are in LIMIT OFFSET what does not exist in any other solutions for paged queries... where key last-previous-key order by key -- Best regards, Vitalii Tymchyshyn
Re: [PERFORM] Paged Query
Понеділок, 9 липня 2012 р. користувач Misa Simic misa.si...@gmail.com написав: 2012/7/9 Gregg Jaskiewicz gryz...@gmail.com Use cursors. By far the most flexible. offset/limit have their down sides. Well, I am not aware what down sides there are in LIMIT OFFSET what does not exist in any other solutions for paged queries... 'where key last-value order by key limit N' is much better in performance for large offsets. p.s. Sorry for previous email- hit send too early. -- Best regards, Vitalii Tymchyshyn
Re: [PERFORM] how could select id=xx so slow?
Yan Chunlu wrote: I have logged one day data and found the checkpoint is rather frequently(detail: https://gist.github.com/3088338). Not sure if it is normal, but the average time of checkpoint is about 100sec~200sec, it seems related with my settings: 574 checkpoint_segments = 64 575 wal_keep_segments = 5000 I set checkpoint_segments as a very large value which is because otherwise the slave server always can not follow the master, should I lower that value? You mean, you set wal_keep_segments high for the standby, right? wal_keep_segments has no impact on checkpoint frequency and intensity. You are right that your checkpoint frequency is high. What is your value of checkpoint_timeout? You can increase the value of checkpoint_segments to decrease the checkpoint frequence, but recovery will take longer then. or the slow query is about something else? thanks! I guess the question is how saturated the I/O system is during checkpoints. But even if it is very busy, I find it hard to believe that such a trivial statement can take extremely long. Yours, Laurenz Albe -- 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] how could select id=xx so slow?
could that because of my system is really busy? 1, postgresql always have 400+ connections(dozens of python process using client pool) 2, the query peak is 50+/s 3, I do have some bad performance sql executing periodically, need 100+ second to complete. could those bad performance sql influence others? because when I execute those simple sql directly, they was fast. but the slow query log shows it took too much time. On Wed, Jul 11, 2012 at 4:23 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote: Yan Chunlu wrote: I have logged one day data and found the checkpoint is rather frequently(detail: https://gist.github.com/3088338). Not sure if it is normal, but the average time of checkpoint is about 100sec~200sec, it seems related with my settings: 574 checkpoint_segments = 64 575 wal_keep_segments = 5000 I set checkpoint_segments as a very large value which is because otherwise the slave server always can not follow the master, should I lower that value? You mean, you set wal_keep_segments high for the standby, right? wal_keep_segments has no impact on checkpoint frequency and intensity. You are right that your checkpoint frequency is high. What is your value of checkpoint_timeout? You can increase the value of checkpoint_segments to decrease the checkpoint frequence, but recovery will take longer then. or the slow query is about something else? thanks! I guess the question is how saturated the I/O system is during checkpoints. But even if it is very busy, I find it hard to believe that such a trivial statement can take extremely long. Yours, Laurenz Albe
Re: [PERFORM] how could select id=xx so slow?
On Wed, Jul 11, 2012 at 9:24 AM, Yan Chunlu springri...@gmail.com wrote: I have logged one day data and found the checkpoint is rather frequently(detail: https://gist.github.com/3088338). Not sure if it is normal, but the average time of checkpoint is about 100sec~200sec, it seems related with my settings: 574 checkpoint_segments = 64 575 wal_keep_segments = 5000 I set checkpoint_segments as a very large value which is because otherwise the slave server always can not follow the master, should I lower that value? or the slow query is about something else? thanks! Some things to notice from the checkpoints log: * All chcekpoints are triggered by checkpoint_timeout, using up only a couple log files * Checkpoints write out around 40MB of buffers * The write out period is spread out nicely like it's supposed to but the sync phase is occasionally taking a very long time (more than 2 minutes) This looks like something (not necessarily the checkpoint sync itself) is overloading the IO system. You might want to monitor the IO load with iostat and correlate it with the checkpoints and slow queries to find the culprit. It's also possible that something else is causing the issues. If the cause is checkpoints, just making them less frequent might make the problem worse. I'm assuming you have 16GB+ of RAM because you have 4GB of shared_buffers. Just making checkpoint_timeout longer will accumulate a larger number of dirty buffers that will clog up the IO queues even worse. If you are on Linux, lowering dirty_expire_centisecs or dirty_background_bytes might help to spread the load out but will make overall throughput worse. On the otherhand, if the I/O overload is from queries (more likely because some checkpoints sync quickly) there are no easy tuning answers. Making queries less IO intensive is probably the best you can do. From the tuning side, newer Linux kernels handle I/O fairness a lot better, and you could also try tweaking the I/O scheduler to achieve better throughput to avoid congestion or at least provide better latency for trivial queries. And of course its always possible to throw more hardware at the problem and upgrade the I/O subsystem. Ants Aasma -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- 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
Daniel Farina dan...@heroku.com writes: 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? Yes, I will push back on that. (1) We don't need the extra complexity. (2) I don't believe that you know where the performance crossover point would be (according to what metric, anyway?). (3) The performance of the truncation itself should not be viewed in isolation; subsequent behavior also needs to be considered. An example of possible degradation is that index bloat would no longer be guaranteed to be cleaned up over a series of repeated truncations. (You might argue that if the table is small then the indexes couldn't be very bloated, but I don't think that holds up over a long series.) IOW, I think it's fine as-is. I'd certainly wish to see many more than one complainant before we expend effort in this area. regards, tom lane -- 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 Wed, Jul 11, 2012 at 10:05:48AM -0400, Tom Lane wrote: Daniel Farina dan...@heroku.com writes: 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? Yes, I will push back on that. (1) We don't need the extra complexity. (2) I don't believe that you know where the performance crossover point would be (according to what metric, anyway?). (3) The performance of the truncation itself should not be viewed in isolation; subsequent behavior also needs to be considered. An example of possible degradation is that index bloat would no longer be guaranteed to be cleaned up over a series of repeated truncations. (You might argue that if the table is small then the indexes couldn't be very bloated, but I don't think that holds up over a long series.) IOW, I think it's fine as-is. I'd certainly wish to see many more than one complainant before we expend effort in this area. regards, tom lane +1 TRUNCATE needs to keep the same properties independent of the size of the table. Smearing it into a DELETE would not be good at all. If there are optimizations that can be done to keep its current behavior, those might be possible, but the complexity may not be worthwhile for a relative corner case. Regards, Ken -- 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] how could select id=xx so slow?
huge thanks for the patient explanations, I think you are right, it is really related to the IO. I monitor the IO using iostat -x and found the utilize part reach 100% frequently, postgresql is the only service running on that machine, so I think it is either checkpoint or queries caused the problem. and I agree that checkpoint may not the problem, I guess I need to tackle those damn queries. currently the data dir(pgsql/data/base) used 111GB disk space, some tables has tens of millions records. could that cause the query heavy disk IO? when should I split the data to other machines(aka sharding)? and you are right the machine has 16GB memory and commodity 500GB disk. kernel: Linux adams 2.6.26-2-amd64 #1 SMP Mon Jun 13 16:29:33 UTC 2011 x86_64 GNU/Linux by new kernel which version do you mean? and about those IO intensive queries, I can only tell the time used from slow query log, is there anything like explain analyze that shows specific information about IO usage? On Wed, Jul 11, 2012 at 7:59 PM, Ants Aasma a...@cybertec.at wrote: On Wed, Jul 11, 2012 at 9:24 AM, Yan Chunlu springri...@gmail.com wrote: I have logged one day data and found the checkpoint is rather frequently(detail: https://gist.github.com/3088338). Not sure if it is normal, but the average time of checkpoint is about 100sec~200sec, it seems related with my settings: 574 checkpoint_segments = 64 575 wal_keep_segments = 5000 I set checkpoint_segments as a very large value which is because otherwise the slave server always can not follow the master, should I lower that value? or the slow query is about something else? thanks! Some things to notice from the checkpoints log: * All chcekpoints are triggered by checkpoint_timeout, using up only a couple log files * Checkpoints write out around 40MB of buffers * The write out period is spread out nicely like it's supposed to but the sync phase is occasionally taking a very long time (more than 2 minutes) This looks like something (not necessarily the checkpoint sync itself) is overloading the IO system. You might want to monitor the IO load with iostat and correlate it with the checkpoints and slow queries to find the culprit. It's also possible that something else is causing the issues. If the cause is checkpoints, just making them less frequent might make the problem worse. I'm assuming you have 16GB+ of RAM because you have 4GB of shared_buffers. Just making checkpoint_timeout longer will accumulate a larger number of dirty buffers that will clog up the IO queues even worse. If you are on Linux, lowering dirty_expire_centisecs or dirty_background_bytes might help to spread the load out but will make overall throughput worse. On the otherhand, if the I/O overload is from queries (more likely because some checkpoints sync quickly) there are no easy tuning answers. Making queries less IO intensive is probably the best you can do. From the tuning side, newer Linux kernels handle I/O fairness a lot better, and you could also try tweaking the I/O scheduler to achieve better throughput to avoid congestion or at least provide better latency for trivial queries. And of course its always possible to throw more hardware at the problem and upgrade the I/O subsystem. Ants Aasma -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de
Re: [PERFORM] moving tables
Last night I created directories and moved files as outlined in Josh's very helpful reply to my original request. All seemed okay until we unmounted the drives from the first volume. I got the following error (with oid differences) whenever I tried to access any of the tables that were not originally on the 2nd volume raid 10: ERROR: could not open file pg_tblspc/18505/PG_9.0_201008051/99644466/99645029: No such file or directory When I looked at the files in the linked directories on the raid 10, it appeared that the oid (18505 in the above error) was missing. After we remounted the drives so that access could be restored, it occurred to me that I should have altered the tablespaces to match the move to the 2nd volume. Would that have dealt with the error I saw? On further reflection, it seems that the best course of action would be to have only the one tablespace on the existing raid 10 drive that resides on the 2nd volume. Then the first volume can be reconfigured into one raid 10 and I could move everything to it and the 2nd volume can physically be removed for use in another server that I can configure as a hot standby. Does this plan make sense? Any comments or suggestions are welcome. Thanks, Midge - Original Message - From: Josh Berkus To: pgsql-performance@postgresql.org Sent: Wednesday, June 20, 2012 5:28 PM Subject: Re: [PERFORM] moving tables On 6/20/12 3:27 PM, Midge Brown wrote: I need to move a postgres 9.0 database -- with tables, indexes, and wals associated with 16 tablespaces on 12 logical drives -- to an existing raid 10 drive in another volume on the same server. Once I get the data off the initial 12 drives they will be reconfigured, at which point I'll need to move everything from the 2nd volume to the aforementioned 12 logical drives on the first volume. This is being done both to free up the 2nd volume and to better utilize raid 10. I checked around and found a way to create sql statements to alter the public tablespaces and indexes, but I haven't found anything that provides information about moving the numerous associated config files, log files, etc. ANY comments, suggestions, or direction to existing documentation would be greatly appreciated. 1. back everything up. 2. create a bunch of directories on the RAID10 to match the existing tablespaces (they won't be mounts, but Postgres doesn't care about that). 3. shut down postgres 4. copy all your files to the new directories 5. change your mount points which were in use by the old tablespaces to symlinks which point at the new diretories 6. start postgres back up from the new location -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] DELETE vs TRUNCATE explanation
Tom Lane wrote: (3) The performance of the truncation itself should not be viewed in isolation; subsequent behavior also needs to be considered. An example of possible degradation is that index bloat would no longer be guaranteed to be cleaned up over a series of repeated truncations. (You might argue that if the table is small then the indexes couldn't be very bloated, but I don't think that holds up over a long series.) IOW, I think it's fine as-is. I'd certainly wish to see many more than one complainant before we expend effort in this area. I think a documentation change would be worthwhile. At the moment the TRUNCATE page says, with no caveats, that it is faster than unqualified DELETE. It surprised me to find that this wasn't true (with 7.2, again with small tables in a testsuite), and evidently it's still surprising people today. -M- -- 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 Wed, Jul 11, 2012 at 7:05 AM, Tom Lane t...@sss.pgh.pa.us wrote: Daniel Farina dan...@heroku.com writes: 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? Yes, I will push back on that. (1) We don't need the extra complexity. (2) I don't believe that you know where the performance crossover point would be (according to what metric, anyway?). (3) The performance of the truncation itself should not be viewed in isolation; subsequent behavior also needs to be considered. An example of possible degradation is that index bloat would no longer be guaranteed to be cleaned up over a series of repeated truncations. (You might argue that if the table is small then the indexes couldn't be very bloated, but I don't think that holds up over a long series.) IOW, I think it's fine as-is. I'd certainly wish to see many more than one complainant before we expend effort in this area. It strikes me as a contrived case rather than a use case. What sort of app repeatedly fills and truncates a small table thousands of times ... other than a test app to see whether you can do it or not? The main point of truncate is to provide a more efficient mechanism to delete all data from large tables. If your app developers don't know within a couple orders of magnitude how much data your tables hold, and can't figure out whether to use delete or truncate, I can't find much sympathy in my heart. Craig
Re: [PERFORM] DELETE vs TRUNCATE explanation
On 07/11/2012 03:18 PM, Craig James wrote: It strikes me as a contrived case rather than a use case. What sort of app repeatedly fills and truncates a small table thousands of times ... other than a test app to see whether you can do it or not? Test systems. Any company with even a medium-size QA environment will have continuous integration systems that run unit tests on a trash database hundreds or thousands of times through the day. Aside from dropping/creating the database via template, which would be *really* slow, truncate is the easiest/fastest way to reset between tests. If TRUNCATE suddenly started defaulting to DELETE on small table-sets and several iterations led to exponential index growth, that would be rather unfortunate. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 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] DELETE vs TRUNCATE explanation
On 07/11/2012 04:47 PM, Shaun Thomas wrote: On 07/11/2012 03:18 PM, Craig James wrote: It strikes me as a contrived case rather than a use case. What sort of app repeatedly fills and truncates a small table thousands of times ... other than a test app to see whether you can do it or not? Test systems. Any company with even a medium-size QA environment will have continuous integration systems that run unit tests on a trash database hundreds or thousands of times through the day. Aside from dropping/creating the database via template, which would be *really* slow, truncate is the easiest/fastest way to reset between tests. Why is recreating the test db from a (populated) template going to be slower than truncating all the tables and repopulating from an external source? I had a client who achieved a major improvement in speed and reduction in load by moving to this method of test db setup. cheers andrew -- 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 11/07/12 21:18, Craig James wrote: It strikes me as a contrived case rather than a use case. What sort of app repeatedly fills and truncates a small table thousands of times ... other than a test app to see whether you can do it or not? If I have a lot of data which updates/inserts an existing table but I don't know if a given record will be an update or an insert, then I write all the 'new' data to a temporary table and then use sql statements to achieve the updates and inserts on the existing table. Is there a better way of doing this in standard SQL? Mark -- 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 Wed, Jul 11, 2012 at 2:32 PM, Mark Thornton mthorn...@optrak.com wrote: On 11/07/12 21:18, Craig James wrote: It strikes me as a contrived case rather than a use case. What sort of app repeatedly fills and truncates a small table thousands of times ... other than a test app to see whether you can do it or not? If I have a lot of data which updates/inserts an existing table but I don't know if a given record will be an update or an insert, then I write all the 'new' data to a temporary table and then use sql statements to achieve the updates and inserts on the existing table. Is there a better way of doing this in standard SQL? If it's a single session, use a temporary table. It is faster to start with (temp tables aren't logged), and it's automatically dropped at the end of the session (or at the end of the transaction if that's what you specified when you created it). This doesn't work if your insert/update spans more than one session. Another trick that works (depending on how big your tables are) is to scan the primary key before you start, and build a hash table of the keys. That instantly tells you whether each record should be an insert or update. Craig Mark
Re: [PERFORM] DELETE vs TRUNCATE explanation
On Wed, Jul 11, 2012 at 7:05 AM, Tom Lane t...@sss.pgh.pa.us wrote: Daniel Farina dan...@heroku.com writes: 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? Yes, I will push back on that. (1) We don't need the extra complexity. Well, a need is justified by the gains, no? It seems like this follows from the thoughts presented afterwards, so I'll discuss those. (2) I don't believe that you know where the performance crossover point would be (according to what metric, anyway?). Nope. I don't. But an exact crossover is a level of precision I don't really need, because here are where things stand on a completely unremarkable test suite on the closest project to me that meets the regular web-app profile case: With en-masse DELETE: rake 41.89s user 3.08s system 76% cpu 58.629 total With TRUNCATE: rake 49.86s user 2.93s system 5% cpu 15:17.88 total 15x slower. This is a Macbook Air with full disk encryption and SSD disk with fsync off, e.g. a very typical developer configuration. This is a rather small schema -- probably a half a dozen tables, and probably about a dozen indexes. This application is entirely unremarkable in its test-database workload: it wants to load a few records, do a few things, and then clear those handful of records. (3) The performance of the truncation itself should not be viewed in isolation; subsequent behavior also needs to be considered. An example of possible degradation is that index bloat would no longer be guaranteed to be cleaned up over a series of repeated truncations. (You might argue that if the table is small then the indexes couldn't be very bloated, but I don't think that holds up over a long series.) I'm not entirely convinced to the mechanism, it was simply the most obvious one, but I bet a one that is better in every respect is also possible. It did occur to me that bloat might be a sticky point. IOW, I think it's fine as-is. I'd certainly wish to see many more than one complainant before we expend effort in this area. I've seen way more than one complaint, and I'm quite sure there are thousands of man hours (or more) spent on people who don't even know to complain about such atrocious performance (or maybe it's so bad that most people run a web search and find out, probably being left really annoyed from having to yak shave as a result). In spite of how familiar I am with Postgres and its mailing lists, I have glossed over this for a long time, just thinking wow, that really sucks and only now -- by serendipity of having skimmed this post -- have seen fit to complain on behalf of quite a few rounds of dispensing workaround advice to other people. It's only when this was brought to the fore of my mind did I stop to consider how much wasted time I've seen in people trying to figure this out over and over again (granted, they tend to remember after the first time). Perhaps a doc fix is all we need (TRUNCATE is constant-time on large tables, but can be very slow compared to DELETE on small tables), but I completely and enthusiastically reject any notion from people calling this contrived or an edge case, because people writing software against PostgreSQL that have unit tests have this use case constantly, often dozens or even hundreds of times a day. What I don't know is how many people figure out that they should use DELETE instead, and after how long. Even though the teams I work with are very familiar with many of the finer points of Postgres, doing some probing for the first time took a little while. If we're going to live with it, I contest that we should own it as a real and substantial weakness for development productivity, and not sweep it under the rug as some contrived or corner case. -- fdr -- 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] how could select id=xx so slow?
On 07/11/2012 07:40 PM, Yan Chunlu wrote: could that because of my system is really busy? 1, postgresql always have 400+ connections(dozens of python process using client pool) 2, the query peak is 50+/s 3, I do have some bad performance sql executing periodically, need 100+ second to complete. could those bad performance sql influence others? because when I execute those simple sql directly, they was fast. but the slow query log shows it took too much time. Oh, come on, these are the sorts of things you tell us /when you ask your question/, not days later after lots of back-and-forth discussion. -- Craig Ringer
Re: [PERFORM] how could select id=xx so slow?
Really sorry for the lack of information, but I did asked if the slow queries could affect those simple one: 'so I wonder could this simple select is innocent and affected badly by other queries? ' I didn't mention the connections number because I don't think my app is that busy, and the large number connections was caused by slow queries. I was wrong, everything is connected, too many factor could end with the result,I am really sorry, I will tell everything I knew the next time:) I learnt a lot during the back and forth! On Thursday, July 12, 2012, Craig Ringer wrote: On 07/11/2012 07:40 PM, Yan Chunlu wrote: could that because of my system is really busy? 1, postgresql always have 400+ connections(dozens of python process using client pool) 2, the query peak is 50+/s 3, I do have some bad performance sql executing periodically, need 100+ second to complete. could those bad performance sql influence others? because when I execute those simple sql directly, they was fast. but the slow query log shows it took too much time. Oh, come on, these are the sorts of things you tell us *when you ask your question*, not days later after lots of back-and-forth discussion. -- Craig Ringer
Re: [PERFORM] how could select id=xx so slow?
On Wed, Jul 11, 2012 at 5:47 PM, Yan Chunlu springri...@gmail.com wrote: I learnt a lot during the back and forth! Great to hear. 1, postgresql always have 400+ connections(dozens of python process using client pool) Note that Postgres does not deal well with a large number of connections[1]: consider shrinking the size of the pool. [1]: http://wiki.postgresql.org/wiki/Number_Of_Database_Connections -- 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 07/12/2012 02:10 AM, Matthew Woodcraft wrote: I think a documentation change would be worthwhile. At the moment the TRUNCATE page says, with no caveats, that it is faster than unqualified DELETE. +1 to updating the docs to reflect the fact that TRUNCATE may have a higher fixed cost than DELETE FROM table; but also prevents bloat. It's a weird little corner case, but with database-backed unit testing it's going to become a more significant one whether or not it feels like it makes any sense. -- 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 07/11/2012 01:22 PM, Daniel Farina wrote: 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? Yep, I'd object. It's more complicated and less predictable. Also, as I strongly and repeatedly highlighted in my post, DELETE FROM table; does a different job to TRUNCATE. You'd at minimum need the effect of DELETE followed by a VACUUM on the table and its indexes to be acceptable and avoid the risk of rapid table + index bloat - and that'd be lots slower than a TRUNCATE. You could be clever and lock the table then DELETE and set xmax at the same time I guess, but I suspect that'd be a bit of work and still wouldn't take care of the indexes. It's also too complicated, not least because AFAIK util commands and CRUD commands go through very different paths in PostgreSQL. I guess you could propose and post a prototype patch for a new command that tried to empty the table via whatever method it thought would be fastest. Such a new command wouldn't be bound by the accepted and expected rules followed by TRUNCATE so it could vary its behaviour based on the table, doing a real truncate on big tables and a delete-then-vaccum on small tables. I suspect you'd land up writing the fairly complicated code for the potentially multi-table delete-and-vaccum yourself. Honestly, though, it might be much better to start with how can TRUNCATE of empty or near-empty tables be made faster? and start examining where the time goes. -- 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 07/12/2012 06:51 AM, Daniel Farina wrote: 15x slower. This is a Macbook Air with full disk encryption and SSD disk with fsync off, e.g. a very typical developer configuration. Don't use full disk encryption for throwaway test data if you care about how long those tests take. It's a lot like tuning the engine in your car while ignoring the fact that the handbrake is jammed on and you're dragging a parachute. Use a ramdisk or un-encrypted partition, something that doesn't take three weeks to fsync(). That said, this performance gap makes me wonder if TRUNCATE is forcing metadata synchronisation even with fsync=off, causing the incredibly glacially awesomely slow disk access of your average FDE system to kick in, possibly even once per table or even once per file (index, table, toast, etc). If so, it may be worth: - Allowing TRUNCATE to skip synchronization when fsync=off. Pg is already allowed to eat all your data if it feels like it in this configuration, so there's no point flushing filesystem metadata to make sure files are really swapped. - When fsync=on, trying to flush all changes to all files out at once rather than once per file as it could be doing (haven't checked) right now. How to do this without also flushing all other pending I/O on the whole system (with a global sync()) would be somewhat OS/filesystem dependent, unfortunately. You could help progress this issue constructively by doing some profiling on your system, tracing Pg's system calls, and determining what exactly it's doing with DELETE vs TRUNCATE and where the time goes. On Linux you'd use OProfile for this and on Solaris you'd use DTrace. Dunno what facilities Mac OS X has but there must be something similar. Once you've determined why it's slow, you have a useful starting point for making it faster, first for test systems with fsync=off then, once that's tracked down, maybe for robust systems with fsync=on. I've seen way more than one complaint, and I'm quite sure there are thousands of man hours (or more) spent on people who don't even know to complain about such atrocious performance (or maybe it's so bad that most people run a web search and find out, probably being left really annoyed from having to yak shave as a result). I suspect you're right - as DB based unit testing becomes more commonplace this is turning up a lot more. As DB unit tests were first really popular in the ruby/rails crowd they've probably seen the most pain, but as someone who doesn't move in those circles I wouldn't have known. They certainly don't seem to have been making noise about it here, and I've only recently seen some SO questions about it. Perhaps a doc fix is all we need (TRUNCATE is constant-time on large tables, but can be very slow compared to DELETE on small tables), but I completely and enthusiastically reject any notion from people calling this contrived or an edge case, because people writing software against PostgreSQL that have unit tests have this use case constantly, often dozens or even hundreds of times a day. I have to agree with this - it may have been an edge case in the past, but it's becoming mainstream and is worth being aware of. That said, the group of people who care about this most are not well represented as active contributors to PostgreSQL. I'd love it if you could help start to change that by stepping in and taking a little time to profile exactly what's going on with your system so we can learn what, exactly, is slow. -- 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] how could select id=xx so slow?
On 07/12/2012 08:47 AM, Yan Chunlu wrote: Really sorry for the lack of information I shouldn't have grumped like that either, sorry about that. I didn't mention the connections number because I don't think my app is that busy, and the large number connections was caused by slow queries. Yep - assumptions are a killer like that. Now you know to watch your system load with iostat, vmstat, top, etc and to monitor your overall load. -- Craig Ringer
Re: [PERFORM] how could select id=xx so slow?
after check out the wiki page Maciek mentioned, turns out that heavy connection also burden the disk hardly. looks like I am in the vicious circle: 1, slow query cause connection blocked so the client request more connection. 2, more connection cause high disk io and make even the simplest query slow and block. I guess I should optimized those queries first... On Thu, Jul 12, 2012 at 10:20 AM, Craig Ringer ring...@ringerc.id.auwrote: On 07/12/2012 08:47 AM, Yan Chunlu wrote: Really sorry for the lack of information I shouldn't have grumped like that either, sorry about that. I didn't mention the connections number because I don't think my app is that busy, and the large number connections was caused by slow queries. Yep - assumptions are a killer like that. Now you know to watch your system load with iostat, vmstat, top, etc and to monitor your overall load. -- Craig Ringer
