PlanetMysql.ru — информация о СУБД MySQL

Benchmarking tools like Sysbench and DBT2 has helped alot of DBAs in measuring their MySQL databases performance. By benchmarking you will really know how far your current setup will go. In this part, you will learn how to install the sysbench in Ubuntu and other Enterprise linux.

1. Installing sysbench to Ubuntu is never been easy as issuing the apt-get command. You can also download the source from sourceforge.

In ubuntu, execute below.


2. Installing to enterprise linux like Oracle Linux/ Red Hat Linux. Download the rpm file from rpmfind.net or any sites providing rpm downloads. You can also check this source.

3. Once downloaded try to install it if it will not find the dependencies file.


In this example, i used a 64-bit machine with Oracle Linux 6.0 installed. There are required dependencies before sysbench will be installed.

1. It will require that the machine has a GCC Compiler. To install it,

Yum will not work to unregistered linux, so to get this done you need to create a local repository.

2. Other dependencies


All these dependencies are available in your Linux DVD/ISO Installers.

Sysbench Test Modes

1. Create 'sbtest' database first
2. Prepare the database

This command will create 'sbtest' table and inserts 1M records

3. OLTP Test

OLTP Read Only

OLTP Read+Write
this command will run the actual benchmark with 16 client threads, limiting the total number of request by 100,000

4. CPU Test

5. Thread Test - This test mode was written to benchmark scheduler performance.

PlanetMySQL Voting: Vote UP / Vote DOWN

Alex Yurchenko finally posted results on a benchmark he has planned to do for a long time: Galera vs NDB cloud shootout.

Their blog requires registration to comment, so I'll post my comment here instead:

***

Sysbench can do the loadbalancing itself, so there is no need for external loadbalancer. Just add a comma separated list of master MySQL nodes to --mysql-host. This is similar to what the JDBC and PHP drivers can do too, and it is my favorite architecture. Why introduce extra layers of stuff that you don't need and that doesn't bring any additional value?

read more

PlanetMySQL Voting: Vote UP / Vote DOWN

If you’ve been tuning your MySQL database and have wondered what effect the innodb_flush_method settings have on write performance, then this information might help. I’ve recently been doing a lot of baseline load tests to show performance differences between localdisk and the new SAN we’re deploying. Since we run InnoDB for everything in production, and writes are very heavy, I decided to run comparison tests between two identical servers to find the best setting for innodb_flush_method. We have the following specs for the hardware:

• Dell R610
• 24 core Intel Xeon X5670 @ 2.93ghz
• 72GB ECC RAM
• Brocade 825 HBA
• Local disk: RAID-10 15K SAS Ext3 (ugh)
• SAN: Oracle 7420 with four Intel Xeon X7550 @ 2.00GHz, 512GB RAM, 2TB read-cache(SLC-SSD), 36GB write cache (MLC-SSD), 3 disk shelves populated with 60x2TB 7200RM SATA drives setup in mirrored format with striped logs, dual 8Gb FC links to redundant fabric, connected to Brocade DCX 8510-4.
• The my.cnf file being used for the tests: click-click

I’m using the following sysbench command to run the tests. On each server the same commands are used. I ran a 1B row prepare prior to the 1B row test.

sysbench –db-driver=mysql –num-threads=64 –max-requests=1000000000 –max-time=3600 –test=oltp –verbosity=3 –validate=off –oltp-test-mode=complex –oltp-read-only=off –oltp-table-name=sbtest –oltp-table-size=1000000000 –oltp-dist-type=special –mysql-host=localhost –mysql-port=3306  –mysql-table-engine=innodb run

On the server that is utilizing SAN paths there are two LUNS presented for MySQL use. /db/data01 for InnoDB data files, /db/logs01 for InnoDB logs. These filesystems are both formatted as XFS. The server running local-disk tests is running Ext3. I might run some more tests later with the local-disk setup as XFS if time allows.

Here are the results. Clearly a well designed SAN infrastructure is superior to even RAID-10 15K SAS drives. And of course you can see the different performance values from using O_DIRECT for the innodb_flush_method for the different data storage mediums.

1B Row Complex Transactional Test, 64 threads

• SAN O_DIRECT: read/write requests: 31560140 (8766.61 per sec.)
• SAN O_DSYNC: read/write requests: 5179457 (1438.52 per sec.)
• SAN fdatasync: read/write requests: 9445774 (2623.66 per sec.)
• Local-disk O_DIRECT: read/write requests: 3258595 (905.06 per sec.)
• Local-disk O_DSYNC: read/write requests: 3494632 (970.65 per sec.)
• Local-disk fdatasync: read/write requests: 4223757 (1173.04 per sec.)

PlanetMySQL Voting: Vote UP / Vote DOWN

If you’ve been tuning your MySQL database and have wondered what effect the innodb_flush_method settings have on write performance, then this information might help. I’ve recently been doing a lot of baseline load tests to show performance differences between localdisk and the new SAN we’re deploying. Since we run InnoDB for everything in production, and writes are very heavy, I decided to run comparison tests between two identical servers to find the best setting for innodb_flush_method. We have the following specs for the hardware:

• Dell R610
• 24 core Intel Xeon X5670 @ 2.93ghz
• 72GB ECC RAM
• Brocade 825 HBA
• Local disk: RAID-10 15K SAS Ext3 (ugh)
• SAN: Oracle 7420 with four Intel Xeon X7550 @ 2.00GHz, 512GB RAM, 2TB read-cache(SLC-SSD), 36GB write cache (MLC-SSD), 3 disk shelves populated with 60x2TB 7200RM SATA drives setup in mirrored format with striped logs, dual 8Gb FC links to redundant fabric, connected to Brocade DCX 8510-4.
• The my.cnf file being used for the tests: click-click

I’m using the following sysbench command to run the tests. On each server the same commands are used. I ran a 1B row prepare prior to the 1B row test.

sysbench –db-driver=mysql –num-threads=64 –max-requests=1000000000 –max-time=3600 –test=oltp –verbosity=3 –validate=off –oltp-test-mode=complex –oltp-read-only=off –oltp-table-name=sbtest –oltp-table-size=1000000000 –oltp-dist-type=special –mysql-host=localhost –mysql-port=3306  –mysql-table-engine=innodb run

On the server that is utilizing SAN paths there are two LUNS presented for MySQL use. /db/data01 for InnoDB data files, /db/logs01 for InnoDB logs. These filesystems are both formatted as XFS. The server running local-disk tests is running Ext3. I might run some more tests later with the local-disk setup as XFS if time allows.

Here are the results. Clearly a well designed SAN infrastructure is superior to even RAID-10 15K SAS drives. And of course you can see the different performance values from using O_DIRECT for the innodb_flush_method for the different data storage mediums.

1B Row Complex Transactional Test, 64 threads

• SAN O_DIRECT: read/write requests: 31560140 (8766.61 per sec.)
• SAN O_DSYNC: read/write requests: 5179457 (1438.52 per sec.)
• SAN fdatasync: read/write requests: 9445774 (2623.66 per sec.)
• Local-disk O_DIRECT: read/write requests: 3258595 (905.06 per sec.)
• Local-disk O_DSYNC: read/write requests: 3494632 (970.65 per sec.)
• Local-disk fdatasync: read/write requests: 4223757 (1173.04 per sec.)

PlanetMySQL Voting: Vote UP / Vote DOWN

I've been promising I should re-visit once more the disk bound sysbench tests I ran on Galera. In December I finally had some lab time to do that. If you remember what troubled me then it was that in all my other Galera benchmarks performance with Galera was equal or much better compared to performance on a single MySQL node. (And this is very unusual wrt high availability solutions, usually they come with a performance penalty. This is why Galera is so great.) However, on the tests with a disk bound workload, there was performance degradation, and what was even more troubling was the performance seemed to decrease more when adding more write masters.

In these tests I was able to understand the performance decrease and it had nothing to do with Galera and not even InnoDB. It's a defect in my lab setup: all nodes kept their data on a partition mounted from an EMC SAN device - the same device for all nodes. Hence, when directing work to more nodes, and the workload is bottlenecked by disk access, naturally performance would decrease rather than increase. Unfortunately I don't currently have servers available (but will have sometime during this year) where I could re-run this same test with local disks.

As part of this lab session I also investigated the effect varying the number of Galera slave applier threads, which I will report on in the remainder of this post. Of course, the results are a bit obscure now due to the problematic lab setup wrt SAN, but I'll make some observations nevertheless.
While the previous tests were run on MySQL 5.1, this test was run on MySQL 5.5 and I will make some observations there too.

read more

PlanetMySQL Voting: Vote UP / Vote DOWN

Here are the slides to my first talk at Percona Live UK 2011: Choosing a MySQL High Availability solution.1

1. 1. See this for a review of the conference as a whole: http://openlife.cc/blogs/2011/october/thanks-percona-and-attendees-great-percona-live-uk-2011

read more

PlanetMySQL Voting: Vote UP / Vote DOWN

People commenting on my results for benchmarking Galera on a disk bound workload seemed to be confused by the performance degrading when writing to more than one master, and not convinced at my speculations on the reasons. Since sysbench 0.5 has the benchmarks in the form of LUA scripts, it was temptingly easy to tweak those a little to see if my speculations were correct. So yesterday I did run tests again with a slightly modified sysbench workload. (Everything else is identical, so see previous article for details on the setup.)

So my analysis of the previous results were that

read more

PlanetMySQL Voting: Vote UP / Vote DOWN

An important part of benchmarking is to draw graphs. A graph can reveal results you wouldn't have spotted just by looking at raw numbers. By the way, the process of massaging the raw numbers into graphs will often reveal things too.

Sysbench output tends to be quite wordy, especially when you have a script that runs 1, 2, 4, 8... threads with the same test. To manually copy paste the numbers into a spreadsheet is tiresome. So I came up with this monster shell one-liner to condense the output into a csv file. I'm posting it here so I will find it the next time I need it:

read more

PlanetMySQL Voting: Vote UP / Vote DOWN

Recently I tested our new segmented key cache feature for MyISAM in MariaDB 5.2.2-gamma for performance gains. You can check our new features in MariaDB 5.2 in our Ask Monty Knowledge Base

• What is in MariaDB 5.2

You will also find the details about the segmented key cache feature in our Knowledge Base at:

• Segmented key cache for MyISAM

We wrote a test in LUA for SysBench v0.5 called select_random_points.lua, to figure out the performance gain of splitting the key cache’s global mutex into several mutex under multi user load.

You can find all the details about the benchmark in our Knowledge Base article here:

• Segmented key cache performance

The results were quite surprising, because we found up to 250% gain depending on the amount of concurrent users. On a side note we also found out that our new Intel system with 24 virtual cores has more than a 10 times performance advantage compared to our older 4 core system.

PlanetMySQL Voting: Vote UP / Vote DOWN

These are some of my notes from some sysbench in-memory r/o testing in past day or so:

• At ‘fetch data by primary key’ benchmark with separate read snapshots at each statement, MySQL shines until ~200 concurrent threads, then performance starts dropping slightly faster than one would want, I think mostly from table cache LOCK_open contention
• auto-commit cost (establishing read snapshot per statement) for SELECTs is ~10% for MySQL, but for PG it can be +50% in plain SQL mode and +130% (!!!!!!!) when using prepared statements (this can be seen in a graph – obviously the global lock PG has during this operation is held for too long and maybe is too costly to acquire.)
• Some benchmarks went up by 10% when using jemalloc
• MySQL could accept 10x more connections per second than PG (15000 vs 1500)
• Most confusing behavior MySQL exhibited was at 100-record range scans in PK order:
  • At innodb_thread_concurrency=0 it did around 70k range reads, both fetching data and aggregation (SUM())
  • At innodb_thread_concurrency>0 it did only 10k range reads returning data but still was able to do 70k aggregations/s
  • PG was doing ~35k ops/s at that test

  It seems that at least for systems that do lots of range scans (or joins) I guess, managed concurrency kills performance entirely due to giving up tickets too often, need to review it more.

PlanetMySQL Voting: Vote UP / Vote DOWN