MySQL useability and performance

Krokodox · Fri Apr 07, 2006 9:14 pm

Hi there!

First of all, i would like to say that you have created what seems to be an impressive piece of software. Which is why I am considering to use it in our production environment, if it fits our needs.

I have some questions that I think you will be able to straighten out for me, my scenario for this setup is as follows:

1. I need to setup a MySQL environment for a web service. As it is today, it consists of several HTTP front-ends serving data from 7 separate computers (2.8 Ghz / 1 GB RAM / single SCSI harddisk) running MySQL on Windows 2003. This means that I am sitting with 7 single points of failure. Not so good!

2. We have now purchased 6 new DELL 2850 servers, each equipped with 6 SCSI disks connected to a 2-channel (onboard) HW RAID adapter. The servers have 2 x 3.0 GHz CPU / 2 GB RAM / 2 x 1 Gb LAN ports each.

Our goal is to reach high availability (HA) at a reasonable cost. We could set the servers up in a MySQL master / slave configuration, but it is not as good as it sounds. Therefore a SAN storage approach might be better, and this is where StarWind could come handy:

One approach would be to have one of these servers as a primary iSCSI target with the database files, an other as a secondary iSCSI target with a some sort of replica of the database files, and the remaining 4 as MySQL service servers / iSCSI initiators.

The two iSCSI target servers would be connected via both their interfaces to a Gb switch, and the four initiator servers would be connected to the same switch with one LAN interface each (their other LAN interface would be connected to an other switch where also the HTTP servers would be connected:

Code: Select all


                     +--|initiator|--+            ++==|target|--+
|HTTP|--+            +--|initiator|--+            ||            |
|HTTP|--+--|switch|--+--|initiator|--+--|switch|==++            |
|HTTP|--+            +--|initiator|--+            ||            |
                                                  ++==|target|--+

The targets would be interconnected via an add-on LAN interface for the primary / secondary communication in order to keep them synched.

If for some reason one of the iSCSI initiators would fail, we would just start up an other session of MySQL on one of the remaining servers, point it to the LUN of the failed server and have the HTTP servers ask the "new" MySQL server for data.

If for some reason one of the iSCSI targets would fail, we would re-direct the initiators to the other iSCSI target and thus keep the business up-and-running.

Now after this exhausting introduction comes my questions

:

1. Have you had any experience with MySQL (or some other serious database server) data being stored on your iSCSI target in regard of performance / latency / usability?

2. Can your iSCSI target handle 5.000 transactions / second? (80% reads / 20% writes). Is this setup meaningful or would it suffer badly from the transaction rates?

3. Can your iSCSI target handle up to 10.000 files open simultaneously?

4. Is it possible to set up a primary / secondary configuration of the target that is transparent for the service (MySQL in this case)? I do not need automatic failover or other fancy automagic stuff, only that the data is available from an other independant sources in case the first fails.

5. Is it possible to re-synchronize the targets without halting all MySQL traffic after a failure resulting in a switch from the primary to the secondary iSCSI target?

6. Is it an option or a no-no to distribute the MySQL queries between the primary and the secondary iSCSI targets so that the primary would receive all writes and all reads would be distributed between the primary and the secondary? (this question is not MySQL specific, we already have this setup in production, the question is whether this kind of distribution of reads and writes messes up the iSCSI synchronization / replication process)

7. Can LAN adapter teaming / aggregation be used on the iSCSI targets in order to achieve higher throughput? Is it worth doing or does it just increase the CPU usage leaving very little CPU left for the transactions?

8. And finally: is this setup realizeable, i.e. does it make sense or should we look at some other solution for our HA cravings?

I would be really thankful for your answer, as it would help me to decide to try out this setup. If this iSCSI solution would yield good performance with the presence of HA then I would not hesitate to implement it into our environment.

Many thanks in advance for your help and efforts in answering my questions, I am looking forward for your answer!

//Tamas

Val (staff) · Sat Apr 08, 2006 11:22 pm

Hi Tamas,

Using of 2 independent servers with StarWind installed can't be used for synchronization/replication of the iSCSI disks.
Have you already thought about building a MS cluster from the 2 storage servers? It looks like a working solution as for your description.
But this configuration should use a 'real' shared storage (like 2-way SCSI disk). The shared storage will keep the quorum disk and data volumes shared by the 2 clustered StarWind instances.
Your MySQL servers will use the cluster instead of 2 separate storage servers, so their configuration will be much simplier.

According to your questons:
1) We have not specially tested databases with StarWind, but our performance tests with IOMeter show good results.

2-3) It depends on the disk subsystem that stores your iSCSI targets.
'transactions' and other terms are not relaited to storage devices (like iSCSI disks). Also storage devices have nothing to do with 'files'.
Filesystems use sector-level access to storage, while databases use filesystems to read/write their data files.

4-5) As I said using a cluster should help with these tasks.

6) Starwind does not support this itself. In a 2-way MSCS configuration only one machine is active at a moment, so load-balancing of requests is impossible.

7) NIC teaming / aggregation works well with iSCSI. But the disk subsystem of the target should be fast. I guess a single 1GbE connection is enough for most of existing disk subsystems.

You could download the evaluation of StarWind and test some scenarions you need with it (throughput, etc).

I hope this helps.