tag:blogger.com,1999:blog-20038672.post3894785987764305435..comments2024-03-12T17:48:06.493-04:00Comments on Robert Haas: MySQL vs. PostgreSQL, Part 2: VACUUM vs. PurgeRobert Haashttp://www.blogger.com/profile/08393677427643988650noreply@blogger.comBlogger21125tag:blogger.com,1999:blog-20038672.post-64146503717226035482011-02-03T17:30:34.198-05:002011-02-03T17:30:34.198-05:00Grant, MySQL rollback segments don't have a si...Grant, MySQL rollback segments don't have a size limit and will grow to fill the hard drive if the server never catches up. I've seen it about twice in six years and we added an option innodb_max_purge_lag to increase priority for purge after the first of them. Now the independent purge thread should make that even less likely, though setting innodb_max_purge_lag to 10 million or 100 million is still of possible use to cover cases where it might not do the job. Benchmarks.<br /><br />All of this is irrelevant for almost all users. It's just handled automatically and that works. Production servers tend to have far more banal issues. The usual sort of query optimisation or badly wrong settings. Knowing that that's all that's wrong and eliminating the rare cases is where the knowledge comes in.<br /><br />This is just my opinion, not the official view of the company. Talk to the press people if you want the latter.<br /><br />James Day, MySQL Principal Support Engineer, OracleAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-197911627024599292011-02-03T13:29:43.095-05:002011-02-03T13:29:43.095-05:00@Grant: While the concept is similar, InnoDB rollb...@Grant: While the concept is similar, InnoDB rollback segments aren't managed the same as Oracle. They are stored inside of a tablespace and can grow and contract as needed. It is much more automatic and normally doesn't have any of the traditional rollback segment related Oracle problems. There is no 'snapshot is too old' type error in InnoDB.Harrisonhttps://www.blogger.com/profile/05318054903467929570noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-25488460845241003092011-02-02T17:55:31.913-05:002011-02-02T17:55:31.913-05:00Oracle rollback segments have some issues. If the...Oracle rollback segments have some issues. If they are too small then large transactions will fail because they will run out of space. If they are too large you get other problems.<br /><br />Oracle has switched from Rollback Segments to an Undo Tablespace, which I understand gets rid of these issues.artsrchttps://www.blogger.com/profile/08224958231370433683noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-50390003150040751472011-02-02T16:39:00.749-05:002011-02-02T16:39:00.749-05:00I have not had experience in managing MySQL, but i...I have not had experience in managing MySQL, but it uses the same technique as Oracle, or rollback segments.<br /><br />Here is what I have found:<br />1) Oracle rollback segments tend to take manual management, and tend to cause issues with large or long running queries, either running out of rollback space, or timing out giving a snapshot too old error.<br /><br />2) The PostgreSQL method tends to cause the core table space to grow over time (vacuum, even full seems to miss something).<br /><br />Both methods have their issues. The Oracle method tends to be more controlled on how much disk is used, but takes more administrator intervention. PostgreSQL is easier to "set and forget" but does not seem to clean up after itself as well. With the price of disk and labor lately, I guess that automation wins out, but it is close.Grant Johnsonhttps://www.blogger.com/profile/06772023212277928936noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-32593166443592046262011-02-02T15:30:47.268-05:002011-02-02T15:30:47.268-05:00@Baron, Facebook does not use multiple purge thre...@Baron, Facebook does not use multiple purge threads. We have seen a few cases where it might have been useful, but normally those point to a bigger issue which we fix (reallly long transactions and saturated disk I/O).<br /><br />@Robert, It seems more likely that an undo segment would remain in memory compared to large portions of indexes which need to be scanned with vacuum. I agree that it doesn't make total sense to discuss cache use with regards to this though.<br /><br />The size of the undo segments remain roughly fixed based on transaction length and rate of changes, not based on the size of the table. Index size naturally grows with table size. <br /><br />The visibility map optimization did a lot to make vacuum more manageable, the primary thing that doesn't seem to scale well now is the full index scans now.Harrisonhttps://www.blogger.com/profile/05318054903467929570noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-13003385798434561482011-02-02T13:23:28.753-05:002011-02-02T13:23:28.753-05:00Robert, Percona Server offers multiple threads bec...Robert, Percona Server offers multiple threads because at a minimum, we don't like hard-coding things; history has proven that "oh, X is enough for this" is wrong. But again, some of my colleagues who actually created that functionality might have needed multiple threads, I don't know. I just know that the really grievous problems I've seen were solved with one dedicated thread.<br /><br />One thing I'd like to caution about analysis of the amount of work it takes to make a change in InnoDB. It has had something called the "insert buffer" forever, and in recent releases it's changed to the "change buffer" because it delays the work involved in more than just inserts. This design can significantly reduce the amount of work required.<br /><br />About heaps and b-trees and CTIDs and such, there is one bit of trivia that I want to mention; each leaf node of InnoDB's clustered index (which is the table, and is a b+tree) actually contains a heap of records. So it's a b-tree until you get to the leaf node (== page), and then the records on the page are organized in a heap. Hope that makes sense.Baronhttps://www.blogger.com/profile/01621441847303652718noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-49858678879677077052011-02-02T13:05:54.641-05:002011-02-02T13:05:54.641-05:00@Jeff Davis: AIUI, it scans the table in key order...@Jeff Davis: AIUI, it scans the table in key order, rather than physical order. See the "part 1" post in this series. If the tuple doesn't fit into the page, the page must be split - but that's OK, because the secondary indexes point back to the PK, not the physical location (CTID) as they do in PostgreSQL.Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-14866579548789985032011-02-02T12:52:07.705-05:002011-02-02T12:52:07.705-05:00It seems like the InnoDB approach would complicate...It seems like the InnoDB approach would complicate reads, as well. I don't know how it works, but I assume it has to be careful not to miss tuples in a scan as they are being moved around.<br /><br />Also, what if the old tuple fits in the page and the new one doesn't? Does it just do a DELETE/INSERT instead? And if so, does it have a third version in the rollback segment, or does it optimize that away?Jeff Davishttp://thoughts.j-davis.comnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-48323211014548692432011-02-02T12:49:45.825-05:002011-02-02T12:49:45.825-05:00@Sergei: After thinking about this for a bit, I be...@Sergei: After thinking about this for a bit, I believe that in the case of a non-HOT update your analysis is basically correct, but for a HOT update PostgreSQL only does two writes, since there's no index update in that case.<br /><br />I'm reluctant to rely on this path of analysis for very much, though, because there are a lot of other things that go into performance, and really the only way to know what's going to work better for your workload is to try it. The depth of the index isn't necessarily the same in PostgreSQL and InnoDB (which one is deeper? I don't know, and it may depend on the width of the primary key relative to the table row), and the chances that an insert will require a page split are probably also different (and I'm not sure which one will need to do that more often in real-world workloads). All of these factors affect how much work actually will need to get done in a particular case.Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-62287422301504509992011-02-02T12:24:16.771-05:002011-02-02T12:24:16.771-05:00Robert, it was this statement that caught my atten...Robert, it was this statement that caught my attention:<br /><br />> One small downside of this approach is that performing an update means writing two tuples - the old one must be copied to the undo tablespace, and the new one must be written in its place.<br /><br />Let's say I have a simple schema:<br /> create table foo(a int primary key);<br /><br />In InnoDB the write goes into the clustered index, redo log, and the rollback segment.<br /><br />In PG, the write goes into the heap, the WAL, and the index.<br /><br />Same number of writes.Sergeihttps://www.blogger.com/profile/12505242855655371767noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-39026312352555019682011-02-02T12:09:47.388-05:002011-02-02T12:09:47.388-05:00@Harrison: Understood. I was actually referring t...@Harrison: Understood. I was actually referring to CPU and buffer management cost, not I/O cost. It's probably small enough not to be terribly noticeable, but it can't be free; and I do think that avoiding the need for that sort of copying is one reason for the PostgreSQL design, though it certainly doesn't mean that the PostgreSQL design is better; I'm not convinced that it is.<br /><br />A small PostgreSQL table can get vacuumed before it ever leaves the buffer pool, too, but I don't think small tables are a major concern in either system any more. It's the big ones that cause problems - where you need to evict the pages and then eventually read some or all of them back in for cleanup.<br /><br />@Baron: Interesting. So why does Percona server offer multiple threads?<br /><br />@Sergei: I'm not sure that has much to do with this particular issue, although I did discuss it in my earlier post.<br /><br />@Martin LeBlanc: Thanks, fixed.Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-46195951959277771442011-02-02T11:07:12.990-05:002011-02-02T11:07:12.990-05:00You got a little typo:
"MySQL performs perfo...You got a little typo:<br /><br />"MySQL performs performs purges ..."Martin LeBlanchttps://www.blogger.com/profile/06626813650734356951noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-19122460807228947062011-02-02T10:38:55.458-05:002011-02-02T10:38:55.458-05:00Except InnoDB clusters the base table by primary k...Except InnoDB clusters the base table by primary key and PG does not. So if you want an access path across the primary key in PG you manage the heap and the index, while in InnoDB it's just the index.Sergeihttps://www.blogger.com/profile/12505242855655371767noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-56107337549599107442011-02-02T10:37:29.849-05:002011-02-02T10:37:29.849-05:00For the vast majority of cases, single-threaded pu...For the vast majority of cases, single-threaded purge isn't even a problem in InnoDB. What is (was) the problem was when purge was done as an intermittent task by InnoDB's main thread, among several other tasks it did in a loop. Having a dedicated purge thread, even if it's only one thread, is sufficient for every case I've ever seen personally. Harrison, does Facebook use multiple purge threads?Baronhttps://www.blogger.com/profile/01621441847303652718noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-23429264333919495932011-02-02T09:59:26.614-05:002011-02-02T09:59:26.614-05:00In InnoDB the additional IO is normally just theor...In InnoDB the additional IO is normally just theoretical. Modifications go through the InnoDB buffer pool, and assuming you don't have extremely long transactions, purge will often remove the record before it has actually been written to disk. <br /><br />If there is a really long transaction, then it might not remain in the buffer pool. Cases like this is where the single threaded purge thread can really hurt since purge becomes disk bound and very slow at catching up.Harrisonhttps://www.blogger.com/profile/05318054903467929570noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-72669592265401053042011-02-01T15:28:06.801-05:002011-02-01T15:28:06.801-05:00I believe that the Updates are most important when...I believe that the Updates are most important when you use it a lot, and Vacuum is more important in environments where databases are used with many terabytes of data and use them 24 hours a day because Vacuum can cause slow when implemented on tables too large. In environments that use the database between 22 or fewer hours per day without hesitation I would choose faster updates. A scheduled task for the Vacuum be made within two hours left over each day. But if you never bothered by Vacuum, then automatically schedule a task to be performed continuously and automatically the vacuum on larger tables. All this also depends on the server if you use a storage system fast or slow.Hugo Rafael Lesme Marquezhttp://www.datacentersystems.org/index.htmnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-65403519081014636362011-02-01T15:10:18.779-05:002011-02-01T15:10:18.779-05:00@Hugo Rafael Lesme Marquez: I'm not sure that ...@Hugo Rafael Lesme Marquez: I'm not sure that question has one right answer.Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-74633480500776633672011-02-01T15:09:33.558-05:002011-02-01T15:09:33.558-05:00@Anonymous: Thanks.
@Yang: It's not exactly a...@Anonymous: Thanks.<br /><br />@Yang: It's not exactly a free list, but it does mark the space as available for reuse. A HOT prune can do that part on the fly to a limited degree; however, there's still the problem of reclaiming index entries. It's hard to do the whole thing on the fly due to MVCC visibility rules - the tuple actually becomes dead when the last snapshot that can see it is released, which is totally disconnected from the event of marking the tuple deleted.Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-18612033365221992572011-02-01T15:07:19.285-05:002011-02-01T15:07:19.285-05:00PostgreSQL makes fastest updates and MySQL makes t...PostgreSQL makes fastest updates and MySQL makes the PURGE fastest than the Vacuum , what is more important to be fast? an Update o Vacuum. Thats the questionHugo Rafael Lesme Marquezhttp://www.datacentersystems.org/index.htmnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-68212718934599176132011-02-01T14:19:45.217-05:002011-02-01T14:19:45.217-05:00In InnoDB, is the "free list" of space m...In InnoDB, is the "free list" of space marked deleted just kept around in the rollback segment for as long as the space is there? Also, are rows the same size, or else does InnoDB prefer to overwrite in-place? Is there a fragmentation issue? A separate compaction process?<br /><br />Also, for PG: (non-FULL) VACUUM just scans to build a free list, right? Could this be maintained on-the-fly instead?Yanghttp://yz.mit.edu/noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-18612387757086644602011-02-01T11:31:04.314-05:002011-02-01T11:31:04.314-05:00Nice write up Robert. Good to see an up to date, c...Nice write up Robert. Good to see an up to date, clear and consciously unbiased comparison of the two architectures. Appreciate the anonymous comment option too.<br /><br />-eyecueAnonymousnoreply@blogger.com