Re: [PERFORM] Slow insert performace, 8.3 Wal related?
On Thu, Jan 15, 2009 at 2:36 PM, Bill Preston wrote: > We are in Southern California. > What I need someone for when the SHTF again, and if I can't handle it, I > have some resource to get on the job right away. And it would help if they > were a company that does this kind of thing so that I can get some buy in > from those above. Did you look here? http://www.postgresql.org/support/professional_support_northamerica Personally, I would first look to a company who currently pays active PostgreSQL developers - Command Prompt, EnterpriseDB are two prominent vendors on that list. Looking at their websites (I have not used the services of either) Command Prompt has a number you can call for round-the-clock support whether you are a customer or not and fairly clear pricing available up front. -Dave -- 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 insert performace, 8.3 Wal related?
Thanks Scott. We are in Southern California. What I need someone for when the SHTF again, and if I can't handle it, I have some resource to get on the job right away. And it would help if they were a company that does this kind of thing so that I can get some buy in from those above. Rusty Scott Marlowe wrote: On Thu, Jan 15, 2009 at 2:55 PM, Bill Preston wrote: Nothing special about that table. One index. It really seems that the system would grind to a stand-still when a lot of non-transaction inserts were run combined with the creation of some large temp tables. Since we added transactions and started using truncate, things have cleared up nicely. The suggestions here really helped. Does anyone know of some established postgresql consultants that can be hired for emergency analysis/tuning when things come up? There are several companies who have employees on this list who provide for fee contract / consulting work. If you're local to me and need help over a weekend I might have some spare time. :) But I'm generally pretty busy on weekends. -- 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 insert performace, 8.3 Wal related?
On Thu, Jan 15, 2009 at 2:55 PM, Bill Preston wrote: > Nothing special about that table. One index. > > It really seems that the system would grind to a stand-still when a lot of > non-transaction inserts were run combined with the creation of some large > temp tables. > > Since we added transactions and started using truncate, things have cleared > up nicely. The suggestions here really helped. > > Does anyone know of some established postgresql consultants that can be > hired for emergency analysis/tuning when things come up? There are several companies who have employees on this list who provide for fee contract / consulting work. If you're local to me and need help over a weekend I might have some spare time. :) But I'm generally pretty busy on weekends. -- 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 insert performace, 8.3 Wal related?
Nothing special about that table. One index. It really seems that the system would grind to a stand-still when a lot of non-transaction inserts were run combined with the creation of some large temp tables. Since we added transactions and started using truncate, things have cleared up nicely. The suggestions here really helped. Does anyone know of some established postgresql consultants that can be hired for emergency analysis/tuning when things come up? Rusty Alan Hodgson wrote: On Monday 12 January 2009, Bill Preston wrote: As to the second example with the delete. There are no foreign keys. For the index. If the table has fields a,b,c and d. We have a btree index (a,b,c,d) and we are saying DELETE FROM table_messed_up WHERE a=x. Is there anything special about this table? Does it have like a hundred indexes on it or something? Because deleting 8k rows from a normal table should never take more than a couple of seconds. -- 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 insert performace, 8.3 Wal related?
Bill Preston wrote: Fsync is off in 8.3. You should consider turning synchronous_commit off instead. That's almost as good as fsync=off performance-wise, but doesn't leave your database corrupt in case of power loss or OS crash. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Slow insert performace, 8.3 Wal related?
On Monday 12 January 2009, Bill Preston wrote: > As to the second example with the delete. There are no foreign keys. > For the index. If the table has fields a,b,c and d. > We have a btree index (a,b,c,d) > and we are saying DELETE FROM table_messed_up WHERE a=x. > Is there anything special about this table? Does it have like a hundred indexes on it or something? Because deleting 8k rows from a normal table should never take more than a couple of seconds. -- Alan -- 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 insert performace, 8.3 Wal related?
Wow thanks for all the help Tom and Alan. Sadly I was un-aware of the well-known behavior. Consider it more will known now. Fsync is off in 8.3. I am not too worried about what was before in 8.1 since we are not going back. For the first example (bad behavior when I am not using transactions). Is there anyway to tell that it is going on at a given point and time? Is their a buffer that fills up, a stat somewhere that I can read? A lot of our code isn't using transactions yet so I would like a heads up when this problem is happening or if possible increase some parameter so it happens less. As to the second example with the delete. There are no foreign keys. For the index. If the table has fields a,b,c and d. We have a btree index (a,b,c,d) and we are saying DELETE FROM table_messed_up WHERE a=x. So the WHERE statement is the first field in the the index. Now that you have given me more knowledge, let me ask a question that might lead to the answer. Example 1 happens in isolation. Example 2 happened on a live system with the parameters that I specified and a whole lot of sql statements without transactions being run at the same time. In fact their probably was a whole lot of inserts on this very table before the delete statement was hit. Is it possible that a problem like Example 1 caused the behavior that I witnessed in Example 2? It was waiting for the WAL's to catch up or something? Thanks Rusty Alan Hodgson wrote: On Monday 12 January 2009, Bill Preston wrote: I had a data load that I was doing with 8.1. It involved about 250k sql statements that were inserts into a table with just one index. The index has two fields. With the upgrade to 8.3 that process started taking all night and 1/2 a day. It inserted at the rate of 349 records a minute. When I started working on the problem I decided to test by putting all statements withing a single transaction. Just a simple BEGIN at the start and COMMIT at the end. Magically it only took 7 minutes to do the whole set, or 40k per minute. That seemed very odd to me, but at least I solved the problem. That's well-known behaviour. If you don't do them in one big transaction, PostgreSQL has to fsync after every insert, which effectively limits your insert rate to the rotational speed of your WAL drive (roughly speaking). If you don't explicitly start and end transactions, PostgreSQL does it for you. For every statement. The most recently noticed simple problem. I had a table with about 20k records. We issued the statement DELETE FROM table where this=that. This was part of a combined index and about 8k records should have been deleted. This statement caused all other queries to grind to a halt. It was only when I killed it that normal operation resumed. It was acting like a lock, but that table was not being used by any other process. Are there foreign keys on any other table(s) that point to this one? Are the relevant columns in those tables indexed? -- 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 insert performace, 8.3 Wal related?
On Monday 12 January 2009, Bill Preston wrote: > I had a data load that I was doing with 8.1. It involved about 250k sql > statements that were inserts into a table with just one index. The index > has two fields. > With the upgrade to 8.3 that process started taking all night and 1/2 a > day. It inserted at the rate of 349 records a minute. > When I started working on the problem I decided to test by putting all > statements withing a single transaction. Just a simple BEGIN at the > start and COMMIT at the end. Magically it only took 7 minutes to do the > whole set, or 40k per minute. That seemed very odd to me, but at least I > solved the problem. > That's well-known behaviour. If you don't do them in one big transaction, PostgreSQL has to fsync after every insert, which effectively limits your insert rate to the rotational speed of your WAL drive (roughly speaking). If you don't explicitly start and end transactions, PostgreSQL does it for you. For every statement. > The most recently noticed simple problem. > I had a table with about 20k records. We issued the statement DELETE > FROM table where this=that. > This was part of a combined index and about 8k records should have been > deleted. > This statement caused all other queries to grind to a halt. It was only > when I killed it that normal operation resumed. It was acting like a > lock, but that table was not being used by any other process. Are there foreign keys on any other table(s) that point to this one? Are the relevant columns in those tables indexed? -- Current Peeve: The mindset that the Internet is some sort of school for novice sysadmins and that everyone -not- doing stupid dangerous things should act like patient teachers with the ones who are. -- Bill Cole, NANAE -- 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 insert performace, 8.3 Wal related?
Bill Preston writes: > I've noticed some performance problems that I am guessing are WAL > related based on my browsing around and wondered if someone had some > suggestions for tuning the WAL settings. It could also help if someone > just laughed at me and told me it wasn't WAL. Consider it done ;-). I'm not sure what your problem is but it's unlikely to be WAL, especially not if you're using the same WAL-related settings in 8.1 and 8.3. Which you might not be. The large speedup from wrapping many small inserts into one transaction is entirely expected and should have occurred on 8.1 as well. I am suspicious that you were running 8.1 with fsync off and 8.3 has it on. Do you still have your 8.1 postgresql.conf? Comparing all the non-defaulted settings would be the first thing to do. If it's not that, I'm not sure. One cross-version difference that comes to mind is that 8.3 is a bit stickier about implicit casting, and so it seems conceivable that something about these queries was considered indexable in 8.1 and is not in 8.3. But you've not provided enough detail to do more than speculate. 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] slow insert into very large table
Wolfgang Gehner <[EMAIL PROTECTED]> writes: > This is with PostgreSQL 8.0 final for WindowsXP on a Pentium 1.86 GHz, > 1GB Memory. HD is fast IDE. Try something more recent, like 8.0.3 or 8.0.4. IIRC we had some performance issues in 8.0.0 with tables that grew from zero to large size during a single session. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] slow insert into very large table
Wolfgang Gehner wrote: Hi there, I need a simple but large table with several million records. I do batch inserts with JDBC. After the first million or so records, the inserts degrade to become VERY slow (like 8 minutes vs initially 20 secondes). The table has no indices except PK while I do the inserts. This is with PostgreSQL 8.0 final for WindowsXP on a Pentium 1.86 GHz, 1GB Memory. HD is fast IDE. I already have shared buffers already set to 25000. I wonder what else I can do. Any ideas? Run VACUUM ANALYZE to have statistics reflect the growth of the table. The planner probably still assumes your table to be small, and thus takes wrong plans to check PK indexes or so. Regards, Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Slow insert
On Wed, Dec 08, 2004 at 10:42:19AM +0800, Christopher Kings-Lynne wrote:
> Why would an INSERT ever be really slow? This is what I see a lot of in
> our site logs:
>
> Dec 5 15:57:48 marshall postgres[19599]: [3-1] LOG: duration:
> 13265.492 ms statement: INSERT INTO users_sessions (sid, cobrand_id,
> uid) VALUES ('145982ac39e1d09fec99cc8a606155e7', '1', '0')
>
> 13 seconds to insert a single row!
Do you have a foreign key or other check which could be really slow?
/* Steinar */
--
Homepage: http://www.sesse.net/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Slow INSERT
Michal Taborsky <[EMAIL PROTECTED]> writes: > I ran some tests to support this hypothesis. Every 500th insert is a tad > slower, but it is insignificant (normally the INSERT lasts 1.5ms, every > 500th is 9ms). During my tests (10 runs of 1000 INSERTS) I had > experienced only one "slow" insert (2000ms). It is clearly caused by > other processes running on this server, but such degradation of > performance is highly suspicious, because the server very rarely goes > over load 1.0. Actually, the simpler theory is that the slowdown is caused by background checkpoint operations. Now a checkpoint would slow *everything* down not only this one insert, so maybe that's not the right answer either, but it's my next idea. You could check this to some extent by manually issuing a CHECKPOINT command and seeing if you get an insert hiccup. Note though that closely spaced checkpoints will have less effect, because less I/O will be triggered when not much has changed since the last one. So you'd want to wait a bit between experiments. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Slow INSERT
Michal Taborsky <[EMAIL PROTECTED]> writes: > I've read the discussion in "Trying to minimize the impact of > checkpoints" thread and I get it, that there is nothing I can do about > it. Well, we'll have to live with that, at least until 7.5. You could experiment with the checkpoint interval (checkpoint_timeout). A shorter interval will mean more total I/O (the same page will get written out more often) but it should reduce the amount of I/O done by any one checkpoint. You might find that the extra overhead is worth it to reduce the spikes. But 7.5 should provide a much better answer, yes. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Slow INSERT
Tom Lane wrote: Actually, the simpler theory is that the slowdown is caused by background checkpoint operations. Now a checkpoint would slow *everything* down not only this one insert, so maybe that's not the right answer either, but it's my next idea. You could check this to some extent by manually issuing a CHECKPOINT command and seeing if you get an insert hiccup. Note though that closely spaced checkpoints will have less effect, because less I/O will be triggered when not much has changed since the last one. So you'd want to wait a bit between experiments. Aha! This is really the case. I've let the test run and issued manual CHECKPOINT command. The command itself took about 3 secs and during that time I had some slow INSERTS. So we know the reason. I've read the discussion in "Trying to minimize the impact of checkpoints" thread and I get it, that there is nothing I can do about it. Well, we'll have to live with that, at least until 7.5. Thanks of the help all the same. -- Michal Taborsky http://www.taborsky.cz ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Slow INSERT
Tom Lane wrote: It's hard to see how inserting to such a simple table would be slow. Indeed. Is the number of inserts between slowdowns perfectly repeatable? My first thought is that the fast case is associated with inserting onto a page that is the same one last inserted to, and the slow case is associated with finding a new page to insert onto (which, given that you never UPDATE or DELETE, will always mean extending the file). Given that the table rows are fixed width, the number of rows that fit on a page should be constant, so this theory cannot be right if the number of inserts between slowdowns varies. I ran some tests to support this hypothesis. Every 500th insert is a tad slower, but it is insignificant (normally the INSERT lasts 1.5ms, every 500th is 9ms). During my tests (10 runs of 1000 INSERTS) I had experienced only one "slow" insert (2000ms). It is clearly caused by other processes running on this server, but such degradation of performance is highly suspicious, because the server very rarely goes over load 1.0. Just for the record, it is FreeBSD 4.9 and the system never swaps. Also, are all the inserts being issued by the same server process, or are they scattered across multiple processes? I'm not sure this theory holds water unless all the inserts are done in the same process. Nope. It is a webserver, so these requests are pushed through several persistent connections (20-30, depends on current load). This insert occurs only once per pageload. -- Michal Taborsky http://www.taborsky.cz ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Slow INSERT
=?ISO-8859-2?Q?Michal_T=E1borsk=FD?= <[EMAIL PROTECTED]> writes: > I am experiencing rather slow INSERTs on loaded server. > ... There are no indices, triggers or constraints attached to it. It's hard to see how inserting to such a simple table would be slow. > Sometimes, it takes as long as 1300ms! Other queries are quite swift, > even compplex SELECTS and most of the INSERTS run fast. But occasionally > (every 50th or 100th INSERT) it takes forever (and stalls the webpage > from loading). Is the number of inserts between slowdowns perfectly repeatable? My first thought is that the fast case is associated with inserting onto a page that is the same one last inserted to, and the slow case is associated with finding a new page to insert onto (which, given that you never UPDATE or DELETE, will always mean extending the file). Given that the table rows are fixed width, the number of rows that fit on a page should be constant, so this theory cannot be right if the number of inserts between slowdowns varies. Also, are all the inserts being issued by the same server process, or are they scattered across multiple processes? I'm not sure this theory holds water unless all the inserts are done in the same process. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
