On Wed, Aug 26, 2009 at 10:30 AM, Dan Linder
<dan@linder.org> wrote:
Mario,
Thanks for the feedback.
Not a problem.
Thankfully, the database is very light on the write/update side. Does
the read lock lock out other readers at the same time?
This is an excerpt from SQLite's manual regarding concurrency:
SQLite uses reader/writer locks on the entire database file. That means
if any process is reading from any part of the database, all other
processes are prevented from writing any other part of the database.
Similarly, if any one process is writing to the database,
all other processes are prevented from reading any other part of the
database.
For many situations, this is not a problem. Each application
does its database work quickly and moves on, and no lock lasts for more
than a few dozen milliseconds. But there are some applications that require
more concurrency, and those applications may need to seek a different
solution.
In simpler terms, the longer your SQL Statement has to execute, the longer the database is going to be locked in either read/write mode. If your getting small subsets of data from the database, then the milliseconds quote is correct, and it won't take long to execute the SQL Statements. However, if you are getting a few hundred megs of records from the database in a SQL statement, either inserting, or retrieving, then it's going to take the database engine longer to fetch the data, and will hold the lock. I believe you can have as many readers as you want reading from the database, but once a program sends a write SQL statement, all other readers will lock till the writer finishes, the same for the opposite direction.
But if it's small updates, and small record sets being retrieved, then you shouldn't have anything to worry about.
As much as there is some pressure to stay "Pure Perl" and not rely on
non-core modules, I think this is the only route toward expanding this
tool. (Plus the added flexibility of adding other DB options by
including the appropriate perl module.)
There's a lot of people that way, same with wanting "Pure Ruby" or "Pure Python" or "Pure" any other language out there. But the thing about it is, you need to realize that with Perl, Ruby, Python, C# and such languages, they are all interpreted languages, and anything "Pure" in those languages, are going to be slow to execute.
The best thing about SQLite, atleast in Ruby, haven't really dealt much with the Perl one, is that the extension that binds with the engine itself, is all compiled together into a single dll/so extension, so you don't have to rely on an external library to be installed. And most times, it compiles without any problems, especially on Linux based systems.
Thankfully the server portion is 100% Unix. :-)
And again, this is a life send for you, as stated above.
I'm thinking that as a work-around to this, I can keep both versions
available. Since these data files are only updated in a couple key
locations (and the update is mostly through non-interactive means),
this should be easily achievable. Once the data is saved in both
forms and the flat and db files are consistent, updating the reporting
pieces should be easier since I won't (shouldn't) break anything
during the transition. An added bonus is that customers who rely on
the textual data will not have to immediately re-code for the new DB
chosen.
Another nice thing that you can do, if you want to go this route, with having the ability for grep/vi, is that you can create simple wrappers around grep and vi, and run SQL statements to generate the output, and pipe it into grep, or vi, and have no problems with it.
As I'm writing it, I'm leaning toward using the DBD interface and
accessing SQLite initially. If/when the time comes that
MySQL/Postgres/Oracle/AnotherDB is requested, the changes should be
minimal. The downfall of being an external module is greatly
out-weighed by the flexibility it provides us.
I would definitely agree with that, you want flexibility, not just in the code that you have to write, but flexibility in the programs themselves, to meet the needs of various customers. Not all customers have the same need, and when you start to use the DBD interface, you'll find that it works quite well, and will require hardly any differences between the various database engine backends, that DBD Supports.
I'm hoping to carve out some free time over the next couple weeks to
put some test code together to see what speed differentials are
achieved by replacing dump/eval with SQLite, MySQL, etc.
I can almost garuntee that SQLite, and MySQL will overpower dump/eval in nothing flat. At that point, your bottlenecks should dis-appear, least you code sloppy SQL Statements, but JOINs are your friends when cross table data retrieval.
And your welcome for the help,
Mario
Thanks,
Dan
--
******************* ***************** ************* ***********
******* ***** *** **
"Quis custodiet ipsos custodes?" (Who can watch the watchmen?) -- from
the Satires of Juvenal
"I do not fear computers, I fear the lack of them." -- Isaac Asimov (Author)
** *** ***** ******* *********** ************* *****************
*******************
_______________________________________________
Omaha-pm mailing list
Omaha-pm@pm.org
http://mail.pm.org/mailman/listinfo/omaha-pm