Re: Best database for web backend on Linux?
--------------EEFFDDB86DC8134BCB0D2FD0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Christopher , Ok, Lets look at this in some detail. To do this, I'll pull a case study out of my hat from a customer I've consulted with in the past (a major integrator here in Plymouth MI, U.S.A.). These guys had a system where they loaded a bunch of scanned documents into a "job". This documents consisted of the image of the document (both front and back) along with its ASCII file that represented an effort by the machine to convert a portion of the image to text. This portion of the equipment we referred to as "real-time" and it worked pretty well. The other portion of the equipment was designed as a workcell that fixed the documents the OCR software couldn't handle. These jobs then generally contained 100 to 300 documents of which roughly 50% were correctly OCR'd and marked as "done". The other 50% were marked as needing "keying". The software as designed read the "jobs" and entered a record in their workcell database that represented every document in the job. The way their workcell was set up, there could be many jobs that needed work, but only one person could work on a job at a time. The way they set up the database was one table containing a record for each of the jobs on the system, and a "volatile" table for each of the jobs containing a record for each of the documents in that job. Just to sum that up another way, if you had three jobs each with 300 documents, you would have a total of 4 tables, one listing the jobs on the system, and 3 more for each of the jobs each containing 300 records. You're probably thinking that they were using the jobs table as a status table, and you're right. You're also probably thinking that they didn't have row level locking with this database and that would seriously limit the speed with which they could process their documents, and you're right again. To make things more interesting, the "green" designer who put together the demo system for this production system used cursors in VB <sigh>, what a mess. Suffice it to say that the performance of this system was dismally low. It was supposed to work with 30 or more operators, and it could barely keep up with 8. The fix to the system, which they are in the process of implementing, is to break down the functionality of their status table into its component parts. The status table really performs two tasks: 1. A simple (for the designer) network based semaphore. 2. A source of persistent storage of state in case of a client (or server) crash, which had a habit of happening all too often (NT don't you know). If you take a look at the first item above you will note that a semaphore also has two functions, that of query and that of update. The solution is to create one table for persistent store and query and another for update. This has the effect of partitioning the HOT process of querying from the relatively cooler (but more damaging) process of updating. In order to make this work, you must also make sure that no updates are ever made to the persistent store except by the database engine (in an environment with many developers this often means turning off update privileges). As a further constraint, no cursors are to be allowed on the update table, only singleton updates or inserts (and of course no selects). So what does this accomplish? Well, it limits the access to the hot table (the one that everybody is polling for status) to queries only, except for the database engine, thus limiting the number and duration of table locks on the "persistent networked semaphore". Because we have done a little extra work to limit locking in this way, the background tasks that require speedy access to the state information can proceed at a much faster rate because there is now much more bandwidth on that table. The people trying to do updates also benefit because there is less traffic on the physical table they are trying to update. Since we also limited them to non-cursor singleton updates and inserts, any locks that do occur clear quickly and can easily be recovered from by retrying. Your milage may vary with your particular application, but suffice it to say the reason why Sybase resisted row-level-locking so long was because it was never necessary and that inclusion of it in any database will inevitably incur overhead. Apparently, Sybase's unexperienced database/application designer customers and their internal marketing department won out. -- ----==-- _ / / \\ ---==---(_)__ __ ____ __ / / /\\ \\ --==---/ / _ \\/ // /\\ \\/ / / /_/\\ \\ \\ -=====/_/_//_/\\_,_/ /_/\\_\\ /______\\ \\ \\ A proud member of TeamLinux \\_________\\/ email: carew@home.msen.com web: http://home.msen.com/~carew --------------EEFFDDB86DC8134BCB0D2FD0 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit <HTML> Christopher , <P>Ok, Lets look at this in some detail. To do this, I'll pull a case study out of my hat from a customer I've consulted with in the past (a major integrator here in Plymouth MI, U.S.A.). These guys had a system where they loaded a bunch of scanned documents into a "job". This documents consisted of the image of the document (both front and back) along with its ASCII file that represented an effort by the machine to convert a portion of the image to text. This portion of the equipment we referred to as "real-time" and it worked pretty well. The other portion of the equipment was designed as a workcell that fixed the documents the OCR software couldn't handle. <P>These jobs then generally contained 100 to 300 documents of which roughly 50% were correctly OCR'd and marked as "done". The other 50% were marked as needing "keying". The software as designed read the "jobs" and entered a record in their workcell database that represented every document in the job. The way their workcell was set up, there could be many jobs that needed work, but only one person could work on a job at a time. The way they set up the database was one table containing a record for each of the jobs on the system, and a "volatile" table for each of the jobs containing a record for each of the documents in that job. Just to sum that up another way, if you had three jobs each with 300 documents, you would have a total of 4 tables, one listing the jobs on the system, and 3 more for each of the jobs each containing 300 records. <P>You're probably thinking that they were using the jobs table as a status table, and you're right. You're also probably thinking that they didn't have row level locking with this database and that would seriously limit the speed with which they could process their documents, and you're right again. To make things more interesting, the "green" designer who put together the demo system for this production system used cursors in VB <sigh>, what a mess. Suffice it to say that the performance of this system was dismally low. It was supposed to work with 30 or more operators, and it could barely keep up with 8. <P>The fix to the system, which they are in the process of