Performance issues with PHP5 and MySQL5

maaki

I'm not quite sure is this the best forum for this topic, but at least it is highly related to the forum's subject 🙂 I could start by telling some basic information before moving into the problem.

I started to work in my current job place somewhat 6 months ago and on my responsibility is the most vital product, writen with PHP5 and it uses MySQL5, for the company. I have read the source code of the program and I could say I'm familiar with it. One of the customer has had a serious performance problems with the product even before I started working. The problem is the page loads takes about 5 to 45 seconds depending on a page.

I started to locate the reason for the long page load times and I discovered following when I tested the program at one of the clients environment:

program makes a bit over 2 000 SQL queries to genetare the main page
on some pages I encountered even 12 000 SQL queries per page load
a lot of identical SQL queries are made during page execution
a result of one SQL query can be saved in two or three objects (same data in each one)
somewhat 97% of the query results return data which aren't needed. A lot of uncessary data is fetched from database
none of the query results are free during the script execution
etc..
customer's database is only 60MB

How that amount of queries are got? The code is written so first is made one query which returns n amount of rows. The result is then ran through a loop in which more queries are made. So the basic thing is the more data customer has in data base the more queries are made. And this leads to doom, the usability will be zero finally. Which is it nearly is already.

I have told the management that the program should be fixed or a new better version should be done. The management doesn't see the amount of queries as a problem (and fixing the program isn't an option) and tells just to configure the database server better to get lower page load times. I would be interested to hear also opinions from other coders, which is why I'm writing here? 🙂 Can the page load times be fixed only by configuring the database server more or would it be wiser to fix the program itself? I'm also interested to all other opinions and thoughts you might have 🙂

mpb001

Sounds like they have gone down the developer route of sod the database engine and the years of work the engine writers have done and just use overly developer friendly object mappings instead.

Associate as much memory to the database as possible and make sure the database is indexed everywhere it should be. Sometimes if someone can't be bothered to use joins they can't be bothered with indexing properly ( dates are prime examples of things to check ), that alone can make a real difference. Without indexes there is a continual degradation, with them much less so.

Also log slow queries.

read this post at the bottom as well about server optimisation( query cache etc )
http://www.phpbuilder.com/board/archive/index.php/t-10346854.html

Links to all the juicy MySQL stuff that can solve your problems.

laserlight

maaki wrote:
I have told the management that the program should be fixed or a new better version should be done. The management doesn't see the amount of queries as a problem (and fixing the program isn't an option) and tells just to configure the database server better to get lower page load times. I would be interested to hear also opinions from other coders, which is why I'm writing here? 🙂 Can the page load times be fixed only by configuring the database server more or would it be wiser to fix the program itself? I'm also interested to all other opinions and thoughts you might have 🙂

It seems to me that fixing the program would have a much greater impact than tweaking the database. After all, reducing the number of queries means reducing the number of operations over a network (or at least over a filesystem), and I/O is typically more expensive than CPU time or memory access.

mpb001

laserlight wrote:
It seems to me that fixing the program would have a much greater impact than tweaking the database. After all, reducing the number of queries means reducing the number of operations over a network (or at least over a filesystem), and I/O is typically more expensive than CPU time or memory access.

It is not about fixing the program though, it is about getting it into a semi reasonable state. If it is properly indexed and configured the engine should be more tolerant for the sloppyness there. The engine should also move things to memory where it can. Full table scans sound like an enemy here, once they are removed and the database is as optimised it can be then it is time to refactor the most problematic parts over time. When talking to management don't use the term rewrite but refactor. A proper join here or there could do wonders, but by optimising the database beforehand and having the belief/knowledge/resources to back it up before any code changes are needed management will have to start to concede.

The term rewrite is synonymous with the term someone screwed up. Management don't like these black marks. Refactoring is just a normal part of a projects life cycle and they can shrug it off.

laserlight

It is not about fixing the program though, it is about getting it into a semi reasonable state. If it is properly indexed and configured the engine should be more tolerant for the sloppyness there. The engine should also move things to memory where it can. Full table scans sound like an enemy here, once they are removed and the database is as optimised it can be then it is time to refactor the most problematic parts over time.

Assuming cooperative management, doesn't it make more sense to tackle the area that will give larger performance gains first?

When talking to management don't use the term rewrite but refactor. A proper join here or there could do wonders, but by optimising the database beforehand and having the belief/knowledge/resources to back it up before any code changes are needed management will have to start to concede.

The term rewrite is synonymous with the term someone screwed up. Management don't like these black marks. Refactoring is just a normal part of a projects life cycle and they can shrug it off.

I like your style 🙂

maaki

mpb001 wrote:
Associate as much memory to the database as possible and make sure the database is indexed everywhere it should be. Sometimes if someone can't be bothered to use joins they can't be bothered with indexing properly ( dates are prime examples of things to check ), that alone can make a real difference. Without indexes there is a continual degradation, with them much less so.

I'm more application developer than database administrator so optimizing database server configuration is a totally new area for me. Though I have knowledge to do basic configurations etc.

So far what I have done (before making this thread). I have associated more memory for innodb_buffer_pool_size and query_cache_size. I took MySQL's my-huge.cnf as an example for this. Indexes should be quite ok. So far I have noticed that some indexes doesn't exist which should so in there some improvement can be made.

I make a quick look for the link you gave and seems at least some help could be found there. I read it more carefully tomorrow. Thanks 🙂

maaki

laserlight wrote:
Assuming cooperative management, doesn't it make more sense to tackle the area that will give larger performance gains first?

I would say the first priority is to get the customer pleased enough for the program. Hopefully this can be done by tweaking the database. When this is achieved we can discuss with management what would be the best way to proceed. In other words: will the optimizing database be good enough solution for a long run or should also refactoring be made.

laserlight

I would say the first priority is to get the customer pleased enough for the program. Hopefully this can be done by tweaking the database. When this is achieved we can discuss with management what would be the best way to proceed.

Ah, yes, this makes economic sense.

mpb001

laserlight wrote:
Assuming cooperative management, doesn't it make more sense to tackle the area that will give larger performance gains first?

This has taken me so long you two have been talking 🙂

Well it is a balance between time needed to be taken and the performance gained. Once entered into the potentially murky world of the code it could turn into a bottomless well of hair pulling. Select * in these things is also a very dirty part of them, if someone has stuck an image as a blob in a field that is coming out when not needed the thing becomes an eater of worlds later.

Optimising the database, if it is known what to look for could be done in a morning and he can turn round and say he improved it x amount in a few hours/day. Doing it first also could achieve very good results right across the site and be very visible. He may be known in the future as the 'wizard'.

The engineers who write the engines have to assume that monkeys are going to write stuff and have to guard against it. If they don't guard against it with query caching etc then ultimately their database will get the blame, however good it is, and get bad press.

Once it is optimized it breaks down to 3 things

Optimise the sql of common elements that could be incurring loss across multiple pages. Fixing those will give a general performance increase that will also be pretty noticeable.
Pages that are most popular and have the most problematic performance.
Pages that are infrequently used but have real major problems still. Someone who infrequently goes to a report that appears quite complex assumes it is quite complex and assumes it will take a while.

The order the subtasks of those 3 are done is really up to instinct. Just comment stuff out and see how it affects things in a development environment, estimate how long it will take to do and keep track of very effective 1-2 hr refactorings. Then move onto half day/one day ones etc.
Each time a small thing is done well management will hopefully get more cooperative. If he puts the things that will float their boats the most at the top of each list even more so. Start off small and push management a little bit further each time.

The whole time slowing chiselling away at the current way of doing things and building a new database layer that actually has respect for the database. I am assuming it was done using an ORM thingy which could cause the most amount of problems to remove. If it is just select * and raw mysql calls then it can be done pretty quickly. Just laborious. Also check indexes as it is being done as some slip under the net as they might not be obvious.

Most people are so busy looking for attacks from the back and front it is pretty easy to sneak in from the side.

laserlight wrote:
I like your style 🙂

Well as you are the one who usually contests me I quite like yours 🙂