Re: Simple SQL with MySQL - performance

Hello Benji Spencer I would like to just point you at WebObjects (http://www.apple.com/webobjects). Since Apple changed their license policy last May, it is quite afordable. WebObjects just handles you all the things about sessions (key generation, storing keys, timeouts,...) among a lot of other things. The big advantages: It does work with MySQL AND it does not need to make any DB-access for the whole session issue. I think that you should spend a serious thought about WebObjects because your page is rather busy (as you mentioned). Don't take me wrong: nothing at all against PHP. Kind Regards, Fabian >Thema: [PHP-DB] Simple SQL with MySQL - performance >gesendet: 21.10.1920 20:33 Uhr >empfangen: 17.10.2000 14:17 Uhr >Absender: Benji Spencer, bspencer@moody.edu >An: PHP-DB Mailinglist, php-db@lists.php.net >Kopie: php-general@lists.php.net > >Amoung other things, we are developing a session management/access system >which will store the session information in a MySQL db table. The speed of >this process is important. below is the rough idea of what we are doing. > >1) the user goes to the site, and logs in. They are issued a random ticket >(a cookie). The ticket, and their user information is stored in a MySQL >table, along with the timestamp of when they logged in (used later for >expiring their session later). 1 db hit. > >2) The user cruises around the site, and have access to everything (we are >intergrating various backend systems, some of which use Basic Auth to >authenticate people, some of which use web forms, so the proxy script >passes the user information off to the various things that need it) At some >point their session expires (just because they love the site and stay long >enough). If they are still in an active session, they ticket is renewed. 1 >db hit for each html page, and maybe one for each graphic (haven't figured >out how far we want to go yet) to see if the session is still active + 1 db >hit for the renewing of the ticket every so often. > >3) If the user goes away from the site for awhile, and comes back, the >ticket might have expired (1 db hit to check that if they hadn't closed >their browser). If the ticket expired, they are bounced to a login page, >and things start over with #1. The expired ticket is also deleted from the >db (1 db hit there also) > >Step #2 could be very painful for the db. Apache tells us that we average >about 7 requests per second during busy times. Not all of those would be >going through this ticketing mechanism (for a highend figure, lets assume >an average of 2 requests per second on this ticketed area with peaks of 5-7 >requests per second). > >All the SQL statements would be a select (90%+), and then there would be a >delete for every insert > >I am sure MySQL can handle this, but what type of box would be needed? >Ideally the entire thing (Apache reverse proxy with php page rewriting + >MySQL ticketing) would reside on one box. I can figure out how much of a >box is needed my the Apache part, but the MySQL part is a bit harder for >me. Does anyone with a highly active MySQL database site have any input? > >benji >--- >Benji Spencer >Web Programmer >Moody Bible Institute >312-329-2288