I've been told that some people have some questions regarding MySQL on PPC.

Please ask here your questions and I'll be happy to answer them.
About me:
I'm a certified MySQL expert with the history of developing for MySQL and adding PowerPC optimizations for the MySQL server engine.


Some answers to frequently asked questions:

MySQL on PowerPC:
MySQL runs both stable and with good performance on PowerPC.
There is no difference between running MySQL on PowerPC or running MySQL on a comparable x86 machine.


Interoperability for other (x86) MySQL servers:
- Any client (x86 or not) can access and normally use a PowerPC MySQL server.
- MySQL databases are stored endian neutral.
This means you can even copy the raw database files from your old x86 server and start them on your new PowerPC server.
- If you need several database server either replicating or load balancing, then you can freely mix x86 with PowerPC servers.
- One exception are clusters using the MySQLCluster
engine. Here you need the same endianess per cluster!


Here are some simple golden rules to get best performance out of MySQL.
Most of the rules apply to other databases like PostgreSQL too.

The rules are a bit simplified but should help to understand what you can do.

- max out the Memory
rule of thumb: for 5GB data you need 8GB mem.

- max out the CPU clock.
MySQL scales very good to two cores and quite good up to 4 cores but single clock rate is always preferable.
MySQL will runs *much* better on two cores with 2 Ghz each than on 4 cores with 1 Ghz each.

- never swap
MySQL is very sensitive to IO bottlenecks.
If your loaded database server ever starts swapping then all is lost.

- be *very* carefull with running other tasks on your database server.
Depending on the used table types, MySQL will heavely rely on the OS disk cache.
If you use such a table type (default) then running processes which could trash your disk cache will ruin the database performance.
Logfile parsing, long greps, or file serving - this will trash your disk cache and will have huge impact on your database.
If your database setup relies on regular tables scans then thrashing your cache will often result in piling up numbers of in parallel running competing table scans.


I'm looking forward to your questions


Gunnar

Thanks Gunnar, this is valuable information about MySQL!

2x 2.5GHz G5 (or even 2x2.0) would also
be a very fine workstation for desktop use.

So - how does MySQL performance behave comparing PPC32 to PPC64?

AFAIR you already even did some Altivec-optimizing.
Are there any news on that?

I believe that MySQL will experience a huge performance leap from the ODW to OSW.

Many of the differences of the PPC64 to PPC32 will be benefitial to the database performance.

The first difference between PPC32 and PPC64, that everybody notices, is that the integer registers are twice as big now.
MySQL works internally a lot with big integers (64bit).
The 64bit PPC64 register will speed up all the computing functions. But in the big picture of overall database performance this will only be a small improvement.

The 64bit registers give the PPC64 a much bigger address space that will allow to completely memory map all the database files. This will simplify the IO and improve the performance. This should result in a real speed up.

I think the biggest plus will be the increased memory throughput. The MySQL database performance depends very much on the memory throughput. The ODW was a bit limited on that side. I have not had my hands on the OSW yet, but looking at other G5 systems I'm hoping for a big speed up, maybe in the range of up to 5 times here.

I believe that the OSW will be an excellent MySQL server. The increased memory throughput, the bigger address space - allowing to better map the database files and the possibility to add more memory should result in superb MySQL performance.



MySQL is known to be very fast and it runs very good on PPC.
Its clear that an OSW with MySQL will be an impressive database server. It will be able to hold its ground and compete well against all other servers on the market.


While its clear that we will get very good results on the OSW out of the box, we still have a very good tuning potential on the PowerPC.

Often algorythms are designed without taking special powers of certain CPU families into account. By using Altivec or other general PPC specific features, we will be able to improve a number of functions in MySQL and other applications.


To answer your Altivec question:
We know that Altivec can give impressive speed ups if the problem can be solved by a vectorized function.

MySQL is of course a very complex program and not as easy to vectorize as an RC5 algorythm. MySQL utilizes hundreds of functions and operates a lot on structures which are often not good aligned for Altivec usage.
Because of this, adding general performance improvements to the MySQL server by using Altivec is difficult. But vectorizing some single functions showed impressive results: 400-800% speed ups were no problem.

One MySQL usage case that we had was limited (bottle necked) by a single functions which was vectorizeable. By Altivec enabling this single function we got a 400% total server performance increase.

Improving the performance for a few functions was quite easy. But improving the general server performance will be much more work - we are not quite there yet.

But I have to say, that we mainly looked at functions which were easy to improve without the need to redesign huge parts of MySQL backend. Simple drop in replacements so to say. We can surely achieve much bigger speed ups if we change bigger parts of the backend and for example optimize the way certain data structures are internally organized into formats which are better to handle with Altivec.


In total, I'm expecting huge MySQL performance increases by the new hardware.
Altivec showed potential but its a big task to optimize such complex programs as MySQL.
I'm sure putting more effort in optimizing the new MySQL storage engines for PPC64 will be a very rewarding project.



Cheers
Gunnar

Let me give you another example for the PowerPC tuning potential that we have with Linux.

A key factor for the performance of many applications is the memory throughput. This is one field where Linux does not use of the potential of the PowerPC yet.

Here are some benchmarks on the ODW showing you the potential:

Memory thoughput of moving big memory blocks of 80 MB
(uncached memory). This benchmark shows the efficience of different copy routines on the 1Ghz G4 ODW.

Standard glibc 374 MB/sec
Standard STREAM 384 MB/sec
selfmade Non-Altivec 533 MB/sec
Freescale Altivec 657 MB/sec


You can see that Freescales Altivec routine improve the performance to 175% in comparition to the normal Linux glibc routine.

This should give you a rough idea by how much you can easely improve the performance of many applications.

Cheers

Hi Gunnar,

may I ask you about your experience with MySQL and virtualisation?

F.ex. how big are the performance impacats when using XEN (or UML when you know that too) when you are running 2 MySQL "partitions" on the server? Only "half as fast" as normal or will the virtualisation have a huge impact?

thank you,
M

Hi MeyerM,

I can not give you can "exact" percentage here, out of my head.
I will need to run some db-benchmarks to be able to give you some good numbers.
My experience so far, is only based on some real projects were we worked on consolidating numbers of under utilized linux webserver/db-servers onto bigger virtualized machines.

Virtualization will always be challenged by applications like databases. This is very easy to understand if we think about how a database works. A database usually works on huge amounts of data on disk and tries to optimize disk IO and memory cache to archive good performance.

One real example:
Often you have aggregate queries that need to read/search over big tables on disk. This is a big IO requirement and very often you have many simultanious queries with IO needs on your database server.

If you now run 10 databases on one big server with 100 simultinous queries then the database backend is aware of the estimated IO requirements of *all* queries and can schedule and prioritize them to make best use of all the available cache ram and max out the IO disk throughput.

If you now split the server into 10 virtual servers then each virtual database server has much less overview and much less cache to use.
Such a virtual server is not aware of the IO needs of the other servers and can of course not take them into account. This means that the virtualized server will naturally make much worse usage of the cache and create worse IO plans.

With every virtualization/splitting of the database server you are reducing the overview of the server backend and its ability to plan.

When we look at it this way then its very easy to understand that a real server will always give the best database performance.

A database has often several ways to solve a problem.
Typically a database can choose to solve problems much faster using more memory or slower using less.
Over simplyfied a server often has the choice to run one query in 0.01 seconds utilizing XXX MB of cache or in 1.0 seconds using less cache. If you now have 10 such queries on one big machine then the backend can schedule them one after the other to finish in 0.10 seconds total. If you run the same queries seperately on 10 smaller servers then the small servers might have no choise but to run them in 10x1 seconds.

To answer your question:
As a rule of the thumb as more you divide the server as more the server will loose on overview and performance. My simple estimate is: The total performance of 4 v-servers on one machine will be 25-50% of the performance of using this one machine as one real server.

I hope this helps.

Cheers
Gunnar

Hello Gunnar,

thank you very much for your detailed answer. Very appreciated . So I will restrict myself to use virtualisation only for developing and can save the time benchmarking different setups.

Cheers too
Marcel

Hi Marcel,

Just one more thing regarding the MySQL virtualization:

I don't want to mislead you here.
The OSW will be a real fast machine and MySQL is running fast out of the box anyway.
So if we are talking about "small" databases and virtualization in the one digit range then no-one will feel a difference between a single server or virtualized servers.


A beefed up quad core OSW will make an excellent database server capable of literally running hundreds or a thousand user databases.
You can of course not scale that high using virtualization.
If you virtualize then 20-30 database v-servers will be a sensible limit per physical machine.

So if you run some real busy databases or if you want to run a few hundred databases then virtualization is not a good choice. But if you only need want to create a few databases server, (<20) with little traffic then virtualisation should work quite well.


Cheers
Gunnar

I fully agree with Gunnar: having lot's of small virtualized MySQL servers makes no sense from the performance point of view. He also mentiones, that it's better to avoid running other software on a server hosting MySQL.

From my experience: a p4 Xeon can callopse under the load of having MySQL and the PHP web application on the same server. Separating tasks to two ancient p3 machines can handle about 10 times more hits.

Exactly for this reason, I see often, that those who co-locate a machine and have higher traffic sites, buy place for a second machine.

Here is where Xen and multi core/cpu machines come into the picture. Single database server performance is just about the same inside or outside of Xen. Remember, Xen is optimized for quick RAM access, and that's what MySQL needs. Xen is also able to tie guest machines to specific CPU(s).

When you consider this, you will see, that with an OSW and Xen one has the perfect web hosting solution in ONE machine. One CPU is dedicated to I/O, and the rest is divided between between Apache and MySQL. Depending on the use, it might be 2 CPU to MySQL, one to Apache, or vica-versa. As both CPU and RAM are dedicated to the different applications, there is no real competition between them. I have yet to test it with OSW (for practical reasons), but it worked fine in my test environment.

BTW: I'm speaking now against myself, as I have a small part time job at a web hosting / co-location provider...

CzP

Thank you again for your clarifications. My question was indeed targeted towards getting a single, big MySQL-daemon to run on a physical machine within a virutal machine to be able to seperate it from other processes in the physical machine (that's a bad idea to run other stuff on this machine, I know - but it wasn't mine ).


So to summarize: the virtualisation itself (at least using xen) is not the big problem when the database runs within it. Putting other processes onto the machine would be usable, when it is a SMP system where I can "glue" the xen-instances to a special core/cpu (I hope I understood this one right - I didn't know I would be able to do that. Guess it's time to decide me for XEN and RTFM ).

Thanks,
Marcel