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

(Note that this is a copy of the original article from Hasham Pathan posted on 21st April 2009).

Summary of Results:

We recently conducted a Performance Benchmark of the newly released version of MySQL Cluster database software version 7.0 using the DBT2 Benchmark. The results are very impressive.

Highlight of the results:

• For 4 Node Cluster, we achieved 251,000 Transactions per minute which is more than 4X improvement over the MySQL Cluster 6.3 release.
• For 2 Node Cluster, we achieved 143,000 Transactions per minute which is more than 4X improvement over the MySQL Cluster 6.3 release.
• Whether a user is looking to take advantage of the latest multi-core, multi-thread server processors, or is seeking to reduce space and energy consumption of their Cluster hardware, MySQL Cluster 7.0 provides a higher throughput, more efficient solution than previous generations of the MySQL Cluster database.

Note that these results were obtained using 1GB Gigabit Ethernet. We expect improved performance for high speed cluster interconnect like InfiniBand and Dolphinics interconnect solutions. Testing using these interconnects is currently underway.

Some of the KEY features of MySQL cluster 7.0 include “ability to add nodes and node groups online” and “Data node multithreading support” You can look at the list of new feature available in MySQL cluster 7.0 here.

Deployment Architecture and Configuration Details:

In the case of a 2 node scenario, the data node processes were running on TWO Sun Fire x4450 system with 8 processor cores per data node. The MySQL server nodes were running a combination of Sun Fire x4450 systems and Sun Fire x4600 systems as shown in the deployment diagram below.

 

 

 

 

 

In the case of a 4 node scenario, FOUR x4450 system were used to deploy the Data Nodes, each data node using 8 cores. The MySQL Server nodes were running on a combination of TWO Sun Fire x4600, ONE Sun Fire x4240 and FOUR Sun Fire x4450 systems.(co-located with data node systems). Other relevant details are captured in the deployment topology diagram.

 

 

 

 

 

Detailed Results:

Important Notes:

1. Each MySQL server was bound to 3 processor cores using “processor set” feature(to accommodate more MySQL Servers on the available hardware). It is observed that we get increased TPM/MySQL Server and slight decrease in the response time with 4 processor cores per MySQL server). An example: With 3 processor cores assigned for the MySQL server process, we get the TPM of 12988 for 1 Server, 20 Warehouses. With 4 processor cores, the TPM increases to 19113 with decrease in the response time from 52ms to 34ms.
2. TPM – New-order transactions per minute. It should be noted that the each transaction comprises of about 30 queries, so average response time per query if calculated would be ~3ms. More details on the “transaction” are available in the TPC-C specification document and DBT2 code base.
3. The MySQL 6.3 Cluster saturate with fewer number of MySQL servers. For comparison purpose, we ran the tests with equal number of MySQL servers for both MySQL Cluster 6.3 and 7.0

Benchmark Description

Database Test 2 (DBT-2) : DBT2 is an open source benchmark developed by OSDL (Open Source Development Labs ). Significant updates to the benchmark were made to simplify its ability to run with a clustered database such as MySQL Cluster Carrier Grade Edition. DBT2 simulates a typical OLTP (Online Transaction Processing) application that performs transactions with around ten to twenty SQL statements per transaction within five distinct transaction types. The DBT2 benchmark can be used as a good indicator in regards to the expected performance of any application that performs fairly simple transactions and executes these transactions in a repetitive fashion.

For the benchmark results above, DBT2 and MySQL Cluster were configured as an “in memory” database to simulate typical “real-time” database configurations. It should be noted that performance results are being measured as new-order transactions per minute (TPM). The changes made to the DBT2 benchmark are documented and can be found on the SourceForge page for DBT2 downloads. It can also be downloaded from ww.iclaustron.com.

Additional Information:

More information on MySQL Cluster

• MySQL Cluster 7 Architecture and New Features
• MySQL Cluster Evaluation Guide

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

Here is the code structure dbfuncs.php : is the file which contains classes and functions for firing queries on mysql and pgsql mysqlinsert.php : creates and fires inserts on mysql mysqlselect.php : creates and fires selects on mysql pgsqlinsert.php : creates and fires inserts on pgsql pgsqlselect.php : creates and fires selects on pgsql benchmark.php : script used to control concurrency and
PlanetMySQL Voting: Vote UP / Vote DOWN

Here is the comparison between mysql and postgresql for selects (only). I had used the same table that i had created earlier http://jayant7k.blogspot.com/2010/09/database-speed-tests-mysql-and.html while comparing insertion speed. I have created approximately 1,000,000 records in the table and ran selects on them. I also modified the configuration of both mysql and postgresql to enable faster
PlanetMySQL Voting: Vote UP / Vote DOWN

There has been major changes in mysql and postgres over a couple of years. Mysql has been focusing on improving and optimizing innodb. Postgres on the other hand has been focusing on database replication and hot standby. Recently postgres came out with version 9.0 which has built-in replication and hot standby - the two most requested feature in postgresql. Earlier people used to shy away from
PlanetMySQL Voting: Vote UP / Vote DOWN

Here are some quick commands for installing the proper packages and requirements for the MySQL dbbenchmark program.

export PKG_PATH="ftp://openbsd.mirrors.tds.net/pub/OpenBSD/4.7/packages/amd64/"
pkg_add -i -v wget
wget http://dbbenchmark.googlecode.com/files/dbbenchmark-version-0.1.beta_rev26.tar.gz
pkg_add -i -v python
Ambiguous: choose package for python
 a       0:
         1: python-2.4.6p2
         2: python-2.5.4p3
         3: python-2.6.3p1
Your choice: 2

pkg_add -i -v py-mysql
pkg_add -i -v mysql
pkg_add -i -v mysql-server
ln -s /usr/local/bin/python2.5 /usr/bin/python
gzip -d dbbenchmark-version-0.1.beta_rev26.tar.gz
tar -xvf dbbenchmark-version-0.1.beta_rev26.tar
cd dbbenchmark-version-0.1.beta_rev26
./dbbenchmark.py --print-sql
 - login to mysql and execute sql commands
./dbbenchmark.py

PlanetMySQL Voting: Vote UP / Vote DOWN

At Kscope this year, I attended a half day in-depth session entitled Data Warehousing Performance Best Practices, given by Maria Colgan of Oracle. In that session, there was a section on how to determine I/O throughput for a system, because in data warehousing I/O per second (iops) is less important than I/O throughput (how much actual data goes through, not just how many reads/writes).

The section contained an Oracle-specific in-database tool, and a standalone tool that can be used on many operating systems, regardless of whether or not a database exists:

If Oracle is installed, run DBMS_RESOURCE_MANAGER.CALIBRATE_IO:

SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO(<DISKS>, <MAX_LATENCY>,iops,mbps,lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
dbms_output.put_line('max_mbps = ' || mbps);
end;

For us MySQL folks, or even the Drizzle or NoSQL folks, Oracle offers a free standalone tool called Orion. The example given in the slides was:

./orion –run advanced –testname mytest –num_small 0 –size_large 1024 –type rand –simulate contact –write 0 –duration 60 –matrix column

-num_small is 0 because you don’t usually do small transactions in a dw.
-type rand for random I/O’s because data warehouse queries usually don’t do sequential reads
-write 0 – no writes, because you do not write often to the dw, that is what the ETL is for.
-duration is in seconds
-matrix column shows you how much you can sustain

I would be interested to see how other folks measure I/O throughput, and maybe even do a side-by-side comparison of different tools. Orion is available for:

Linux (x86, x86-64, Itanium, Power)
Solaris (SPARC64)
AIX (PPC64)
zLinux
HPUX (PA RISC, Itanium)
Windows

I am working on a larger write-up of the session itself, which had many concise descriptions of data warehousing issues, but I thought that this merited its own post.

PlanetMySQL Voting: Vote UP / Vote DOWN

Over the weekend I spent a lot of time improving my new Shard-Query tool (code.google.com/p/shard-query) and the improvements can equate to big performance gains on partitioned data sets versus executing the query directly on MySQL.


I'll explain this graph below, but lower is better (response time) and Shard-Query is the red line.

MySQL understands that queries which access data in only certain partitions don't have to read the rest of the table. This partition elimination works well, but MySQL left a big optimization out of partitioning: getting data in parallel.

In fact, since partition elimination is the only major optimization provided by the partition options it isn't great for scaling access to large data sets when the entire data set must be accessed, but only when smaller parts of a the set are examined.

Since Shard-Query exploits parallelism with Gearman (http://www.gearman.org) I decided to extend the Shard-Query "optimizer" to support running queries with IN lists in parallel. This makes a query scale much further than it would if there was no parallelism at work.

Consider the table following partitioned fact table:

CREATE TABLE `fact` (
  `id` bigint(20) unsigned DEFAULT NULL,
  `a_id` bigint(20) unsigned DEFAULT NULL,
  `b_id` int(11) NOT NULL,
  `c_id` int(11) NOT NULL,
  `i1` tinyint(4) DEFAULT NULL,
  `qty` smallint(6) DEFAULT NULL,
  `score` decimal(10,10) DEFAULT NULL,
  `price` decimal(7,3) DEFAULT NULL,
  `i2` int(11) DEFAULT NULL,
  `i3` int(11) DEFAULT NULL,
  `wide_row` char(54) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (i1) PARTITIONS 100 */

The table is partitioned into 100 partitions, and there are 100 distinct values for i1. This means that all the values for a particular i1 are housed in a single partition.

Consider the following query:

select price*qty from fact where i1 in (1,2,3);

This query is semantically equivalent to:

select price*qty from fact where i1 = 1
UNION ALL
select price*qty from fact where i1 = 2
UNION ALL
select price*qty from fact where i1 = 3

Unfortunately, MySQL does not have any intra-query parallelism, so rewriting the query that way is not an effective scaling strategy. However, if you execute all three queries at the same time, and use a temporary table as the UNION ALL, you can actually get parallelism. This is what Shard-Query does. It can take each IN list item and assign it to a worker, and stuff the results back together at the end.

The second thing that can be done to improve performance at the partition level (or the shard level) is to push down aggregation of distributable aggregate functions to the worker. If you've read my blog before you might know that the distributable aggregate functions are SUM and COUNT.

Consider a query very much like the previous query:

select shard_col, sum(price * qty) from t1 where shard_col in (1,2,3) group by shard_col;

This query features aggregation with distributable functions. This query is semantically equivalent to the following:

This query is semantically equivalent to:

SELCT shard_col, SUM(`sum(price*qty)`) as `sum(price*qty)` 
from ( select shard_col, sum(price*qty) from t1 where shard_col = 1 group by shard_col
       UNION ALL
       select shard_col, sum(price*qty) from t1 where shard_col = 1 group by shard_col
       UNION ALL
       select shard_col, sum(price*qty) from t1 where shard_col = 1 group by shard_col
) GROUP BY shard_col;

Shard-Query can push the aggregation down to the shards and it sends each query which is part of the "UNION ALL" operation in parallel.

I ran a benchmark based on the above table with 40M rows in it. That is 40K rows per shard.
The benchmark queries follow the pattern:

select i1,sum(qty*price) from SAB_SF1.fact where i1 in(1) group by i1;
select i1,sum(qty*price) from SAB_SF1.fact where i1 in(1,2) group by i1;
select i1,sum(qty*price) from SAB_SF1.fact where i1 in(1,2,3) group by i1;
select i1,sum(qty*price) from SAB_SF1.fact where i1 in(1,2,3,4) group by i1;
...

All the way up to 100 values in the IN list. The table contains 400K rows for each c1 value, and all those rows are stored in a single partition.

Here is that graph again. For this test I used an EC2 "c1.large" instance. That is, 8 cores with 8 GB of CPU. I used a 4GB data set, Percona Server 10.2 and a 1GB buffer pool size. Each partition is approximately 32MB in size.

Since the machine has eight cores, I started eight Gearman workers. The results are, I think, impressive. In the graph, "partitions scanned" is the number of values in the IN list.



This performs very well due to the pushdown of the aggregation. If a non-distributable aggregate function were to be used, then performance would probably be worse than MySQL because all 40M rows would be accessed and copied into a temporary table.
PlanetMySQL Voting: Vote UP / Vote DOWN

(and before you ask, yes “rotating blades” comes from “become a fan”)

I’m forming the ideas here first and then we can go and implement it. Feedback is much appreciated.

Two tables.

Table one looks like this:

CREATE TABLE fan_of (
user_id BIGINT,
item_id BIGINT,
PRIMARY KEY (user_id, item_id),
INDEX (item_id)
);

That is, two columns, both 64bit integers. The primary key covers both columns (a user cannot be a fan of something more than once) and can be used to look up all things the user is a fan of. There is also an index over item_id so that you can find out which users are a fan of an item.

The second table looks like this:

CREATE TABLE fan_count (
item_id BIGINT PRIMARY KEY,
fans BIGINT
);

Both tables start empty.

You will have 1000, 2000,4000 and 8000 concurrent clients attempting to run the queries. These concurrent clients must behave as if they could be coming from a web server. The spirit of the benchmark is to have 8000 threads (or processes) talk to the database server independent of each other.

The following set of queries will be run a total of 23,000,000 (twenty three million) times. The my_user_id below is an incrementing ID per connection allocated by partitioning 23,000,000 evenly between all the concurrent clients (e.g. for 1000 connections each connection gets 23,000 sequential ids)

You must run the following queries.

• How many fans are there of item 12345678 (e.g. SELECT fans FROM fan_count WHERE item_id=12345678)
• Is my_user_id already a fan of item 12345678 (e.g. SELECT user_id FROM fan_of WHERE user_id=my_user_id AND item_id=12345678)
• The next two queries MUST be in the same transaction:
  • my_user_id becomes a fan of item 12345678 (e.g. INSERT INTO fans (user_id,item_id) values (my_user_id, 12345678))
  • increment count of fans (e.g. UPDATE fan_count SET fans=fans+1 WHERE item_id=12345678)

For the first query you are allowed to use a caching layer (such as memcached) but the expiry time must be 5 seconds or less.

You do not have to use SQL. You must however obey the transaction boundary above. The insert and the update must be part of the same transaction.

Results should include: min, avg, max response time for each query as well as the total time to execute the benchmark.

Data must be durable to a machine being switched off and must still be available with that machine switched off. If committing to local disk, you must also replicate to another machine. If running asynchronous replication, the clock does not stop until all changes have been applied on the slave. If doing asynchronous replication, you must also record the replication delay throughout the entire test.

In the event of timeout or deadlock in doing the insert and update part, you must go back to the first query (how many fans) and retry. Having to retry does not count towards the 23,000,000 runs.

At the end of the benchmark, the query SELECT fans FROM fan_count WHERE item_id=12345678 should return 23,000,000.

Yes, this is a very evil benchmark. It seems to be a bit indicative about the kind of peak load that can be experienced by a bunch of Web 2.0 sites that have a “like” or “become a fan” style buttons. I fully expect the following:

• Pretty much all systems will nosedive in performance after 1000 concurrent clients
• Transaction rollbacks due to deadlock detection or lock wait timeouts will be a lot.
• Many existing systems and setups not complete it in reasonable time.
• A solution using Scale Stack to be an early winner (backed by MySQL or Drizzle)
• Somebody influenced by Domas turning InnoDB deadlock detection off very quickly.
• Somebody to call this benchmark “stupid” (that person will have a system that fails dismally at this benchmark)
• Somebody who actually has any knowledge of modern large scale web apps to suggest improvements
• Nobody even attempting to benchmark the Oracle database
• Somebody submitting results with MySQL to not wait until the replication stream has finished applying.
• Some NoSQL systems to suck considerably more than their SQL counterparts.

PlanetMySQL Voting: Vote UP / Vote DOWN

You have to love all the debating going on over NOSQL -vs- SQL don’t you? With my UC session on choosing the right data storage tools ( does this sound better then SQL-vs-NoSQL?) I have been trying to stay current with the mood of the community so i can make my talk more relevant. Today I was catching up on reading a few blogs posts and I thought I would pass along these two: Pro SQL and Pro NoSQL … these represent the two very different views on this subject. (Note I think there are misleading facts and figures in these that should be flushed out more, but they are a good sample of what I am talking about). Sure lots of people have posted on this and even talked on it ( I am sure you have all seen Brian’s NOSQL -vs- MySQL presentation from open sql camp last year). You see there is a huge angery bitter flame war over who is right and who is wrong. People have very strong opinions on whether SQL or NOSQL is the anti-christ. We should organize a debate at some time. So who is right? My opinion is no one is.

The fact of is if a solution meets your needs and it works it is not wrong (it may have flaws or risks to different degrees). In the case of an RDBMS -vs- NOSQL, for some applications one is better then others. The issue I think we all run into is not really the merit of NOSQL -vs- a traditional RDBMS its the willingness to accept alternative views. Too many shops out in the world are all about the new hotness and not about what’s best for their application or organization. While other people would rather die then allow there database to be taken away from them. For some apps, durability is not a big deal for others it is. Everyone has different requirements. Just because Digg or Twitter or Rackspace is doing NOSQL and it works for them does not mean you have to use it, or that it will even work for you. In fact, if you leap without thinking you may in fact hurt yourself more then solve your problems. Every situation is unique and before you jump head first into one solution or another take a breath and analyse the situation. Ask questions like : Why are we thinking about NOSQL? Is just because of HA ( hey RDBMS’s can handle that! ), is it to replace sharding? Is it to do something else? … Ask yourself about the work you need to do: do you need to do complex joins? How much data will your really have? What sort of workload do you have? Really define your goal, then research and test solutions. I am sure that the big names using Cassandra or Hbase did not read a blog post somewhere and start converting everything that day, and you should not either.

Also Be careful of all the analysis, all the opinions, benchmarks, etc you see on the web on the topic. These are specific to a certain workload or user. Take Joe’s post (pro nosql from above), he says “Anyone out there running an EC2 large instance with a RDBMS on it that’s doing 1,800 reads/second? I’ve got a Cassandra node that was getting hammered with a load of 6 serving that much traffic without falling over..” taken out of context I could say, well hell my laptop this morning got 1200 reads/second on Cassandra and 4,000 reads/second with innodb. Does that mean MySQL is 4x better then cassandra? Well in a certain workload, under certain conditions sure… but I can write another benchmark that shows the opposite. By the way yes I have gotten well more then 1800 reads/second on an ec2 large instance…. but the workload is probably so different it’s a worthless comparison.

Facts and figures can be used to sway opinions, especially when variables are unknown. Let me show you what I mean. One of my colleagues was getting 55K read/write operations per second on a new server the other day. Joe ( Joe I am not picking on you directly, really ) posted he gets 1800/s on a large ec2 server. That **could** mean that Cassandra would need 31 large ec2 instances to match the power of that one server. That’s a cost of ( $2978.40 per aws large instance) of $92,330 per year. It’s over 3x the cost of the particular server that achieved 55K ops. Who would want to pay 3x more for the same performance right? This Proves SQL is awesome and NOSQL Sucks right? The answer is NO. Again the workloads are probably so different one may lend itself better to SQL. What if Joe has 1TB of data and I only had 100G, well that changes the equation and we would have to adjust to account for that. In this case with 31 servers if I could process 31TB of data at that consistent speed, then it maybe worth it, depending on how long it takes a single RDBMS to deliver results over 31TB.

I guess I am trying to say, make a decision based on your own tests and your own workload. There is nothing wrong with you considering either option as they have their merits and their place in the world:) There certainly is nothing wrong with listening to all of the banter about our experiences and our opinions. But even if really smart people tell you all kinds of reasons why NOSQL is better then a RDBMS, or other Equally Smart people tell you why an RDMS is better then a NOSQL Solution, evaluate for yourself and make an informed decision. A lot of these smart people are looking at the problem from there own unique experience. If someone had a bad experience with MySQL and did not have a good DBA, they may view MYSQL in a very negative light. Similarly if you have optimized, developed, and improved MySQL over the years you may view NOSQL solutions as foreign and filled with risk. Also remember sometimes really smart people sometimes do really dumb things ( I could talk about all the really smart people I know, and the rather non-common sense approaches they have tried because they are so close to a problem).

PlanetMySQL Voting: Vote UP / Vote DOWN