Installing an older version of mysql?

Problems with the Linux version of XAMPP, questions, comments, and anything related.

Installing an older version of mysql?

Postby matteosistisette » 18. September 2012 00:23

Hi,

On a real server I have MySQL 5.0.77 and it's working fine.
On my local machine I have installed the latest xampp 1.1.8-beta1 which has MySQL 5.5.25 (and had previously tried 1.1.7 which had MySQL 5.5.16) and I'm experiencing tremendous performance issues on the local machine. Some slightly complex queries that on the real server (old MySQL) take just a few milliseconds to complete, on my local machine (new MySQL) take about 60 seconds at 100% CPU on an identical copy of the database.

So either there is an infamous regression in MySQL somewhere between 5.0.77 and 5.5.16, or there's something in XAMPP or in my machine that is preventing MySQL from working decently.

That's what I want to figure out, so I need to install MySQL 5.0.77 or older on my local machine to work with XAMPP, but I'd like to keep the rest of XAMPP current (i.e. php version, phpmyadmin version, apache version), rather than install an old version of XAMPP altogether.

Is it very complicated to replace mysql with a different version? How would you do that?

thanks in advance
m.
matteosistisette
 
Posts: 38
Joined: 11. January 2011 12:22
Operating System: Windows 7 64bit; Ubuntu 11.10

Re: Installing an older version of mysql?

Postby JonB » 18. September 2012 01:04

I am not saying its impossible. I will say you are on your own, and it would be quite difficult. XAMPP does not support swapping out of hey components; i.e Apache, PHP, MySQL. For a variety of reasons, some of the other components like Perl or phpMyAdmin are do-able if you have patience and a bit of knowledge. That is really because they are very loosely tied in, not so for the 'Big 3'.

XAMPP basically is a suite, not the raw components. It isn't really even a LAMP Stack, as the OS and 'installers' have no idea that XAMPP or any of its components are installed. Its actually a bash script. You should read it. Open /opt/lampp/lampp in a programmer's editor and set the language to bash/sh -- you will see its all command line stuff. Good luck on getting that all correct.

That is where the problem comes in, you would have to manually configure and compile MySQL to get it to work, and then weave that back into lampp.

Sorry to rain on your parade.

Good Luck with your projects.
8)
User avatar
JonB
AF Moderator
 
Posts: 3210
Joined: 12. April 2010 16:41
Location: Land of the Blazing Sun
Operating System: Windows XP/7 - Fedora 15 1.7.7

Re: Installing an older version of mysql?

Postby matteosistisette » 18. September 2012 02:28

Thank you for your reply.

I have been able to test an old version of xampp (1.6) with MySQL 5.0.37 and it exhibits the same shamefuily degraded performance.

So, the problem doesn't seem to be MySQL version at all. For some reason, MySQL on my real server (a CentOS 5 server) works fine while xampp's MySQL on on Ubuntu performs awfully. And it's certainly not because of the machine's hardware (I think the server has actually a slower processor than my laptop, and I doubt it has much more RAM).

Does anybody have any idea what can be causing xampp's MySQL on my desktop machine to misbehave like this and how I could fix it?

Is it perhaps that it must be run with some particular configuration (which perhaps is default on "real" servers) in order to get more resources?

At first sight It doesn't look like the problem is RAM, because if it was, I would expect to notice frenetic hard disk activity (swapping) when the mysql server is responding, and not that much CPU use; but that is not the case: I get 100% CPU usage and no noticeable disk activity.
matteosistisette
 
Posts: 38
Joined: 11. January 2011 12:22
Operating System: Windows 7 64bit; Ubuntu 11.10

Re: Installing an older version of mysql?

Postby matteosistisette » 18. September 2012 03:39

OH MY GOD I fixed it by simply editing my.conf and increasing innodb_buffer_pool_size from 16 to 1024M; also I commented the "skip-innodb" line.

I am a bit confused as to which of these settings was the cause of the issue.

My guess was that skip-innodb had no effect, otherwise I would expect my database with a lot of innodb tables to have never worked with skip-innodb.

If so then the relevant thing should be the buffer pool size. The hint in the file says "you can set the size to about 50%-80% of RAM", so 16M looks like a RIDICULOUSLY small value.

But my online server, which always worked fine, doesn't have any of these settings in my.cnf, which suggests it is using default values. And documentation says the default value of innodb_buffer_pool_size is 8M (for MySQL<=5.1.27 which is the case).....

Can anybody enlighten me?
matteosistisette
 
Posts: 38
Joined: 11. January 2011 12:22
Operating System: Windows 7 64bit; Ubuntu 11.10

Re: Installing an older version of mysql?

Postby JonB » 18. September 2012 14:05

Yes -

When these guidelines/settings were originally developed, there was a huge gap in computing power and memory between 'local' resources (read your average PC) and servers you might find in datacenters. That is still true (because there are 24 core Xeons and SSD arrays) BUT the average PC now dwarfs the average datacenter 1 or 2 unit server of 8-10 years ago.

The problem is in how old the defaults are.

OTAY?
8)
User avatar
JonB
AF Moderator
 
Posts: 3210
Joined: 12. April 2010 16:41
Location: Land of the Blazing Sun
Operating System: Windows XP/7 - Fedora 15 1.7.7

Re: Installing an older version of mysql?

Postby matteosistisette » 23. September 2012 12:37

That makes a lot of sense, but there's something that doesn't quite add up.

On my local machine, xampp's original settings in my.cnf had innodb_buffer_pool_size=16M. Ok this was ridiculously small, so I increased it to 1024 and it works like a charm. So far everything makes sense.

Then, on my server, where everything has always worked fine and I never touched anything, I would expect to find innodb_buffer_pool_size somewhere bigger than 16M, right?
However, there's no such setting on the server's my.cnf. So, it means it uses mysql's factory defaults. So I look at MySQL documentation and it says that the default for versions prior to 5.1.27 (and my server has 5.0.77) is 8M.

So, if the local machine had troubles with 16M and they went away increasing it to 1024, how comes the server works perfectly with just 8M???
matteosistisette
 
Posts: 38
Joined: 11. January 2011 12:22
Operating System: Windows 7 64bit; Ubuntu 11.10

Re: Installing an older version of mysql?

Postby matteosistisette » 25. September 2012 17:07

I'm finding out that the configuration change on my local machine hasn't fixed the problem at all. I thought it had, but it hadn't.
It's just that sometimes, after restarting xampp, it works well for a while (and I thought it was because of the changes made in the configuration), but then, at any (apparently) random time, it starts being slow again. I never observe this slowness on the online server (with the same data)....

I'll post this as a separate question as it is unrelated to the original topic.
matteosistisette
 
Posts: 38
Joined: 11. January 2011 12:22
Operating System: Windows 7 64bit; Ubuntu 11.10


Return to XAMPP for Linux

Who is online

Users browsing this forum: No registered users and 38 guests