On Wed, Jul 21, 2010 at 9:47 AM, Simon Slavin <slav...@bigfraud.org> wrote:
> > On 21 Jul 2010, at 2:30pm, Sam Carleton wrote: > > > My issue with only attaching to the EventDB for only as long as a command > is > > trying to use it is: The EventDB is used a LOT, a WHOLE lot. If I > attach > > and drop between each little call, then within one HTTP request I might > > attach and drop 3 to 8 times. What is the cost associated with > > attaching/dropping a database? Since it involves attaching to a physical > > file, I am assuming it is a bit high. > > > > As far as telling my application when the EventDB file is out of date: > It > > would be pretty easy for me to add a timestamp to the EventDB variable > that > > is in the primary database, but... Once I get a connection from the > > connection pool, how do I know the age of that connection to know if the > > EventDB it is attached to is the new one or the old one? Is there some > > connection age or timestamp I could use? I am guessing not, which is why > I > > am using the PRAGMA database_list to determine if the EventDB is attached > > and if so, find the physical path to the EventDB file to see if it > matches > > with the current system settings. > > Ah. If all you are doing is replying to one HTTP request then that'll take > less than one second, right ? You don't have to worry about the 'right' > EventsDB file changing during a request because it doesn't last long enough. > Your users will have no way of telling if their data is almost one entire > second out of date. So you don't need a complicated semaphore method to > communicate with existing processes, all you need is a way to tell a new > process which events database file to attach. > > As you note, this can be done various ways: put another text file on the > server called 'currentevents.txt' and in that file put the full path of the > current event.sqlite file. Or you could use an 'include' file for your PHP > code, or whatever language you're using for your web backend, and write > software to rewrite that 'include' file. Or you could store the path as > something that can be looked up in another SQLite database, though that > would be a little more 'expensive'. > Simon, For what it matters, the code is not PHP, it is 100% C/C++ in a custom Apache Module and in Axis2/C Web Services. As far as how to communicate to the modules (Apache Modules and Axis2/C Web Services) what the current EventDB file and when it was change is already taken care of, has been for a long time now. Here is what has changed: In the old code, the modules where *NOT* using connection pools, each part of a request would open a connection to the database it needed to talk to (the system DB or EventDB), do unto the DB what it needed and closed the connection. With this approach the EventDB was open/closes each and every time. The problem though is that one request might open/close the System DB 1 to 3 times and EventDB 3 to 6 times! The clients where dropping connections because of DB errors, it looked like it was errors in closing the DB. When I looked at the new features on Apache 2.2, at the very bottom of the page I saw this "DBD Framework (SQL Database API)": http://httpd.apache.org/docs/2.2/new_features_2_2.html The last paragraph on the DBD Framework states "New modules SHOULD now use these APIs for all SQL database operations. Existing applications SHOULD be upgraded to use it where feasible, either transparently or as a recommended option to their users." So that is what I am doing, upgrading to DBD Framework. How the DBD works is this: When the web server starts up it connection to one DB, in my case the SystemDB. As the name implies it is system wide and will never change for the life of the server. At load time (of the Apache server) the min, max, and keep numbers are defined. The end result is: If you define a min of 4, 20, and 8 respectfully, there will ALWAYS be 4 connection to the SystemDB, a max of 20 connections and once 8 connections are created a normal minimum of 8. Each time a request asks for a connection, the DBD gives it one. A VERY important fact is the connection a HTTP request gets will be the same though out the life of that request. What this means to my modules is: When an module gets a connection it is from this pool which might have been one of the original 4, one of the other connections created when the system was under load, or a brand new one that was created for the request. The whole idea is the DBD manages all that for the business logic. As far as raw performance, that goes way up because connections are not being created and destoried all the time. The drawback for me, though is that in my case, the second DB, the EventDB can change at ANYTIME. It could be that the server has been sitting 100% idle for a few hours, the admin changes the EventDB, then goes to access the new one. The 1st 4 connection the request gets from the pool will be one of the 4 minimum connections and many hours old which are still connected to the old EventDB, fore the connections never go away until the server is shutdown. In the end, I am looking for the best way to determine if the connection contains the correct EventDB, since a connection can live for a LONG LONG time. Right now I am using PRAGMA database_list to get the list, find if there is an EventDB and if so, is it the correct one, each and every time the system gets a connection from the pool. This could happen between 3 to 8 times in one request. The question is: Is it worth my time to hook into the whole request process before any other part of my modules run and do this check only once, so the rest of the code that gets the same connection 3 to 8 times can skipt the check, or is the cost of this check so small that it is not worth my time to optimize? Sam _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
