Showing posts with label MySQL benchmark. Show all posts
Showing posts with label MySQL benchmark. Show all posts

Mar 28, 2011

MySQL Partitioning. One step before Sharding.

Many of you may consider Sharding to meet your large scale database demands. However, in some cases it seems too early since you may not have the needed bandwidth in the current phase or you just consider to adapt a Sharding COTS solution like Scalebase and you need a solution for mid term.


I don't have time so what should I do?
The answer is simple: you may choose MySQL built-in mechanism named MySQL Partitioning. 
This mechanism partitions your table using one of 4 different rule types. It main solution is keeping all this process hidden from the applicative DBA and the software engineer. Try one of this methods:

  1. Range Partitioning: partition by column values. Most fit for known ranges like archive by date range.
  2. List Partitioning: similar to the above based on discrete values. Most fit for archive by years.
  3. Hash Partitioning: partition by hash function defined by user. Most fit when data ranges are that are unknown, but you know the spread of them. Should be used when you partition a table according to a foreign key. Please notice that in any case, the hashed key must be included in table primary key.
  4. Key Partitioning: similar to Hash, but this time the MySQL Server takes care of the function. Most fit that the data distribution is uniform. Partition key should include the primary key.
How do I declare that?
Using DDL. just add few more lines to your CREATE TABLE statement and you will receive the partitioning. For example to split by year use the following statement:

CREATE TABLE grades (
...    
year INT NOT NULL,
...
)
PARTITION BY LIST(year) (
    PARTITION p2009 VALUES IN (2009),
    PARTITION p2010 VALUES IN (2010),
    PARTITION p2011 VALUES IN (2011),
    PARTITION p2012 VALUES IN (2012)
);

What do I get?
Mayflower has presented very nice numbers with 200M records table partitioned to 400 parts. They reached 4000 INSERT/UPDATE and 6000 SELECT statements per second.

Bottom Line
If don't have a lot of time and you need an instant solution, go for Partitioning

Keep Performing,

Jan 26, 2009

How much can you get out of your MySQL

Hi,

As you probably understand, our team, as always, is taking these days MySQL to its limits. It is our pleasure to share the insights with you.

First lets make a small assumptions to the process: We were dealing with InnoDB configuration (which MySQL engine should I choose?)

MySQL Performance Benchmarks:
I would like to refer you to several case studies and benchmarks of MySQL:
1. In our tests while boosting an OLAP mechanism, we reached a number of 12 Group by queries/second on a 1 Million records.
2. 17 Transactions/second were reached on a basic machine (PIII, 256MB RAM on a 100K records table). However, the benchmark performer never revealed his exact benchmark.
3. A MySQL performance benchmark paper from 2005, reached 500 reads on a 10 Million records table on a 8 CPU machine (8 MySQL instances). Meaning that MySQL reached a 60 reads/seconds on a single instance MySQL in a well optimized benchmark.
4. Sun performed on a Sun Solaris based 4 AMD Opteron Dual Core Model 875 (8 MySQL instances) 16GB RAM reached 1800 (RW) and 2900 (RO) transactions/seconds or 220 (RW) and 350 (RO) transactions per second on a 1 Million records table.

Bottom Line:
As a rule of thumb we recommand to not use tables with more than few dozens million of records, and not to expect to more than few dozends reads per second per MySQL instance

Other useful issues for building your scalable software system:
1. MySQL supports triggers. However, we recommend to avoid this feature due to performance issues. Please, if you fill any need to use triggers, please implement it in the BLL level, rather then using triggers.
2. MySQL supports Identity using @@Identity. However, please notice that this one is not well working with triggers (noitce our hazard before).
3. MySQL supports XML, and both SQL Server "FOR XML" and "OPEN XML" can be implemented using various methods (critics). However, we do NOT recommand to use these methods either in MySQL and SQL Server. Usually the database is the bottleneck of any system; Therefore, you would like to avoid any unnecesary operation in the database.
4. INSERT DELAYED: very useful in cases, when you would like to make an insert to a table (e.g log tables and queue like tables) and wants to avoid the wait till the INSERT is processes. INSERT DELAYED are performed on the server open windows and not immidiatly.
5. Multiple inserts: multi valued INSERT performs best in MySQL
6. Transactions are supported in MySQL, use them when needed.
7. Try Catch are supported as well.
8. MySQL row sizes:
- Regular fields take up to 8KB.
- You can use VARBINARY, VARCHAR, BLOB and TEXT columns to get more.
- 1000 columns is the limit per table

Best,
Moshe. RockeTier. The Performance Experts.

ShareThis

Intense Debate Comments

Ratings and Recommendations