Will a script 'take a message' if the database is busy?

[gemini18]

Hi all,

I'm able to install and modify os-commerce open-source shopping cart, and need to use it to connect up to 5 'stores' to one single affiliate program (all in PHP).

Testing (alone) everything works great, but in the 'real world':

-The 'script', to credit sales affiliates, is started by the 'Thank you' page of each cart, as a sale is completed. The individual carts will not get massively busy, but will the one vital affiliate database be able to keep up?

-What happens to information which gets sent when the MySQL database is 'busy'? In general (not specific to any one program), does incoming data get 'saved' in a queue, or dumped, when the server is busy? (in other words the os-commerce cart is not waiting for a response to the script)

Anybody have real experience?

Thank You VERY much.

[jatar_k]

if the db is busy I imagine one of two things will happen

1. your script won't be able to connect
2. your script will time out waiting for the response from mysql

requests get queued but not in a way that allows them to be completed after the fact, it isn't a fire and forget scenario.

[mjwalshe]

>What happens to information which gets sent when the >MySQL database is 'busy'? In general (not specific to >any one program), does incoming data get 'saved' in a >queue, or dumped, when the server is busy? (in other >words the os-commerce cart is not waiting for a response >to the script)

>Anybody have real experience?

Yeh I looked at this for a previous employer (for a registry)

Mysql (as normaly used on the web) is quite dumb a transaction has to run in real time there are ways to have asynchronous queues of transactions so that a system can fire off an insert even if the receiving system is down or busy .

A quick search doest show any tools for mysql (if you have mysql 5) you could look at using triggers/ on the sales cart database to update the affiliate database rather than relying on the thank you page.

You could Roll your own

By using triggers or the thank you page script to generate sql transactions as xml files. save these to a directory outside of the webserver root and write some process (in perl/php etc) that executes every 10 min to read these files and insert the info into the affiliate database.

Or use a database with Queuing support postgress appears to support this which if you shopping cart supports it would be the best way to go

[gemini18]

I was delayed making it back here.
Thank you both, for the high quality answers.

[SeanW]

MySQL should be able to keep up with 5 sites. I recently set up a site with 120+ WordPress blogs, the database was doing in excess of 500 queries per second (mostly read) last time I logged in.

It's all in the db tuning -- making sure the db has what it needs to execute quickly. MySQL is very fast, but if (for example), you have 2000 tables and only let the db open 64 files at once, no db will run efficiently.

If you run out of connections, you get an immediate error. If a query takes a long time, the application (ie PHP/oscommerce) blocks until the data returns. This means that the user will see slow loading.

Sean