Process

Generic benchmark tools can be very useful for testing performance on your system. These benchmark tools normally have a set of predefined workloads, but often they don't match your specific workload in useful ways.

One of the best ways to reproduce your workload is to have a good sense of the application that uses the database and how it manages requests to the database. If this is not an option, it is also possible to analyze traffic and to find the most common queries, and use those to define the most common workload.

You can analyze traffic in many ways, from tcpdump to general log, from binlog (only for DML statements) to slow query log.

Afterwards it is possible to analyze them with pt-query-digest (or the obsolete mk-query-digest) to find the most common and/or heavy queries.

In the system we analyze here, the workload was mainly write intensive and involved just 4 tables:

• tableA was receiving single-row INSERT statements;
• for each insert on tableA , on average 200 INSERTs were performed in the other 3 tables, distributed as follows: 100 on tableB, 95 on tableC, 5 on tableD (to be more specific , for each INSERT on tableB there is an INSERT either on tableC or tableD).

The system also receives SELECT statements, but in a very small number and very simple primary key lookup.

To simulate the workload, we generated a simple perl script that spawns a certain number of threads that perform the DML statements, and other threads that perform the SELECT statements.

At regular intervals, the script prints statistics and progress.

The benchmark test was executed in a setup with 2 hosts: one host where the client was running, and another host where the servers were running.

The RDBMS tested were: MariaDB 5.2.3 with TokuDB 5.2.7 and InnoDB, and Percona 5.5.20.

Additionally, Percona 5.5.20 was tested as multiple instances running on the same hosts.

The goal of the first benchmark test was to compare TokuDB against InnoDB for this specific workload.

We executed MariaDB with TokuDB with the following (simple) config file:

[mysqld] 
user=mysql 
table_open_cache=1024 
max_connections=128 
query_cache_size=0 
innodb_file_per_table 
datadir=/localfio/datadir
log_bin 
innodb_flush_log_at_trx_commit=1 
innodb_buffer_pool_size=256M 
innodb_log_buffer_size=8M 
innodb_log_file_size=1024M 
basedir=/usr/local/tokudb 
 

We found the performance of InnoDB significantly better compared than TokuDB in this instance, though this test - where the dataset fits almost entirely in memory - does not show the real power of TokuDB, which excels at insertion rate at scale. Because these tables have very few indexes, TokuDB and Fractal tree indexes weren't very efficient. Furthermore, the benchmarks were running on FusionIO, which meant that performance on InnoDB didn't degrade much as on spinning disks. We excluded TokuDB out from the next benchmark tests because they are all cases which are not well-suited for TokuDB’s strengths.

We temporarily abandoned MariaDB, and tested Percona 5.5.20 with the following config file:

[mysqld] 
user=mysql 
table_open_cache=256 
max_connections=128 
query_cache_size=0 
innodb_file_per_table 
log_bin 
innodb_flush_log_at_trx_commit=1 
innodb_buffer_pool_size=2G
innodb_log_buffer_size=8M 
innodb_log_file_size=1024M 
basedir=/usr/local/mysql 
port=3306
datadir=/localfio/MULTI/db00 
socket=/localfio/MULTI/db00/mysql.sock 


We tried various innodb_flush_method attempts, and the graphs show that O_DIRECT performs slightly better than the default fsync(), even if the benchmark shows a weird bootstrap. We also tried ALL_O_DIRECT, which performed badly.

Additionally, we tried innodb_log_block_size=4096 instead of the default 512, but nothing changed: insert rate wasn't affected.

One of the goals of this benchmark was to test if running multiple mysqld instances on the same host performs better than a single mysqld instance.

On this specific hardware, the answer seems to be yes. Configuring 8 mysqld instances with the same config file listed below (but different paths and ports), throughput is significantly higher. Note that innodb_buffer_pool_size was set to 256M to try to stress the IO subsystem.

[mysqld] 
user=mysql 
table_open_cache=256 
max_connections=128 
query_cache_size=0 
innodb_file_per_table 
log_bin 
innodb_flush_log_at_trx_commit=1 
innodb_buffer_pool_size=256M
innodb_log_buffer_size=8M 
innodb_log_file_size=1024M 
basedir=/usr/local/mysql 
port=3306
datadir=/localfio/MULTI/db00 
socket=/localfio/MULTI/db00/mysql.sock 
 

All the above tests were executed using 36 client connections for writes and 36 client connections for reads.

We then ran a new cycle of tests, but instead of using 36 x 2 connections, we used 80 x 2 (80 for writes and 80 for reads).

With 80 connections, throughput was higher than with 36 connections, but at nearly regular intervals we found performance dropping. This seems independent from the size of the buffer pool.

It is interesting to note that with only one mysqld instance, FusionIO was performing at 4.7k – 4.8k IOPS, while with 8 mysqld instances FusionIO was performing at 27k – 29k IOPS. As expected, with a small buffer pool performance tends to slowly degrade when the data doesn't fit in memory.

We tried various values of innodb_write_io_threads, but this didn't make any difference, since the Redo Log was the most written and not the tablespaces.

To better analyze the throughput, we reduced the sample time to 10 seconds and reran the test:

It is clear that throughput drops from time to time, and for a nearly constant amount of time. While the test was running, we tried to monitor the mysqld instances, but there was no clear indication of why they were stalling. The Redo Log wasn't anywhere close to full and InnoDB wasn't performing aggressive flushing. The amount of data read from disk was pretty low but the amount of data written was spiking. Yet, the writes weren't coming from InnoDB.

The reason for the stalls became apparent when we analyzed the content of /proc/meminfo: the Linux Virtual Memory (VM) subsystem was performing dirty pages flushing!

We changed the dirty_background_ratio from 10 (the default) to 1 , and reran the test.

sysctl -w vm.dirty_background_ratio=1

Throughput is now way more stable, although performance has dropped by 2.8%. It is interesting to note that throughput drops at nearly the same time no matter the value of dirty_background_ratio.

A quick analysis of MySQL source code shows that binlog are synced to disk when closed, therefore the drops in throughput may be caused by the flush of binary logs.

We then raised vm.dirty_background_ratio up to 10 (the default value) and lowered max_binlog_size from 1G to 64M.

Throughput doesn't drop drastically as in the two previous tests, but goes up and down at more regular intervals.

At the end of this test, performance with max_binlog_size=64M is ~4% lower than the initial test with max_binlog_size=1G (in both cases, vm.dirty_background_ratio=10).

The last setup of 8 instances with a 256M buffer pool each and max_binlog_size=64M was then compared with a new setup:  4 instances with a 512M buffer pool each (2GB total in both cases) and max_binlog_size=64M:

An interesting outcome from this last test is that total throughput raised by around 4% (that was originally lost using binlogs of 64M) and that the total number of IOPS dropped to ~16k, leaving room for more IO in case of a different workload.

We then ran a new test using only 2 mysqld instances. It shows what was already easy to guess when running a similar test with only one mysqld instance: a lower number of mysqld instances can't fully utilize IO capacity and therefore has lower throughput.

Conclusions (most of them are as expected) for this specific workload and on this specific hardware:

O_DIRECT performs slightly better than the default fsync for innodb_flush_method .

A high number of clients provides more throughput than a smaller number of clients: not enough tests were performed to find the optimal number of clients.

Throughput reduces when data doesn't fit in the buffer pool.

A high number of mysqld instances running on the same server are able to better utilize the number of IOPS that FusionIO is able to provide (perhaps, it should be a very bad idea to run multiple mysqld instances on the same spinning disk or array)

The sync of binlog during binlog rotation are able to stall the system. Lowering dirty_background_ration or max_binlog_size is able to stabilize the throughput.

What do companies need from their database professional, MySQL or otherwise? How can we exceed those expectations as a remote team?  This is my first in a series of blog posts discussing exactly how we do so at PalominoDB - regardless if the technology is MySQL, MongoDB, Cassandra, Oracle or ottherwise.

Cultural:  

The majority of our clients are start-ups.  Some are small teams experiencing their first three year growth pains, while others are in the three to seven year period, have proven the effectiveness of their business model, yet retain a strong sense of start-up culture and personality.  When looking for staff, their focus is rightly on people who have passion, drive and personalities that fit with their unique corporate cultures.  How can a remote resource, much less one that is not dedicated to one company full-time, understand not just clients’ product and technology, but the people, the schedules and the drive that support clients' success?

Quite often clients want PalominoDB to have a single point of contact who gives us individual tasks and who functions as a filter between their organization and ours.  While we will work with whatever model is requested, this method builds a certain level of isolation that can limit our effectiveness in the bigger picture.  Being in operations requires a certain push and pull with engineering and product groups to meet business demands and ensure availability and performance. We also require knowledge of a company's business goals and project portfolio.  Otherwise, how can we react with urgency at the appropriate times?  How can we know which issues require escalation and which require push back? 

Once we understand company strategy and priorities, we can start to tailor the decisions we make to our clients’ needs. For example, if we know a particular system is crucial to the success of a client's key project, we are much more inclined to work until 2 am to complete the project or to meet a release deadline. If we know that two months of late nights and weekend work are crucial to helping a client with a customer launch, beat the competition and grow successful, my staff and I willingly devise a plan to support that customer. However, if we perceive that a customer’s demands for last minute changes or large amounts of off- hours work come from poor planning, poor communication or a poorly prioritized product plan, we are much more inclined to put our efforts into improving the underlying processes around change management and project planning. 

As operational professionals, we understand the importance of urgency and the product delivery speed that the modern start-up must work with.  Because of the breadth of our experience, we also know that if production teams had their way, all tasks would be P1s, all reports would be real time and there would never be any downtime (and all work would happen for free) and we act in accordance with this desire to the best of our ability.  When we know that the task we are working on is crucial to our clients’ ability to maintain their competitive edge, we are motivated to work the 12 hour days needed to get it done.  Alternatively, when we know a date is flexible, we can choose not to tax our operations team and evoke the risks associated with working too many hours and making crucial decisions under fatigue. As CEO and principal at PalominoDB, it is my job to work within my clients availability and take care of my staff  Work-life balance is not simply a concept to which I pay lip-service; I believe that a happy, rested and alert operations staff is essential to customer up-time and to keeping human mistakes to a minimum. 

Another question we often get asked is how do we as remote team members correctly align with business so that we can support them at their pace and intensity?  We’ve had the most success with regular knowledge- shares and participation in operational team meetings.  While taking part in our clients’ company-wide sessions has been unnecessary, we have found that attending operations and architecture team meetings where information can be shared down and around is an excellent start.  Getting onto operational team distribution lists is another method we use to learn about what is going on.  Obviously, every DBA on our team cannot do these things for every one of PalominoDB's clients, but the primary DBA assigned to a particular client can, and, as they filter out relevant details, they can share pertinent information with the rest of the team. 

Having that primary DBA serve as a client’s advocate is crucial, and a point I will continually discuss in my writings.  It is the primary DBA who asks questions when information is not forthcoming, who reviews the org charts and introduce themselves to engineers, project managers and QA/release folks.  The primary DBA gets contact info from all of these folk, documents it in CRM, and plugs it into GTalk, Skype or whatever medium is appropriate.  The primary DBA will hang out in a clients’ IRC and campfire rooms and soak up everything they see.  The primary DBA even reads powerpoints (yes really)! Finally, and most importantly, the primary DBA makes on-site visits.  PalominoDB’s operations team makes it a point to try and come on-site at least once every two months.  Some of that time is spent in meetings and some of that time is spent simply dining or hanging out.  Regardless, these on-site visits allow us all to connect, to put names to faces, and to get to know each other. It helps to ensure that our clients understand that PalominoDB is not a faceless company full of replaceable DBAs.  We are a company made up of individuals with skills, quirks, personalities (usually BIG ONES), and we know our clients are the same. 

Does this take time? Yes. However, I ask our clients to think about the savings in cost they accrue by using us instead of maintaining a full-time staff.  The extra time spent on meetings, emails and IRC conversations does not add much in overall cost, yet it is invaluable when building relationships.  Constant contact replaces the water-cooler meetings and impromptu conversations at lunch. That small investment of time in camaraderie and in team-building pays-off in more ways than you can imagine.