PHP / MySQL - highly transactional programming & thousands of concurrent users?

# Strong understanding of highly transactional programming
# Code optimization to support thousands of concurrent users


I've just seen these included in the specs for a job advertisement for a PHP, MySQL Web Developer.  Could you give me some ideas / examples of regular solutions / patterns to solve these problems.

What is highly transactional programming? Is this just a fancy term for a web app / site that will have to do a lot of DB updates?

What are the normal methods to accommodate high numbers of concurrent users in web apps.  What can i do in the PHP / MySQL? or is it really down to Apache / MySQL Server and the hardware to deal with this?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Beverley PortlockCommented:
There are too many factors to fit a realistic answer in, but....

Transactional database updates usually refer to the practice of using journalled databases. These allow you to add updates to several tables and then when a consistent point is reached to "commit" all the updates in one go. It means that if a user backs out of a transaction then any uncommitted changes can just be dropped. This is usually done via the Inno-DB engine.

To be honest, if you have ask these sorts of questions then I'm not sure you should be going for the job! (sorry!)

To deal with thousands of users it is a combination of high-speed connections, fail-over setups, large, fast servers, connection pooling, optimisied database design... it needs a book, not an EE answer.

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
damoth1Author Commented:
Thanks bportlock,

These 2 are listed as preferred skills - i have all the required skills on the job spec.  I just want to understand what the two terms meant.

Anyway, now i know what is meant by Transactional Database Updates.  Could i get a little more information about concurrent users just in terms of PHP / MySQL - i just want a very high level overview like what was given  for transactional databases above.
Beverley PortlockCommented:
A lot of what has to be done will depend on the precise configuration being used. For instance, there may be multiple Apache servers being fed on a load-balanced basis. The speed of each Apache server would depend on the memory available the number of children that the server process can spawn, how many are created initially, how many held in reserve.

It may be possible for the databases to be held separately with remote processes consolidating the data in an off-line manner. Probably the must important consideration would be the number of updates as this can drastically affect the database response. It may be that queries are queued and processed by a "dispatcher engine" which I have used in fast processing environments with multi-processor computers (normally IBM AS/400s).

One way to increase responsive is to open 20 or 30 connections to the database and make them persist so that they exist in a pool to be allocated. This saves the overhead of re-establishing a server connection every time a transaction starts but you need an efficient connection pool manager or else the overhead is worse than making a fresh connection.

There are in-memory tables for fast lookup, query optimisation, data denormalisation.... there are dozens of techniques.

At a guess, your first headache is getting the users to the server at a reasonable speed - that mean lots of bandwidth on the networks into the server room. Next the servers will need a good chunk of memory - say 128K per Apache process and if you are running 30 Apache processes then you are looking at nearly 4GB of memory so you want a 64bit server, probably with a minimum of 2 cores per chip. I would imagine some form of RAID - RAID 5 is resilient but slow if it loses a disc. RAID 0 is faster and may suffice if you have the right controllers. The discs will be your biggest bottleneck since, being mechanical, they are the slowest component. I would want a multi-disc setup and the machine partitioned onto different discs to separate data / caches / OS / programs.

I'm skimming the surface here, but you get the idea.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.