Is there a perfomance gain on the language chosen and DB?

Question

Hello all,

We have our current system using PHP and Microsoft SQL Server.

I am trying to work out if there will be a performance gain in using PHP and MySQL?

It will require some effort to change to this platform so I am hoping to find out as much as I can. Are there any questions I should be asking myself to determine the correct choice?

Thanks all

More Info

• 100 of Millions Records Processed Reguraly (monthly)
• PHP + MS SQL Server run on the same machine
• Windows Enivronment

Clarification

I was initially hoping for this swap to be a magic solution, obviously not!!

To keep this thread useful, can we concentrate on what effect switching to MySQL or any other DB system would have on an overall application that has been finely tuned and nothing else can be done!

Answer 1

You may want to profile your application first, and determine how much time is being spent in the database, and how much time in each query.

Then it is easier to answer, as you can say that the application spends 3% in the database, and out of that 27% are in selects and the rest are inserts that involve triggers.

Also, which OS would MySQL be on?

Is it on a separate computer than the PHP server?

What is your current architecture like?

If you are looking at the fact that both your webserver and db server are on the same computer, and then you separate them for MySQL, and you get a performance increase, it is most likely due to the fact that the single cpu on your first computer doesn't have to do double duty now.

Which version of PHP? Would that be changed?

Are you using the mssql functions, odbc functions or pdo currently?

Answer 2

In most cases, I'd wouldn't start thinking about swapping a database back-end for performance reasons until I was sure everything else had been exhausted.

RDBMSes are sophisticated animals. Before you consider switching them out, take a good look at your current database. Even better -- pay a specialist with SQLServer experience to do so. Badly configured databases are everywhere.

You might see a performance increase switching, or you might take a hit. Until you know that your current database is properly tuned, it's all just guesswork.

As James Black just answered, the right way to increase performance is to profile your application, and figure out where it's spending its time.

Answer 3

The most important thing to check on the database side is proper use of indexes. Check your queries using EXPLAIN PLAN to see if they're doing TABLE SCAN. If they are, add indexes to improve performance.

I would seriously doubt that swapping SQL Server out and MySQL in would make any difference at all. If it were that simple, and MySQL had such an across-the-board performance advantage over SQL Server, SQL Server's market share would drop to zero overnight until the discrepancy was rectified. There's no way that such a difference could be hidden with the Internet in place.

The recommendation to profile the overall app is excellent. You can check your queries while you wait for that to happen.

Answer 4

Along with what's been said already, I think it's worth mentioning that MySQL isn't restricted to a single set of characteristics in this regard -- there are several different storage engines you can use with MySQL, and each has a different set of performance characteristics. MySQL with MyISAM acts quite a bit different from MySQL with InnodDB, and MySQL with Infobright is quite a bit different from either one (and, of course, there are others as well).

Answer 5

If you have a database that large that has performance problems, the first thing you need is hire a database professional who can performance tune it. Converting to a new backend is likely to create new problems as well as unlikely to solve the old ones if you don't have anyone on staff whose expertise is database development and performance tuning.

SQl Server can easily handle terrabyte-sized databases if the person who designs and maintains them know what he or she is doing.

I will point out that nothing but SQL Server should run on a SQL Server machine especially if you are expecting high performance. Just that statement alone makes me think you are likely to have a seriously underpowered system for what you are asking it to do. I would never consider running our apps on the same server as our database.