I am sure many readers will read the headline of this post and immediately think of pgTAP, which has become the de facto way to write unit tests in PostgreSQL db's. The sad part is that we on the LedgerSMB team have not yet gotten around to porting our unit tests to pgTAP. We have a slightly different approach which works in a similar way. But this post isn't really about frameworks, but rather applications of the concept of unit testing.
Just to give folks some background, we started adding database-level unit tests to LedgerSMB back in 2007, at a time when pgTAP was still an alpha, and at the time we thought that we should wait for pgTAP to prove itself (pun intended) before relying on it as a testing infrastructure. It has certainly matured since then. Our approach instead is to write unit tests in SQL which write to a temporary table called test_result. At the end of the test run, we select from test_result, provide a report of successes and failures, and roll back the transaction. Of course if you encounter an exception part way through, your test cases will die and you won't have access to the test results until it is able to run through to completion.
The test results are then parsed in Perl and the results fed back through Perl's test harness. This approach has a few advantages and a few disadvantages compared to other fraeworks. I like the fact that all the results are visible at the end. It makes debugging easy when there is a failure because you can look at the full pattern of successes and failures and then scroll back to find any debugging information that the developer felt like outputting between test cases.
However what I really want to talk about is how these are or can be used in a mission critical environment. I think most people would agree that software that tracks money is mission critical at least in terms of its data integrity. If the data integrity suffers, everything in the business can fall apart quite quickly. So ensuring data integrity for accounting software that may be customized by third parties is both a hard job and one that is absolutely necessary.
One key aspect of unit tests in this case is that they don't provide relevant lasting impacts on the database they are run against. While there are some exceptions that are outside transactional control (sequences and the like) usually we don't care so much as to whether those are in the state they would have been for but for the test run., The ability to run unit tests on a live system without the possibility of screwing something up makes customization and rapid development possible in environments that it would not otherwise be. (In actuality we have conventions to offer multiple layers of protection against data corruption. For example serial values are typically assigned in the test case from a range of negative numbers, thus unlikely to conflict with existing real values. However, this is nothing compared to the ability to simply roll back the transactions.)
Consequently you can use unit tests on a live system as a measure of ensuring that everything is ok. A very useful corollary here is that you can use unit tests as a troubleshooting mechanism. Something not working as expected? Maybe the first thing that should be done is running the unit tests and see if anything fails. You can thus add all sorts of checks that are useful for support and deployment purposes.
One key example here is the fact that behavior of LedgerSMB's query mapper is undefined where the target function is overloaded and no arguments defined. Consequently it is very important to know which functions are overloaded that shouldn't be. We actually have a test for overloaded functions, and we run this typically against misbehaving databases to see if there are issues there.
Occasionally I have recommended to customers that they wait to install add-ons until unit tests have been written. This has never yet been the wrong call.
I am a firm believer in unit testing in the db. They are just useful and not only in a QA environment. These tests are just as useful as general diagnostic tools frameworks. Therefore I think every database application should be using them.
Thursday, May 31, 2012
Monday, May 21, 2012
LedgerSMB now in Debian!
As Robert James Clay announced today:
The 1.3.15-2 package for LedgerSMB has been accepted into Debian
unstable. I expect that version of the package to migrate to Debian
testing (wheezy) by the end of this month.
LedgerSMB Debian source package page:
http://packages.qa.debian.org/l/ledgersmb.html
Besides becoming available in the Debian repositories, information
about downloading the current package manually is also available at:
http://packages.debian.org/sid/all/ledgersmb/download
Saturday, May 12, 2012
Thoughts on VoltDB and performance vs flexibility
VoltDB is the latest project of Mike Stonebreaker (original founder of Postgres) aimed at very high performance OLTP. It is fairly clearly a niche product however, and not really usable in the ERP space for reasons I will discuss below.
Stonebreaker's own presentations (one example, at 11:25) suggest he is aware of this issue given his triangle framework.
In the vertices of the triangle he places NoSQL, high performance OLTP (like VoltDB) and column-oriented databases, while in the second, as lower performance generalists, what he calls the legacy databases or elephants are in the middle. The argument is that you can get a significant performance boost by specializing your systems beyond what you could when you have every system doing everything. This is a fair argument to a point, but the implementation details show that it is true only in some areas.
Stonebreaker's analysis of processing time (see above presentation, at 14:36) in traditional databases places virtually all processing time in four areas, namely buffer management, locking, thread locks/semaphores, and recovery tasks, and he suggests that in order to get high degrees of performance one must eliminate these tasks. This requires, however, rethinking how we address concurrency (the locking issues) and durability (recovery and disk-based storage). The VoltDB approach is to get rid of concurrency entirely and see durability as a network, rather than a system, property. Concurrency elimination accepted because the db is fast enough that queries can be run one at a time, and intraquery parallelism can be used instead of interquery parallelism. However, this imposes significant limitations on the database because it means that every transaction is limited to a single query. You can't do round-tripping in your transactions because this would impose locking requirements on the database.
This approach works very well for certain kinds of processing, such as consuming rapid data feeds and then feeding that information into a data warehouse at specified intervals., However one thing that is generally missing from the discussion is that the more complex the application, the more general the database solution needs to be. One solution might be to separate data entry from reporting and use multiple tools, but who wants to pull their trial balance from a different system than they enter invoices on? Since ERP systems usually tightly integrate decision support and OLTP, there isn't really room here to use specialized databases like VoltDB.
As the database market has expanded, it has created niches for products like VoltDB. These niches may indeed grow with time. However, I think it is incorrect to treat generalists as legacy, outdated approaches.
In essence VoltDB typifies what I call a specialism paradox, namely that to perform well in a specialist niche one must give up generalist solutions. It is true that sometimes things improve and supplant older systems but at least as often they only do when the systems are not vastly more complex than the ones they are replacing. For example, Stonebreaker's comparison of high performance OLTP to the shift from cable-operated digging excavation equipment to hydrolics misses the fact that hydrolic systems are not that much more complex than cables and such. There is some additional complexity, but it isn't a huge gap. A better comparison might be comparing semi trucks to race cars and locomotives. These are all highly engineered systems, but the basic design considerations are very different, and specialization comes at the cost of flexibility. You will never see race cars hauling twenty-foot containers of furniture cross-country, and you will never see travelling along our nation's highways to reach out-of-the-way towns. The semi however will never pull the loads that the locomotive can, and will never win the Indie 500...... The semi truck, however, can go fast enough, and pull the loads it needs to pull.....
Stonebreaker's own presentations (one example, at 11:25) suggest he is aware of this issue given his triangle framework.
In the vertices of the triangle he places NoSQL, high performance OLTP (like VoltDB) and column-oriented databases, while in the second, as lower performance generalists, what he calls the legacy databases or elephants are in the middle. The argument is that you can get a significant performance boost by specializing your systems beyond what you could when you have every system doing everything. This is a fair argument to a point, but the implementation details show that it is true only in some areas.
Stonebreaker's analysis of processing time (see above presentation, at 14:36) in traditional databases places virtually all processing time in four areas, namely buffer management, locking, thread locks/semaphores, and recovery tasks, and he suggests that in order to get high degrees of performance one must eliminate these tasks. This requires, however, rethinking how we address concurrency (the locking issues) and durability (recovery and disk-based storage). The VoltDB approach is to get rid of concurrency entirely and see durability as a network, rather than a system, property. Concurrency elimination accepted because the db is fast enough that queries can be run one at a time, and intraquery parallelism can be used instead of interquery parallelism. However, this imposes significant limitations on the database because it means that every transaction is limited to a single query. You can't do round-tripping in your transactions because this would impose locking requirements on the database.
This approach works very well for certain kinds of processing, such as consuming rapid data feeds and then feeding that information into a data warehouse at specified intervals., However one thing that is generally missing from the discussion is that the more complex the application, the more general the database solution needs to be. One solution might be to separate data entry from reporting and use multiple tools, but who wants to pull their trial balance from a different system than they enter invoices on? Since ERP systems usually tightly integrate decision support and OLTP, there isn't really room here to use specialized databases like VoltDB.
As the database market has expanded, it has created niches for products like VoltDB. These niches may indeed grow with time. However, I think it is incorrect to treat generalists as legacy, outdated approaches.
In essence VoltDB typifies what I call a specialism paradox, namely that to perform well in a specialist niche one must give up generalist solutions. It is true that sometimes things improve and supplant older systems but at least as often they only do when the systems are not vastly more complex than the ones they are replacing. For example, Stonebreaker's comparison of high performance OLTP to the shift from cable-operated digging excavation equipment to hydrolics misses the fact that hydrolic systems are not that much more complex than cables and such. There is some additional complexity, but it isn't a huge gap. A better comparison might be comparing semi trucks to race cars and locomotives. These are all highly engineered systems, but the basic design considerations are very different, and specialization comes at the cost of flexibility. You will never see race cars hauling twenty-foot containers of furniture cross-country, and you will never see travelling along our nation's highways to reach out-of-the-way towns. The semi however will never pull the loads that the locomotive can, and will never win the Indie 500...... The semi truck, however, can go fast enough, and pull the loads it needs to pull.....
Subscribe to:
Posts (Atom)