One of the things to take into consideration when developing a multi-user application is the Database performance.
When developing PHP applications, I always use ADODB to provide a Database abstraction layer. More specifically I use my own DB layer, build on top of ADODB, but I only do this is to make it easy to replace ADODB if the need ever arises (e.g. if it stops being maintained).
ADODB has a great module (part of the standard ADODB package) called Performance Monitoring Library. This little gem provides some great functionality and I suggest you check it out.
An SQL logger and analyzer is provided, among other things. The SQL logger, when enabled, stores all executed queries in a DB table, along with some interesting data, like the time it was needed to execute the query. The SQL analyzer gives you 3 extremely useful reports:
- Suspicious SQL: The queries with the highest execution time
- Expensive SQL: The queries with highest total execution times (number executions * average execution time).
- Invalid SQL: Queries that produced an error.
During development I leave the SQL logger constantly on and use the analyzer reports to either optimize my queries or find ways to reduce the number of executions. Warning: Be careful not to leave the SQL logger on by default, because in real installations it can reduce performance. Also the analyzer will take a very long time to analyze more that ~500k queries. Anecdotal: I once left the SQL logger enabled in a health related project running in a hospital (~40 concurrent users, 24×7) for about a week. The analyzer never returned any results for the ~5 million records it had logged. So I had to discard the logged queries and leave it running for exactly 1 day.
Since I’ve made extended use of the Perf library with some really good results in the past, I have integrated it into Magna CRM. Through the use of a single flag (DEBUG_LEVEL) I can do various things like enabling the SQL logger or displaying the SQL analyzer screens to an administrator. I will leave this in, in case I ever need to find what’s wrong with a specific installation that has performance issues.
