MySQL Performance Blog

This is next post in series
Analyzing air traffic performance with InfoBright and MonetDB
Air traffic queries in LucidDB
Air traffic queries in InfiniDB: early alpha

Let me explain the reason of choosing these engines. After initial three posts I am often asked “What is baseline ? Can we compare results with standard MySQL engines ?”. So there come MyISAM to consider it as base point to see how column-oriented-analytic engines are better here.

[read more...]

As Calpont announced availability of InfiniDB I surely couldn’t miss a chance to compare it with previously tested databases in the same environment.
See my previous posts on this topic:
Analyzing air traffic performance with InfoBright and MonetDB
Air traffic queries in LucidDB

I could not run all queries against InfiniDB and I met some hiccups during my experiment, so it was less plain experience than with other databases.

[read more...]

After my first post Analyzing air traffic performance with InfoBright and MonetDB where I was not able to finish task with LucidDB, John Sichi contacted me with help to setup. You can see instruction how to load data on LucidDB Wiki page

You can find the description of benchmark in original post, there I will show number I have for LucidDB vs previous systems.

[read more...]

This is part 3 of our series.  In part 1 we talked about boosting performance with memcached on top of MySQL, in Part 2 we talked about running 100% outside the data with memcached, and now in Part 3 we are going to look at a possible solution to free you from the database.  The solution I am going to discuss here is Tokyo Cabinet and Tyrant.

I am not going to give you a primer  or Tutorial on Tyrant and Cabinet, there are plenty of these out there already.  Instead I want to see what sort of performance we can see compared to MySQL and Memcached, and later on other NoSQL solutions.  Tokyo actually allows you to use several types of databases that are supported, there are hash databases which are very similar to memcached, a table database which is similar to your classic database tables where you can add a where clause and search individual columns, and a ton more “database options”  beyond just those two.  Again my goal is to not make this a Tokyo Tyrant tutorial but rather show one potential role it can play.

More details can be read about here:
http://1978th.net/tokyotyrant/
http://1978th.net/tokyocabinet/

So if we can get performance similar to memcached with Tokoyo Tyrant when using disk based hash tables it would be a compelling replacement for our application here.  It should provide the interface and the same access we saw in memcached but with disk persistence. So let’s look at the numbers:

Tyrant’s disk based hash was almost 2x faster then combining memcached and MySQL, and about 20% slower then the all memory memcached approach.  So for this particular application I would have been much better off not storing my data in MySQL and instead looking outside the database for an answer.  Now sure there are other reasons you may want to keep data in the database… but I am trying to get you to think about your application and if those reasons are really valid.  Helping clients pick the right solution is one of the things we do here at Percona.  If an application requires a database great, but if there is a better solution we want to suggest it.  It’s our goal to make your application perform optimally.

Finally, one concern you have to have is the scalability of your storage solution.  As load, number of threads, and data size increases how does performance differ or change?  One knock on Tokyo -vs- Memcached is Tokyo is not distributed by default.  Now that’s not to say we could not shard it based on a hash, or even build an api with the capability built in ( or use the memcached clients which works! )…  but native support is lacking.  It does support replication which could make some rather interesting architectures in the future.

So lets look at some scalability benchmarks, my server resources are rather limited but I thought I should try throwing more threads and work at the server until it hit its limit and fell over dead.  It’s interesting to see the number of transactions that occur with a given number of threads.  let’s look at some of these:

As expected the smaller buffer pool struggled ( why a smaller buffer pool?  This simulates a much larger data set.  A BP of 256M with 1GB of data, can give similar performance to 20GB of data and a 5GB BP ).  So with 256M BP and 4GB of memcached we were well off the numbers we hit with a 4GB BP+4Gb of memcached ( which is expected ).   Adding more threads even up to 128 threads increased overall throughput but my load average on the server hit 40 and my CPU was pegged.  At 128 threads I was pegging out my CPU across the board.  Also interesting is I started to hit bottlenecks in MySQL/Innodb when I had enough memory but I increased the threads from 64 to 128.  As time permits I should revisit this and look at increased datasets, and look for area’s where Tyrant may stumble a bit.

Bottom line given a specific application and data pattern sometimes a relational database is not the appropriate place for storing data.  A tool like Tokyo Tyrant may not be for everyone or every application, but neither is a relational database.  Before building your next application try and understand whether an RDBMS is really needed or not.

How did I do these tests:

The above number were run with 32 Threads, Tyrant was started with 8 threads and 128M of memory,  memached was started with 16 threads ( 1.4 memcached ), mysql was 5.1 XtraDB.  Each environment had 2 tables each with 2 million rows.  The data was identical. memcached and Tyrant stored a comma delimited string to represent the row.   Mysql was running with 256M allocated to the innodb buffer unless otherwise noted.

What’s next?  Well next I am going to try and continue this series by exploring and benchmarking other NOSQL options and comparing them to database based solutions.  I think showing the performance of a couple of different Tokyo database formats would also be interesting.  What other solutions are people interested in?  I know I have gotten a lot of requests for cassandra #’s, but what else?  Drop a comment and let me know!

Part 1 of our series set-up our “test”  application and looked at boosting performance of the application by buffer MySQL with memcached.  Our test application is simple and requires only 3 basic operations per transaction 2 reads and 1 write.  Using memcached combined with MySQL we ended up nearly getting a 10X performance boost from the application.  Now we are going to look at what we could achieve if we did not have to write to the database at all.  So let’s look at what happens if we push everything including writes into memcached.

Wow that’s shockingly fast isn’t it! I guess being completely in memory helps for this app.  What is very interesting is accessing 100% of the data in memcached gives very similar numbers to accessing 100% of the data in memory in the DB ( part 1 benchmarked a 4GB bp as being able to handle 7K TPS)… something is not 100% right here.  It stands to reason that memcached should be faster for this application then the DB.  Its just doing two gets via key and 1 set.  So why the similar numbers?

Well glad you asked.  It’s the API.  The api in this case was Cache::Memcached, by switching to using Cache::Memcached::Fast look what happens:

That is a nice jump in performance!

Using Memcached::Fast was kind of a mixed bag when looking at the benchmarks for mixing MySQL and Memcached in my tests:

In this case I think the Fast api was slower when working with MySQL with a 256m BP because the slower returns from memcached acted as a bottleneck to thin the demands on MySQL to write data, smoothing out the work load.  When we eliminate this bottleneck with the Fast api, MySQL gets hammered.  This type of thing happens a lot.  For example an application is CPU bound, so you add more processing power, but then you hit disks harder and  now your disk bound.

A couple of good things to remember here:  #1 resolving 1 bottleneck can open another bottleneck that is much worse.  #2  is to understand that not all API’s are created equal.  Additionally the configuration and setup that works well on one system may not work well on another.  Because of this people often leave lots of performance on the table.  Don’t just trust that your current API or config is optimal, test and make sure it fits your application.

So adding Memcached on top of MySQL for our test application can significantly boost performance. But you notice that if we were running 100% in memcached and could cut out MySQL we could get 2.5x more performance over a mixed solution and 100X over just stock MySQL.  As the number of writes against the database increase this gap will increase.  So let’s ditch the database!  But wait!  you need the DB for  persistence, right?

It depends.  A database may not be the best fit for every application.  There are several “NOSQL”  solutions out in the open source space that can give you some of the ease of a Memcached but with persistence most people use their database for.   Each application is different and understanding the application’s requirements is key to picking an appropriate solution.   I am going to look at several database alternatives over the next few months.  I need to start somewhere, so I decided to start with Tokyo Tyrant and Cabinet.    So stop in next time for part 3 of this series where we will focus on running the same tests against Tokyo Tyrant.

All to often people force themselves into using a database like MySQL with no thought into whether if its the best solution to there problem. Why?  Because their other applications use it, so why not the new application?  Over the past couple of months I have been doing a ton of work for clients who use their database like most people use memcached .  Lookup a row based on a key, update the data in the row, stuff the row back in the database.  Rinse and repeat.  Sure these setups vary sometimes, throwing in a “lookup” via username, or even the rare count.  But for the most part they are designed to be simple.

A classic example is a simple online game.  An online game may only require that an application retrieve a single record from the database.  The record may contain all the vital stats for the game, be updated and stuffed back into the database.  You would be surprised how many people use this type of system as I run into this type of application frequently.  Keeping it simple, ensures that application is generally mean and lean and performs well.  The issue is even this simple design can start to have issues as the data size increases and you blow through your available memory.  Is there a better architecture?  Is there a way to get more scalability out of your database?  Is the database even the best place for this data?

I decided to walk through setting up a very simple application that does what I have seen many clients do.  Using this application I can then compare using MySQL to using MySQL + Memcached, and then to other solutions like Tokyo Tyrant or Cassandra.   My Application does the following:

A.)  read a row from a database based on an integer based primary key
B.)  Update data from that row and replace the stored contents on disk
C.)  Use the data from that row to lookup up a row in another table based on a text field ( called email address ).

Seems simple enough right?  My two tables each contain 5M rows of data.  let’s see what happens:

You can see a dramatic drop off in performance as my data falls out of memory, that’s not cool is it?  After all database sizes tend to always grow and very rarely shrink.  Which leads to a challenge faced by almost everyone how do you maintain your performance in the face of increasing data size?

Here is where people start to scratch their heads.  They naturally assume they need to scale more, we need more memory!   If performance sucks, we must need more.  So here comes the bigger boxes, the read-only slaves,  the complex sharding systems, the discussions on cluster, more memcached.  We need to cover up the databases inefficiencies to ensure that our application scales.

The problem is for some applications, we are fixing symptoms, not the problem itself.  No matter how much you want it to fit,  some things may not work (like the Godfather 3).    The issue is people assume that data storage has to be in the database.  “It’s data, it needs to go into the database.” is often the battle cry.   But hold on to your hat,  I  am going to shock you.  For some applications, putting your data in the database is silly.  Yes the guy who blogs on bigdbahead.com and is writing this on the mysqlperformanceblog is saying you may not want to use a database.  Heresy I know!  But for many of us we already accept storing data ( at least temporarily ) outside the DB.  Think memcached.

Almost everyone loves memcached, it’s simple, fast, and just works.  When your dataset exceeds your memory limitations or the database can simply not keep up any more this solution can really boost performance.  I know you’re thinking my simple key lookup should really benefit from memcached. So let’s try it!  I took the simple app I created that reads two rows, and update one of them to read from memcached if available, remove on update, and read from the db only when required.  I tested with a memcached size of 1GB, 2GB, and 4GB.  For these tests I left Innodb with a 256M buffer pool, or roughly with 9% of the total data in memory.

let’s look at the 1GB Setting:

What, a performance regression?  But we threw more memory at it!!   How can that be!

Memcached is not a cure all.  I have talked to many client’s who say “we will just throw memcached at it”.   Sometimes an app will scream other times it won’t… and yet others require lots and lots of memory allocated to memcached to be successful.    This application selects a random # between 1 and 2 Million and looks up the result via that key.  It then uses data from that random row to look up a second piece of information via email address.  Because the entire dataset  is about 4GB and only 1G is in memcached, I keep pushing data out of memcached to make room for new records I am reading from the database. Remember memcached needs repeatability to be helpful.   I am still getting a really solid # of hits in memcached, but the # of writes in MySQL coupled with the still large # of reads takes its toll.  Another place where I have seen this kill clients is in apps that do some sequential scanning and do not have enough memory for memcached.  For instance, if you have 1,000,000 rows of data, but enough memory to only store 500,000 rows… sequentially accessing this data will destroy the use of cache:

get record 1, miss, read from disk, cache record 1
….
get record 500,001, miss, read from disk, expunge record 1, cache record 500,001
….
get record 1, miss, read from disk, expunge record 500,001, cache record 1

you keep overwriting the cache before you can use it.  So here the complexity of adding memcached hurts us, because the cache is not actually buying us anything.

Now bumping this up to 2GB actually makes the TPS jump around a lot, sometimes hitting 400 or 500 TPS and other times hitting as high as 1800 TPS.  My guess is the movement is caused by the random #’s being generated and simply the luck of the draw.

Finally let’s look when we have 4GB of memory allocated to memcached (full dataset fits ):

Here you can see that our “transactions”  per second for this app increased almost 10Xby using memcached.  The TPS I get here vary from 1100 TPS to just under 2000TPS with the average around 1400TPS.   I think we would all be very happy if we could get a 10X performance boost from your application.

But wouldn’t it be great if we could get more?  I mean our reads are going pretty fast, but our writes leave a lot to be desired:

Over 17 MS to do an update.  Wouldn’t be great to just eliminate all the updates as well?  What sort of throughput would we get?   I will show you in part 2.  Part 2 of this post will talk about performance in a 100% pure memcached environment. Part 3 will focus on these same benchmarks in Tokyo tyrant.

For the my previous post, there was comment to suggest to test db_STRESS benchmark on XtraDB by Dimitri. And I tested and tuned for the benchmark. I will show you the tunings. It should be also tuning procedure for general heavy writing workloads.

At first, <tuning peak performance>. The next, <tuning purge operation> to stabilize performance  and to avoid decreasing performance.

<test condition>

Server:
PowerEdge R900, Four Quad Core E7320 Xeon, 2.13GHz, 32GB Memory, 16X2GB, 667MHz

db_STRESS:
32 sessions, RW=1, dbsize = 1000000, no thinktime

XtraDB: (mysql-5.1.39 + XtraDB-1.0.4-current)
innodb_io_capacity = 4000
innodb_support_xa = false
innodb_file_per_table = true
innodb_buffer_pool_size = 16G
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 128M
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 90
innodb_flush_method = O_DIRECT
(the followings are XtraDB specific general settings)
innodb_ibuf_active_contract = 1
innodb_adaptive_flushing = false
innodb_adaptive_checkpoint = estimate

<tuning peak performance>

At first, tuning the peak performance to use CPU and IO resource more effectively. To avoid mutex/lock contentions are good to use more CPU resource of many CPUs.

This graph shows the peak performance in tps of db_STRESS.

At current settings, “base” in the graph is the perfomance. We can confirm the mutex/lock contention roughly by the SEMAPHORES sction of SHOW INNODB STATUS output.

“xx-lock on RW-latch at 0×7f2ff40a3dc0 created in file dict/dict0dict.c line 1627″

It is index->lock, viewing the source file (and it may be HISTORY table). This is the lock for each index tree. We may be able to disperse the lock using by the partitioning of MySQL. Added the following clause to the HISTORY table definition.

“PARTITION BY HASH(REF_OBJECT) PARTITIONS 16″

Now the performance became to “+partitioned” in the graph. Looking the SEMAPHORES section again,

“has waited at handler/ha_innodb.cc line 7275 for 0.0000 seconds the semaphore:
X-lock on RW-latch at 0xd30320 created in file dict/dict0dict.c line 623″

may be the line which appears for the most times (it is dict_operation_lock). It may be partition specific lock contention. The current XtraDB has the variable to tune the contention.

innodb_stats_update_need_lock = 0 (default 1)

It skip the updating statistics which needs the lock. (it only affects for “Data_free:” value of TABLE STATS). And the performance became “+skip_stats” in the graph.  Then, the next contention at SEMAPHORES section is…

“Mutex at 0×1b3e3e78 created file trx/trx0rseg.c line 167″

may be remarkable (it is rseg->mutex). The mutex is for each rollback segments, so we can increase the rsegs to solve the contention problem. XtraDB can increase the rseg.

innodb_extra_rsegments = 64 (affects to initialization of InnoDB)

Recreated database files with the parameter. Then the performance became “+rsegs64″. At last, the next contention may be “Mutex at 0×28ce8e0 created file srv/srv0srv.c line 982″. It is kernel_mutex, currently we don’t have proper solution for that. The setting seems to be enough for now.

<tuning purge operation>

Next, looking the sequential result in more long term.

The next problem is “History list length” growing to huge size. The value is the number of entries in rollback segment. The entries are used for consistent reading of the older transactions. They can be removed when any transactions doesn’t refer the entry. This removing operation for the entries is called “purge” in InnoDB. The purge operation should be done enough on time, because the huge history list affects to performance.

Basically, the purging is done by master_thread (general background thread of InnoDB). The huge history list makes the purge operation slow, and it interferes  with the other tasks of the master_thread (e.g. flushing dirty blocks, treating insert buffer, etc…). Dimitri implemented a purge_thread to devote to the purging, and also XtraDB has similar purge_thread. Though it seems to make the throughput stabilize, it is not enough still for heavy update workloads. A single purge_thread on one CPU is not enough for updates from user threads on the all of other CPUs.

XtraDB can increase the purge_threads from the next release.

innodb_use_purge_thread = 4

seems to be enough for this workload on the server.

The first graph of followings is sequential throughput [tps] up to 3500 sec.

The next is tracking the “History list length” at the same time.

"Norm 1.0.4": Normal InnoDB Plugin 1.0.4 without XtraDB specific options
"xtra p_t 0": XtraDB 1.0.4-new (no purge_thread)
"xtra p_t 1": XtraDB 1.0.4-new (single purge_thread similar to Dimitri's)
"xtra p_t 4": XtraDB 1.0.4-new (4 purge threads)

The graphs show…

• The purge thread (> 0) helps to stabilize the throughput greatly.
• Increasing the purge threads can suppress the strong growing of the hitory list
• The adaptive checkpoint “estimate” needs the purge_thread… (than the adaptive_flushing does)

And the last 300secs’ average tps are…

"Norm 1.0.4": 5725.47
"xtra p_t 0": 4699.33
"xtra p_t 1": 7130.3
"xtra p_t 4": 9118    (about 60%up from Normal Plugin 1.0.4)

In the end, the faster and more stable performance of db_STRESS benchmark is obtained by these tunings of XtraDB.

—————————————–

(Added 2009.10.29)

<FAQ: Is XtraDB slower than Plugin?>

I’d like to say “no” to this question. We have been adding many tuning options to XtraDB. But they are effective not for all cases, sometimes the performance may get worse because of “not proper” or “too much value”. We should choose the options correctly. XtraDB is based on InnoDB Plugin and we can set XtraDB same to InnoDB Plugin at least. The following graphs are results of XtraDB and Plugin with same options and same database.

top-left (same condition to above graphs):
innodb_flush_log_at_trx_commit = 2
innodb_doublewrite = true

top-right:
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = true

bottom-left:
innodb_flush_log_at_trx_commit = 2
innodb_doublewrite = false

bottom-right:
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = false

It seems that XtraDB is not slower than Plugin here at least.

We can start tuning based on these performances using XtraDB specific options!

Why do you make XtraDB slower than Plugin?

Accidentally me and Baron played with InfoBright (see http://www.mysqlperformanceblog.com/2009/09/29/quick-comparison-of-myisam-infobright-and-monetdb/) this week. And following Baron’s example I also run the same load against MonetDB. Reading comments to Baron’s post I tied to load the same data to LucidDB, but I was not successful in this.

I tried to analyze a bigger dataset and I took public available data
http://www.transtats.bts.gov/DL_SelectFields.asp?Table_ID=236&DB_Short_Name=On-Time about USA domestic flights with information about flight length and delays.
[read more...]

It is pretty understood the tables which have long rows tend to be slower than tables with short rows. I was interested to check if the row length is the only thing what matters or if number of columns we have to work with also have an important role. I was interested in peak row processing speed so I looked at full table scan in case data fits in OS cache completely. I created 3 tables – First containing single tinyint column which is almost shortest type possible (CHAR(0) could be taking less space), table with 1 tinyint column and char(99) column and table with 100 tinyint columns. The former two tables have the same row length but have number of column different 50 times. Finally I have created 4th table which is also 100 columns but one of them is VARCHAR causes raw format to be dynamic.

[read more...]

As you may know, InnoDB has 2 limits for unflushed modified blocks in the buffer pool. The one is from physical size of the buffer pool. And the another one is oldness of the block which is from the capacity of transaction log files.

In the case of heavy updating workload, the modified ages of the many blocks are clustered. And to reduce the maximum of the modified ages InnoDB needs to flush many of the blocks in a short time, if these are not flushed at all. Then the flushing storm affect the performance seriously.

We suggested the “adaptive_checkpoint” option of constant flushing to avoid such a flushing storm. And finally, the newest InnoDB Plugin 1.0.4 has the new similar option “adaptive_flushing” as native.

Let’s check the adaptive flushing options at this post.
[read more...]