Thread: BUG #6269: Anomaly detection
The following bug has been logged online: Bug reference: 6269 Logged by: Paul Stapersma Email address: paul.stapersma@gmail.com PostgreSQL version: 8.3.3 Operating system: Mac OS X Version 10.6.8 Description: Anomaly detection Details: Dear reader, For a project at my University, we compared PostgreSQL with MySQL's InnoDB. In this research, we found several cases in which anomalies where detected in Isolation levels that guaranteed not to have these anomalies. In short summary: - we detected non-repeatable reads in the repeatable read isolation level - we detected non-repeatable reads in the serializable isolation level - we detected phantoms in the serializable isolation level - we detected lost updates in the repeatable read isolation level - we detected lost updates in the serializable isolation level Furthermore, we detected differences between Read Committed and Read Uncommitted and differences between Serializable and Repeatable Read which is in contrast to the documentation. A full report on our findings can be found here: http://dl.dropbox.com/u/19316575/report.pdf Friendly regards, Paul Stapersma
"Paul Stapersma" <paul.stapersma@gmail.com> writes:
> For a project at my University, we compared PostgreSQL with MySQL's InnoDB.
> In this research, we found several cases in which anomalies where detected
> in Isolation levels that guaranteed not to have these anomalies.
> In short summary:
> - we detected non-repeatable reads in the repeatable read isolation level
> - we detected non-repeatable reads in the serializable isolation level
> - we detected phantoms in the serializable isolation level
> - we detected lost updates in the repeatable read isolation level
> - we detected lost updates in the serializable isolation level
There are some known (and documented) issues with serializable mode not
being truly serializable; that should be resolved by 9.1. It's too
bad you were testing 8.3.
> Furthermore, we detected differences between Read Committed and Read
> Uncommitted and differences between Serializable and Repeatable Read which
> is in contrast to the documentation.
Quite honestly, that statement makes me assume that your testing methods
are broken. It's entirely impossible for those modes to behave
differently in Postgres, because there's no difference (in 8.3 anyway).
regards, tom lane
On 25.10.2011 12:51, Paul Stapersma wrote: > PostgreSQL version: 8.3.3 That is a pretty old version. At least you should use the latest version in the 8.3 series, which is currently 8.3.16. I'm not sure if there's been any isolation-related bug fixes since 8.3.3, but I don't see any reason not to test using the latest minor version. Even better, use the latest and greated version, 9.1.1. 9.1 is a very interesting release for the stuff you're doing, because it is the first version to implement the Serializable level correctly, which is different from the Snapshot Isolation we had previously. It uses a technique called Serializable Snapshot Isolation. > For a project at my University, we compared PostgreSQL with MySQL's InnoDB. > In this research, we found several cases in which anomalies where detected > in Isolation levels that guaranteed not to have these anomalies. > > In short summary: > - we detected non-repeatable reads in the repeatable read isolation level > - we detected non-repeatable reads in the serializable isolation level > - we detected phantoms in the serializable isolation level > - we detected lost updates in the repeatable read isolation level > - we detected lost updates in the serializable isolation level > > Furthermore, we detected differences between Read Committed and Read > Uncommitted and differences between Serializable and Repeatable Read which > is in contrast to the documentation. That is all pretty hard to believe, to be honest. I'd suggest that you check you testing methodology. If you can provide a short self-contained test script to demonstrate one of those unexpected anomalies, me or someone else on the mailing list can take a closer look. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
"Paul Stapersma" <paul.stapersma@gmail.com> wrote: > PostgreSQL version: 8.3.3 > A full report on our findings can be found here: > http://dl.dropbox.com/u/19316575/report.pdf I would like to independently run your tests, but I don't see anything in the paper to describe the table(s) used or how they were populated. Could you provide more information or a link? Without a bit more information, it's impossible to tell whether some of the more surprising results were the result of a flaw in your methodology, a bug in 8.3.3 which may have been fixed in a release over three years ago, or a bug still present in PostgreSQL. Thanks, -Kevin
On Tue, Oct 25, 2011 at 10:51 AM, Paul Stapersma <paul.stapersma@gmail.com> wrote: > PostgreSQL version: 8.3.3 > For a project at my University, we compared PostgreSQL with MySQL's InnoD= B. > In this research, we found several cases in which anomalies where detected > in Isolation levels that guaranteed not to have these anomalies. It looks to me that you've made an honest attempt at this comparison. Thank you for noting points such as "PostgreSQL=92s overall performance was for all isolation levels better in comparison to InnoDB=92s performance." To allow this type of study to be widely accepted, you need to declare who you are and where you are from. And to allow your results to be confirmed. Accountability and verifiability are essential aspects of good science. I would also like you to state your funding source, so that it is clear whether or not marketing funds inspire or reward such studies. Full disclosure is appreciated. Using InnoDB 5.0 and PostgreSQL 8.3 isn't very useful and I'm surprised you haven't used the latest production releases of both products, especially when we have ground breaking features in exactly this area in the latest release. Perhaps I misunderstand - you don't actually say which release of InnoDB you use, though you have URLs linking to both 5.0 and 5.1 versions. As others have observed, some other points need more careful examination and it would be good to provide exact details of your tests to allow any such results to be reproduced. If problems do exist, we will be very interested. There are many people here that would be happy to help if you find problems, so please don't be put off by these responses to your work. --=20 =A0Simon Riggs=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 http:/= /www.2ndQuadrant.com/ =A0PostgreSQL Development, 24x7 Support, Training & Services
The report is based on a java application that can be found here: http://dl.dropbox.com/u/19316575/dbtp.zip. This zip-file also includes an SQL file that creates the used tables and the queries that are executed. I put a README-file in the zip with installation instructions. Most easy to do the tests: extract the zip, download and install eclipse, create a project based on a folder where the extracted dbtp.zip should be referenced and follow the instructions in the README-file. I'll try redo the tests with the newest version of Postgres myself as soon as I have the time. -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-6269-Anomaly-detection-tp4936233p4938990.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
goudvis <paul.stapersma@gmail.com> wrote: > The report is based on a java application that can be found here: > http://dl.dropbox.com/u/19316575/dbtp.zip. This zip-file also > includes an SQL file that creates the used tables and the queries > that are executed. > > I put a README-file in the zip with installation instructions. > Most easy to do the tests: extract the zip, download and install > eclipse, create a project based on a folder where the extracted > dbtp.zip should be referenced and follow the instructions in the > README-file. Thank you very much! I will review and attempt to replicate your results. > I'll try redo the tests with the newest version of Postgres myself > as soon as I have the time. I've read through the paper more closely now, and recommend that you review the concurrency control section of the PostgreSQL docs to better understand some of the timings you found surprising. http://www.postgresql.org/docs/9.1/interactive/mvcc.html Basically, you seemed to be assuming that concurrency control is handled through 2 Phase Locking (2PL) with Strict 2 Phase Locking (S2PL) used for serializable transaction isolation. This is not at all true in PostgreSQL, and as I understand it InnoDB uses S2PL at the serializable transaction isolation level, and (like PostgreSQL) MVCC at other levels. This would account for much worse performance at the InnoDB serializable level than other levels. It would also explain why repeatable read is generally faster than read committed -- it only needs to build one snapshot per transaction, not one per statement. To understand the new implementation of serializable isolation in PostgreSQL version 9.1, you might want to read the Wiki page which was used to manage development of the feature. http://wiki.postgresql.org/wiki/Serializable The Cahill doctoral thesis is very much worth reading, and many of the papers referenced by it are also excellent. http://hdl.handle.net/2123/5353 Thanks again. I hope to get back to you within a few days. -Kevin
I executed the tests on PostgreSQL 9.1.1. The only result not expected was phantoms in the serializable isolation level. All other results were as expected. -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-6269-Anomaly-detection-tp4936233p4940614.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
ps. Tomorrow I will discuss my approach with my supervisor. In case of a wrong approach or mistake, I'll post it. -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-6269-Anomaly-detection-tp4936233p4940716.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
A few updates from my side: Kevin helped me find two bugs in my test suite. The first: the test suite had a syntax error in setting the isolation level, which resulted in not setting an isolation level at all. Secondly, I made a mistake in the phantom detection code, which resulted in detecting phantoms when there where no phantoms. Afterwards, all tests where repeated on PostgreSQL 9.1.1 with no strange results by both Kevin and me. The results in my earlier posted report can therefore be marked as incorrect. Thanks for the support and replies on my work. -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-6269-Anomaly-detection-tp4936233p4944490.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
On Thu, Oct 27, 2011 at 7:02 PM, goudvis <paul.stapersma@gmail.com> wrote: > A few updates from my side: > Kevin helped me find two bugs in my test suite. The first: the test suite > had a syntax error in setting the isolation level, which resulted in not > setting an isolation level at all. Secondly, I made a mistake in the phantom > detection code, which resulted in detecting phantoms when there where no > phantoms. > > Afterwards, all tests where repeated on PostgreSQL 9.1.1 with no strange > results by both Kevin and me. > > The results in my earlier posted report can therefore be marked as > incorrect. > > Thanks for the support and replies on my work. I would be curious to see (updated, corrected) results on older versions. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas wrote: > > I would be curious to see (updated, corrected) results on older versions. > If I am correct, Kevin Grittner is writing a review of the code and the testing methods. I think it would be wise to wait for the outcome of this. Afterwards, I could post the code and the execution instructions for anyone who wants to test his or her system. Another approach is to do the testing myself, but I don't know when I have the time. -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-6269-Anomaly-detection-tp4936233p4946747.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
goudvis <paul.stapersma@gmail.com> wrote: > Robert Haas wrote: >> >> I would be curious to see (updated, corrected) results on older >> versions. > > If I am correct, Kevin Grittner is writing a review of the code > and the testing methods. I think it would be wise to wait for the > outcome of this. Afterwards, I could post the code and the > execution instructions for anyone who wants to test his or her > system. Another approach is to do the testing myself, but I don't > know when I have the time. Actually, since we (mostly you) found explanations for all the disallowed phenomena, with no PostgreSQL anomalies showing when using the corrected test code, I was going to call it done. Well, except that I think some of your tests are interesting enough to ask whether it was OK to try to integrate some of the related SQL into C-based tests in the optional PostgreSQL regression tests. (They probably run too long to include in the main "make check" set.) If I could get them running in that environment, I think they could stress a different-enough set of code paths to be worth including. Regarding the Java code used in your tests, the only issues I found which I didn't mention to you off list were stylistic or performance-related. Since the performance issues would apply to both MySQL and PostgreSQL, I'm not too worried about them in terms of comparative numbers. I anyone wanted to consider the absolute numbers as significant, I could pass along a few tips. -Kevin
Kevin Grittner wrote: > > Actually, since we (mostly you) found explanations for all the > disallowed phenomena, with no PostgreSQL anomalies showing when > using the corrected test code, I was going to call it done. Well, > except that I think some of your tests are interesting enough to ask > whether it was OK to try to integrate some of the related SQL into > C-based tests in the optional PostgreSQL regression tests. (They > probably run too long to include in the main "make check" set.) > If I could get them running in that environment, I think they could > stress a different-enough set of code paths to be worth including. > No problem at all to include my tests in the regression tests. I really like the fact that this project of mine can contribute to PostgreSQL. -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-6269-Anomaly-detection-tp4936233p4948383.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.