I would vote against stored procedures. Keep the DB simple. Mark
On Tue, Jul 23, 2013 at 9:01 AM, Josh Thompson <[email protected]> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hi Jim, > > What are you saying would not be a problem? I know having 2 systems trying to > write at the same time would be okay, but the scheduling code has to read a > bunch of data from the database, analyze it to select a computer, and then > write information to the database. The semaphore is needed because the state > of the system needs to remain the same between the reading of the data and the > writing of it. Otherwise, computers that were not assigned during the read > may get assigned before the write happens, which can result in multiple people > getting the same computer. We actually had this happen back in 2004 when we > were having our initial pilot of VCL, which is what prompted adding the > semaphore. > > Another option is to actually move the scheduling code to a stored procedure > in the database. However, that would require someone with much better stored > procedure skills than mine. > > Josh > > On Monday, July 22, 2013 2:36:28 PM James O'Dell wrote: >> Hey Josh, >> >> I've been F5 load balancing, and SSL offloading, Moodle for the last >> couple of years with out a problem. >> Moodle just insists the database engine be ACID compliant >> >> If the database engine is transaction safe (i.e. InnoDB) you shouldn't >> have a problem. >> It's really just a matter of handling the results (i.e. if someone else >> grabs the reservation >> what do you do) >> >> Imho relying on the database seems like the simplest/cleanest approach >> >> __Jim >> >> On 7/22/2013 2:20 PM, Josh Thompson wrote: >> > -----BEGIN PGP SIGNED MESSAGE----- >> > Hash: SHA1 >> > >> > The problem with load balancing the front end is that there is a semaphore >> > lock (php's built in implementation) around the scheduling code. Without >> > it, 2 users coming in at the same time can get assigned the same >> > computer. So, having multiple frontends would allow the same thing to >> > happen. >> > >> > I've considered just locking a table in the database as a semaphore. >> > However, in my testing of this, if the web server locks a table and then >> > goes offline before unlocking it, the table stays locked. So, done this >> > way, if there were multiple frontends and one went offline while in the >> > middle of the lock, none of them would be able to create reservations. I >> > don't remember what had to be done to release the lock. >> > >> > The option I'd like to use that I've not gotten around to implementing (I >> > believe there is a really old JIRA issue for it) is to add an XMLRPC API >> > function for calling the scheduling code. Then, have one frontend >> > assigned as the one that would handle all of the scheduling. The others >> > would make calls to that one for just the scheduling part, but would do >> > everything else normally. Optionally, there could be an election process >> > so that a new frontend could be selected to do the scheduling if the one >> > that had been doing it went down. >> > >> > Aaron C. listed some good ideas, but I think the above would be more >> > straightforward since it would not involve bringing in other technologies. >> > >> > Josh >> > >> > On Thursday, July 18, 2013 9:49:39 AM Aaron Peeler wrote: >> >> I like both of these approaches, especially 1, but I'm not sure of the >> >> effort it would take to convert. >> >> -AP >> >> >> >> On Wed, Jul 17, 2013 at 2:59 PM, Aaron Coburn <[email protected]> > wrote: >> >>> In my opinion, this issue has largely been solved by the NoSQL and >> >>> application messaging communities. But as long as the VCL web server(s) >> >>> use local, file-based semaphores to control resource allocation, this >> >>> will be a hard nut to crack. >> >>> >> >>> If I were going to tackle this problem, I would take one of two general >> >>> approaches: >> >>> >> >>> 1) Use an asynchronous messaging queue (i.e. Apache ActiveMQ or >> >>> something >> >>> like Redis) >> >>> >> >>> In this way, when a reservation is made, the request is pushed onto a >> >>> centralized queue, and some intermediate (i.e. vcld) process will shift >> >>> messages off the queue and assign the reservations. If we used ActiveMQ, >> >>> the php and perl code would communicate with the message broker over the >> >>> STOMP protocol [1]. Redis would also work because it runs in a single >> >>> thread and all operations are atomic -- requests can simply be pushed >> >>> onto a FIFO-type list structure [2]. >> >>> >> >>> 2. Use a NoSQL database such as CouchDB or Riak that uses a type of >> >>> optimistic locking model for all writes. >> >>> >> >>> That is, if reservations are stored in such a way that the resource id >> >>> (i.e. computer id) forms the database key, the assignment of a user to a >> >>> particular compute resource requires sending the correct revision_id of >> >>> that resource in order for the write to be successful. If successful, it >> >>> returns an HTTP 200 status code and the client proceeds normally; >> >>> otherwise, it sends a 409 header and it is up to the client to try again >> >>> [3]. It would also be possible to use the existing MySQL database to >> >>> emulate something like #2, but under a heavy load, I imagine that the >> >>> row >> >>> locking could really slow things down. Using Couch would really be much >> >>> more scalable than trying to do everything in MySQL. >> >>> >> >>> Either of these approaches would then allow you to distribute the web >> >>> front end across multiple machines. >> >>> >> >>> Cheers, >> >>> Aaron Coburn >> >>> >> >>> [1] http://activemq.apache.org/stomp.html >> >>> [2] http://redis.io/commands/lpush >> >>> [3] http://wiki.apache.org/couchdb/HTTP_Document_API#PUT >> >>> >> >>> On Jul 17, 2013, at 1:06 PM, Aaron Peeler <[email protected]> wrote: >> >>>> This is definitely desired. >> >>>> >> >>>> An additional challenge with multiple web servers is to figure how to >> >>>> sanely lock/unlock the resource to prevent it from getting assigned >> >>>> two separate users that are making requests at the same instant. >> >>>> >> >>>> AP >> >>>> >> >>>> On Wed, Jul 17, 2013 at 12:57 PM, James O'Dell <[email protected]> >> > >> > wrote: >> >>>>> I also tried load balancing the web. I didn't have any success. >> >>>>> >> >>>>> I also tried an SSL offload using the F5 >> >>>>> >> >>>>> The SSLoffload didn't work because >> >>>>> an internal VCL check noticed the header wasn't https >> >>>>> and redirected to https. Basically it kept looping >> >>>>> >> >>>>> On 7/17/2013 9:36 AM, Dmitri Chebotarov wrote: >> >>>>> >> >>>>> Hi >> >>>>> >> >>>>> I would like to load balance multiple front-end VCL servers. >> >>>>> It is F5 load balancer. The LB configuration allows to enable session >> >>>>> persistency. >> >>>>> Is this will be OK with VCL's front-end? I remember someone mentioned >> >>>>> some >> >>>>> issues with having multiple front-end servers, but don't remember >> >>>>> details. >> >>>>> >> >>>>> Thanks. >> >>>> >> >>>> -- >> >>>> Aaron Peeler >> >>>> Program Manager >> >>>> Virtual Computing Lab >> >>>> NC State University >> >>>> >> >>>> All electronic mail messages in connection with State business which >> >>>> are sent to or received by this account are subject to the NC Public >> >>>> Records Law and may be disclosed to third parties. >> > >> > - -- >> > - ------------------------------- >> > Josh Thompson >> > VCL Developer >> > North Carolina State University >> > >> > my GPG/PGP key can be found at pgp.mit.edu >> > >> > All electronic mail messages in connection with State business which >> > are sent to or received by this account are subject to the NC Public >> > Records Law and may be disclosed to third parties. >> > -----BEGIN PGP SIGNATURE----- >> > Version: GnuPG v2.0.19 (GNU/Linux) >> > >> > iEYEARECAAYFAlHtojYACgkQV/LQcNdtPQNNjwCfQ+PRt/yZXI4tf2YDiH2IZu2m >> > coYAn2QbjICSa5MUR0cR9DIxniZVQFtK >> > =/YZs >> > -----END PGP SIGNATURE----- > - -- > - ------------------------------- > Josh Thompson > VCL Developer > North Carolina State University > > my GPG/PGP key can be found at pgp.mit.edu > > All electronic mail messages in connection with State business which > are sent to or received by this account are subject to the NC Public > Records Law and may be disclosed to third parties. > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v2.0.19 (GNU/Linux) > > iEYEARECAAYFAlHufrkACgkQV/LQcNdtPQPSagCdHzGSWMPRoJjTYR/nqdJScmW/ > k2EAn2cGY5gKBZZgkRsG9ebscAVyOcYa > =oxwt > -----END PGP SIGNATURE----- > -- Mark Gardner --
