Why PHP take more CPU time than MySQL ?

[tornado_mtb]

In my debug message below board show that.

Code: Select all

[ Time : 0.429s | 16 Queries | GZIP : On | Load : 0.58 | Memory Usage: 5.47 MiB | Explain ] 

Then I click "explain"

Code: Select all

SQL Report

Page generated in 0.4241 seconds with 11 queries + 3 queries returning data from cache

Time spent on mysql4 queries: 0.04604s | Time spent on PHP: 0.3781s

I wondering, PHP is take time much more than mysql , Is that correct?

I already install APC, it help a little for PHP time.

I didn't install memcache now. but It seem may not help so much ,
because MySQL time really faster than PHP ,Am I right?

So if I want to increase forum performance, How I should do next?


FYI:
my forum have 5 million post , >50,000 member. My server have RAM 24GB with dual xeon six core CPU.

[drathbun]

Turn gzip off and you'll save some CPU cycles at the expense of increased bandwidth. I've had gzip off for years. Also less than half a second to render a page really isn't that bad.

But even with gzip off, php and the template engine takes more time than a well-tuned database.

[tornado_mtb]

Code: Select all

Page generated in 0.5483 seconds with 11 queries + 3 queries returning data from cache

Time spent on mysql4 queries: 0.06222s | Time spent on PHP: 0.48605s

I try to turn off gzip, but PHP still much than mysql.

[naderman]

Yes, that's because it has to do more work in the PHP code than in the database. That's just where more work takes place.

[drathbun]

A few years back I had nothing better to do so I dissected and tuned every single query that I could find. Even after that I discovered that my page generation times were roughly the same as before. I broke things apart into major categories of php activity and found that - at least in my version of phpBB - the bulk of the work was done in the template engine.

Basically you can break the process down into parts:

1. Get the input from the user / URL
2. Run the appropriate SQL statement to retrieve the data
3. Process the data (I classify this as "other" below, as it's non-template time, or time spent outside of the template engine)
4. Format the data and present the final output to the user (template processing only)
5. (Optional) gzip / compress the output to save on bandwidth

In my analysis, I found that step four - format and present the data - is by far the most time consuming step of that process. I back-ported the phPBB3 template processor to phpBB2 and found a definite improvement. There were several other template engines that I tried as well, all of which were faster than the stock template engine for phpBB2.

The point is if you're looking for a place to shave some time, I think that's where you might want to start looking. As an example, here are the stats from some of my pages. Keep in mind that the numbers are not exactly relevant as they come from a highly modified phpBB2 installation rather than phpBB3. But notice that in each case the parsing step (which is the template engine) is the highest value, so I think it supports the point that I am making.

Code: Select all

board index SQL 0.0032 Parse 0.1375 Other 0.0467
viewtopic SQL 0.0055 Parse 0.1519 Other 0.0613
viewtopic SQL 0.1601 Parse 0.1287 Other 0.0547 (different topic)
viewtopic SQL 0.0080 Parse 0.1048 Other 0.0480 (another different topic)
search new posts SQL 0.0325 Parse 0.1297 Other 0.0559

Here is an exception to the pattern:

Code: Select all

search unanswered SQL 1.2123 Parse 0.0681 Other 0.0824

In this case the "topic_replies" column is a column that is not indexed in my database. If I were to add an index to this field that query time would drop, but that particular search is so rarely used that I don't mind that it's a bit slower. It's not worth the overhead of adding yet another index to the topics table.

Tuning can be frustrating. The first step, though, is to find out where you can do the most good. Figure out what part of the php code is taking the longest, and see if you are willing to make the investment to improve it. In my case I ended up using one of the caching template systems for my board because I saw some improvement and some of the engines that were even faster (including the back-ported phpBB3 template engine) all had bugs that I was not interested in figuring out at the time. To be clear, the phpBB3 template code didn't have bugs, but my attempt to back-port to phpBB2 certainly did.

You can also see what else you have to change. Back then I did an upgrade from php 4 to php 5 and saw a definite performance improvement because some of the functions that were heavily used in the phpBB2 template engine were more optimized in php 5 than they were in 4. On the other hand, you also run the risk that some functions could be slower after a php (or MySQL) upgrade. Not likely, perhaps, but certainly possible.

Adding an external php cache might help, which I think you had already mentioned you have done. You might check to see that it's actually working as you expect; perhaps the configuration for the cache could be optimized.

You can also try to make sure that your database is on a different hard drive than your php installation. If you have two (or more) hard drives in your server, putting your database on one disk and your app server (apache) on another would help optimize the load.

If anyone is at all interested in more numbers from the work that I did on testing the different template engines (several years ago, in 2008) I have a number of blog posts in the "performance tuning" area of my phpBB blog located here:

Code: Select all

www.phpbbdoctor.com/blog/

The work is probably interesting only from a historical perspective as none of the code that I was testing would be of interest to phpBB3 users.

[MattF]

PHP is program code. MySQL is data. Those times you see are the times for PHP to compile, process, execute code, assemble data and output HTML. The MySQL times are simply the time it took to locate data within the database.

Of course PHP will take more time... As naderman said, it is where all the work is happening.

[tornado_mtb]

Thanks so much drathbun , I don't have much experince on php , but as you said ,it look very interesting and hard work to deef tune back to phpbb2. I can not do like this since my board have to maintain many visitor. What I do is install APC. it help a little on loading time (like from 0.5 to 0.4 , 0.1 to 0.08). My harddisk use RAID5 with 5xSAS 15K RPM. I think performance alredy good. next step. if I can do to add more memory. Do you think it will help? another choice is upgrade server to faster cpu. which I think CPU Faster clk should better a lot core.

[drathbun]

More RAM helps assuming you use it. Faster CPU would make the php code execute faster.

What sort of site are you running? Meaning how many concurrent users do you have? My server regularly supports hundreds of concurrent users without problems, and I've seen over a thousand concurrent users during peak hours. Do you have more than that? Are people complaining about web site speeds? Half a second should not be that noticeable to your users.

[Oleg]

Scaling php part is a lot easier than scaling the database. If you are looking to improve performance, get a faster processor (clock speed, not core count).

[bantu]

Upgrading to the latest stable PHP branch (PHP 5.3 at this time) is usually a good idea.

If you have the RAM, using memcache should give you another small speedup.

Also, you could use a Debugger/Profiler like XDebug to see where PHP spends most of its execution time. Maybe it is something obvious that can be improved for your workload.