Tuesday Apr 21, 2009

MySQL 5.4 on 2 Socket Nehalem system (Sun Fire X4270)

Now that MySQL 5.4 (internally code named performance version or summit) is officially released, I can tell you that I used MySQL 5.4 alpha for my Nehalem scaling studies in my earlier blog - MySQL Scalability on Nehalem systems (Sun Fire X4270). I am waiting to get hold of a 4 socket Nehalem system to see we scale; but that will have to wait for the MySQL conference to get over.

Allan managed to get slightly higher Sysbench Read-Only numbers than mine using the latest MySQL 5.4. Interestingly Solaris does better than Linux. Probably a bug since many of the optimizations in MySQL 5.4 are OS independent. But then a lot can happen in 12 months :-)

Thursday Aug 21, 2008

Unlocking MySQL : Whats hot and what's not

One of the approaches we are using to look at MySQL scalability is to identify and if possible eliminate hot locks inside MySQL. For locks that cannot be eliminated, we are looking at ways to reduce the mutex hold times, or replace the critical sections with lock-free algorithms. It won't happen overnight, but it needs to be done if we are to make MySQL more scalable.

Along the way we're planning to blog about how we identified and eliminated performance bottlenecks and provide you with all the gory details about the process and technique. This will hopefully entice you all to contribute to fixing performance issues :-) We can reach our goal of a highly scalable MySQL much faster!. If you any other ideas about how we can scale the MySQL scalability effort (pun intended), or you would like to bust some locks, please feel free to let us know.

Fixing locks is sometimes very tricky as sometimes eliminating a really hot lock can only give a modest improvement in performance, or sometimes, no improvements at all. For example, sometimes fixing a hot lock might only move the bottleneck to the next lock. We have to address all the scalability issues before we actually see a dramatic improvement. A recent example illustrates this point.

LOCK_plugin: Not so hot after all

If you have been following MySQL scalability issues, you must be well aware of the LOCK_plugin lock. LOCK_plugin is used to protect the list of plugins used by MySQL. These plugins can be storage engine plugins, udf plugins, full text parser plugins, etc. LOCK_plugin got extremely hot for several benchmarks. Analysis of the contention showed that LOCK_plugin was being acquired and released at the end of every query execute as part of sending results to the clients. A closer look at the usage reveals that this lock is held while providing a hint to Innodb to release any shared locks and release concurrency tickets. The technique used by the MySQL server was to iterate over the plugin list and call ha_release_temporary_latches() for all storage engine plugins. The method used to do this (plugin_for_each_with_mask() grabs LOCK_plugin while iterating over the plugins list. For more details, checkout Bug#34409 LOCK_plugin contention via ha_release_temporary_latches/plugin_foreach

Sergei Golubchik recently fixed it in the most elegant way possible -- do not acquire a lock over the plugins list while releasing temporary latches. Looking at Sergei's fix and searching through the source, we see ha_release_temporary_latches() is called by MySQL server at multiple places without holding the LOCK_plugin lock! We are unnecessarily holding LOCK_plugin! Since holding LOCK_plugin is the side effect of iterating the plugins list, do not use the plugins list to iterate over storage engines; just iterate over the storage engines that are used in this transaction. What I really liked about the fix is that Sergei identified that there is no need for a lock, and got rid of it, rather than trying to break up the lock, or other things!. Kudos to Sergei for fixing this in such an efficient manner. If you are using 5.1.28 or 6.0.7-alpha, you already have this fix.

With this bugfix, we see around a 5% improvement in sysbench read-only tests. The reason we see only a 5% improvement and not more is that eliminating this lock made other locks more contented, and they are now the bottleneck. We are one step closer to a more scalable MySQL. Back to lock hunting for now.

Wednesday Jul 23, 2008

Peeling the MySQL Scalability Onion

In this blog I will talk about how we (the Sun/MySQL Performance Team) eliminated the need for a lock to get better scalability with MySQL 5.1.24+.

While comparing sysbench runs using two different versions of MySQL 5.1, I noticed a big difference in the system utilization. One version had much more idle time than the other. This difference was much more apparent with high thread counts on machines with lots of CPU. A look at the system calls showed a large number of lwp_park system calls. That is, threads were being put to sleep.

bash # $ dtrace -qn 'syscall:::entry{@c[probefunc]=count()}' -n tick-5s'{trunc(@c, 10);exit(0)}'

  p_online                                                       1535
  ioctl                                                          2255
  fcntl                                                          6134
  priocntlsys                                                    6462
  write                                                          6492
  read                                                          12775
  yield                                                         19065
  gtime                                                        313927
  pollsys                                                      321074
  lwp_park                                                     951749
Investigating why threads are being parked showed a very interesting stacktrace
              mysqld`int ha_innobase::info(unsigned)+0x164
              mysqld`bool make_join_statistics(JOIN\*,TABLE.. +0x180
              mysqld`int JOIN::optimize()+0x670
              mysqld`bool mysql_select(THD\*,Item\*\*\*,TABLE_.. +0x224
              mysqld`bool handle_select(THD\*,st_lex\*,selec.. +0xc4
              mysqld`bool execute_sqlcom_select(THD\*,TABLE.. +0x1c8
              mysqld`int mysql_execute_command(THD\*)+0x380
              mysqld`bool Prepared_statement::execute(Stri.. +0x29c
              mysqld`bool Prepared_statement::execute_loop.. +0xbc
Looking at make_join_statistics() we see that it calls ha_innobase::info() with HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK. A quick search for the definition of HA_STATUS_NO_LOCK shows
  assuming the table keeps shared actual copy of the 'info' and
  local, possibly outdated copy, the following flag means that
  it should not try to get the actual data (locking the shared structure)
  slightly outdated version will suffice
#define HA_STATUS_NO_LOCK        2

So the mysql server is requesting ha_innobase::info() to not hold a lock, and it is being ignored by ha_innobase::info()!.

I compared against MySQL 5.0 and saw that this particular lock was not held when ha_innobase::info() was called. Searching through the commit logs I found that this was introduced by Bug#32440.

Quickly hacking the code to revert to the old behaviour gave a big boost in performance. Hence I filed Bug #38185 ha_innobase::info can hold locks even when called with HA_STATUS_NO_LOCK. Luckily its a very small change and a fix is already in progress.

Moral of the story? A simple contended lock can bring down your performance by quite a lot.

Tuesday May 06, 2008

uperf - A network benchmark tool

Heard of filebench? Want something similar for networking? Look no further! Today we opensourced uperf, a tool to benchmark networking performance. uperf, just like its cousin filebench,1 is a framework that takes a description of a workload/application (called a profile), and generates load to match the profile. uperf is quite heavily used by the performance groups at Sun to study networking performance.

How does uperf help customers?

Often we hear customers complain about not getting good results with their application even though the results with micro-benchmarks were quite good. This is mainly because well known micro-benchmarks do very specific things like measuring bulk throughput or latency and do not really model the customer's application. Using micro-benchmarks does help standardize performance results, but the customer cannot really draw assumptions, based on these standard benchmarks, about how their application will perform. The safest alternative is to run their application to determine the performance. This maybe very time consuming and may require a lot of setup. uperf allows the customer to specify a description(profile) of what their application does and stress the system using this description. Using a profile has some additional benefits like you could modify the profile slightly to determine the effects of using a different protocol, etc..

How does uperf help developers?

Customers rarely want to share their application or it is just too time consuming to get their application and measure their performance in our labs, We are often left with a description of what the application is doing. uperf can take [a modified version of] this description and run whatever the real application does. In addition to running the customer's application, uperf can provide you with additional features like

  • Use threads instead of processes or vice versa
  • Use CPU performance counters to measure certain operations
  • Ability to change the protocol used and/or operation mix to predict performance metrics for a modified application
  • and many other features (uperf.org has the whole list)

So please feel free to check out uperf and contribute code, ideas, suggestions etc..

Wednesday Apr 02, 2008

Finding optimization opportunities in MySQL by looking at callstacks

Using callstacks to look at code is very useful. If you are not familiar with callstacks, I suggest you read my earlier blog about it. I was trying to understand the mysql code path using sysbench as the test and found something interesting. An image of the callstack is shown below. The SVG version, with much more information, is also available . The width of the block is proportional to the time it took for the function, and the height is the level (or depth) of the stack.

Mysql uses mysql_execute_command() to execute queries. Looking at the callstack you can see very clearly that mysql_execute_command() calls open_and_lock_tables which then tries to open tables via open_table(). The code path gets interesting here. As you can see in the image above, (and in the svg callstack), there is tight for loop. Looking closely (moving your mouse over the area in the svg file), you will see that the code is basically traversing a hash list! Not the best thing to do with a hashtable. Looking at the code, it is originating at line number 2781 in sql/sql_base.cc. Can it be improved? Most probably. Will it help if we improve it? Definitely for some cases, maybe not for others.

I found a few more interesting places in the code, but I will save them for my future blogs :-)

Monday Feb 25, 2008

Maximizing Sysbench OLTP performance for MySQL

Maximizing Sysbench OLTP performance for MySQL

Sysbench is a popular open source benchmark used to measure performance of various parts of the operating systems and (one) application (database). Since we are talking about MySQL, I will concentrate on the database part of the test.

The oltp test in the sysbench benchmark creates a single table and runs a set of queries against it. Each row in the table is around 250 bytes and by default it creates 1000 rows. For our experiment we used 10 million rows. Allan has blogged about the details regarding the experiments; I will present an alternate view to those experiments.

The trick to getting good numbers with Sysbench and MySQL is very simple

  1. Maximize CPU utilization
  2. Reduce delays due to IO

Maximize CPU Utilization

It is very important to utilize all the resources in the system to get maximum performance. If you have a multi-core machine (many processors already have multiple cores), this means that you will have to execute several threads in parallel to to fully utilize all the cores in the system. For Sysbench, this is achieved by using the --num-threads argument. We got the best performance when the number of threads is usually close or equal to the number of cores in the system. You also need to increase the innodb threads concurrently executing inside innodb. The meaning of innodb_thread_concurrency has changed since MySQL 5.0.19, so be sure to set it to the right value. We found the best number to be 0 (unlimited threads).

Reduce delays due to IO

One way to eliminate IO delays is by not doing any IO i.e cache everything. Each row in the Sysbench test table is around 250 bytes. For a 10 million row table, it is about 2.5GB of data. Most systems come with more memory than that, and hence you should be able to cache the table in memory.

MySQL uses the innodb_buffer_size variable to determines the size of the cache it uses. If you have sufficient memory (more than 2.5GB in our case), you could cache the whole table. An alternative strategy (especially if you are using 32bit MySQL) is to choose a smaller value for the innodb_buffer_cache and let the table be cached in the filesystem buffer cache; but I suspect it is more efficient to cache the data at the innodb level. This should eliminate or considerably reduce all the reads.

To eliminate writes, you can either choose to do a read-only test, or use a cache-enabled disks for the writes. The cache can either be NVRAM, or the write cache on the disk. Note: Using cache that is not battery backed is very risky. Do it at your own risk.

If you are still seeing delays due to IO(which in turn translates to idle time on the system), you can try adding more threads to soak up the available CPU. You have to be very careful to find the right balance.

To recap, We got the best numbers using

  1. Use multiple sysbench threads via the --num-threads parameter
  2. Set innodb_thread_concurrency = 0 in my.cnf
  3. Set innodb_buffer_size equal to or greater than the table size

Thats it! Here is the my.cnf that gives me the best numbers.


innodb_data_home_dir = /mysqldata
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /mysqldata
innodb_buffer_pool_size = 4096M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 400M
innodb_log_buffer_size = 64M
innodb_thread_concurrency = 0

Friday Jan 18, 2008

Averaging performance data

When you are optimizing benchmarks, the typical process involves running the same benchmark N times, and picking an arbitrary run of the benchmark (called a run) from these N runs to get the representative run. Another option is to average these N runs (creating a new run N') and pick that one as the representative run. In fenxi, we have discussed automatically averaging a bunch of runs. Performance data can be of two types
  • Numerical Data (Throughput, Response time, etc)
  • Textual Data (OS Patch level, syslog messages, etc.)
Averaging numerical data is very easy. Averaging textual data is not possible, or desired. However, since we are creating a new run N', we need to select textual data to be part of this new run. Which run do we pick it from? We are trying to solve this via the Fenxi project. If you have any thoughts or suggestions regarding this, please feel free to contact us.

Monday Jan 07, 2008

Fenxi - Performance analysis made easy

We just opensourced a nice performance analysis tool called Fenxi. Fenxi is a pluggable Java-based post-processing, performance analysis tool that parses and loads the data from a variety of tools into a database, and then allows you to query and compare different sets of performance data. Fenxi can also be used to graph data from performance tools. Fenxi (mandarin for analyze) is the successor to the Sun-internal tool called Xanadu. It is integrated with the Faban Benchmark harness.

If you have ever worked with performance data, you will pretty soon realize that
Performance Data can get huge.
Consider a benchmark running on a 64 core system with 100's of disks attached, with multiple network interfaces for 30 minutes. If you collect mpstat at 10 second intervals for the whole run, you end with more than 11,000 lines of data! (That is 400 CNTRL-F's if you are using VI in a regular sized termial). If you collect data from more tools like vmstat, iostat, trapstat, busstat, cpustat, etc you will end up with much more! Going through each of them line by line is not a scalable approach.
Performance Data is interrelated.
The tool outputs are just different views of the system behavior. We want to look at the system as a whole, rather than at its individual views. If your incoming network packets peaks, your interrupts in your mpstat most likely peaks. We may want to see if throughput was impacted as a result of a burst of writes to our disks, etc.
Some performance data makes sense visually.
For large data, a visual view gives a quick summary of the data. As Tim Cook states it, "the human brain is a powerful pattern-recognition machine - graphs allow you to spot things you would never see in numbers (like waves of CPU migrations moving across different cores)". Look at the bottom of the blog for more details
Performance Data should be queryable
We want to be able to query or ask questions to the performance data.  For ex, you might want to know "What are my hot disks?". Traditionally, people have answered such questions  by writing custom scripts using sed/awk/perl. This can get tedious very fast. We need a better way of asking questions. In Fenxi, we store the data in the database, and questions are formulated in SQL.
Performance Data should be comparable, averageable, etc.
Since I work in the performance group at Sun, we run a lot of benchmarks. Since the goal of [most] benchmarks is to maximize the performance of a system, we are always constantly trying out new changes to the system. Typically, we change a parameter and repeat the benchmark and see if it has improved performance.
Performance Data should be sharable.
We rarely work in isolation. We should be able to share data with our peers and collaborate on finding performance fixes.

Fenxi tries to solve all of the above problems.

Sample Graph

Sample Text

Fenxi text view

You can see a sample database run processed by Fenxi. I urge you to check it out!



« July 2016