Big DBA Head!

Note This Article was actually written back in May after the UC at the request of Linux Magazine, through a series of events It went unpublished. Between then and now Jeremy ended up doing a great job covering most of the topics, so in the end it was unneeded. Now I had this completed article and thought, what should I do with it? In the I decided to publish them here. Also note I did update a few items.

As more companies move to MySQL and the demands for data increase, we push the bounds of the database further. The challenges large Web properties (who have pioneered many of the large MySQL deployments) faced when they stored 50GB of data and had 5,000 users were nothing like the challenges of storing 500GB of data supporting 100,000 users. Today, as we are seeing more and more 10+TB-sized datasets being used and accessed by millions of users, the same properties are again forced to think of new ways to maintain the performance, ease of use, and freedom that using MySQL has afforded them in the past. They have had to adapt and overcome these challenges to survive.

Solutions that work on one-sized environments present new challenges in others. Engineers are considering all their options. These options include moving data to non-relational solutions or even caching large chunks of their data in Memcached. Additionally, many talented engineers have also looked to database designs of the past for clues, resurrecting older database design methodologies like sharding to help keep things moving forward. Some are finding new uses for old technologies like replication by building complex master-master or massive read-write splitting setups to get the job done. So…what is the problem, and what is being done about it?

Most MySQL customers use either MyISAM or Innodb as their storage engine. They originated from those glorious days of yesteryear when we never thought we would need more then a 32-bit machine, an SMP machine meant two CPU’s, and 64MB of memory meant you had a powerhouse. The Innodb storage engine was written way back in the mid 90’s. It’s a beautiful piece of coding that has really stood up over time. However, this meant some optimizations were made based on how to get good performance out of a single CPU server with 128M of Ram and a database that was only a few GB in size. I am not saying these engines have remained unchanged; on the contrary, they have changed dramatically over the years. But while new releases have helped improve performance on larger boxes, there are many places where old code has really hindered performance.

Read the rest of this entry »

Since I have been a home recently, I put some time correcting bugs in WaffleGrid and adding new features. Thanks to gdb, I have been able to understand a silly bug that was affecting WaffleGrid with sysbench but, weird enough, not with dbt2. Everything is in the way connections are established. I will blog more about that soon.

Regarding the new features, it is now possible to choose which tables you want to push to memcached. For that purpose, two new parameters have been introduce:

innodb_memcached_table_filter_enable = 0 | 1   (default to 0)

to enable the filtering and

innodb_memcached_table_list = db1/table1,db2/table2

to list the tables. This feature is filtering based on the space id so, innodb_file_per_table has to be set. Right now, the association table <-> space_id is done only at startup so, the table has to exist. Also, since an alter table change the space_id… you need to restart MySQL to restore the filtering after an alter table. Eventually, it will be more dynamic. Here are some tests done with innodb_memcached_enable = 1, caching around the disk IO, sbtest is in the filter list while sbtest_non_waffle is not. I really like mode 1, it works with the normal memcached and set to memcached are handled by the IO write threads of InnoDB, so they happened in the background. Both tables have exactly the same structure and content (100k rows, sysbench oltp test).

mysql> select avg(length(pad)) from sbtest;select avg(length(pad)) from sbtest_non_waffle;
+------------------+
| avg(length(pad)) |
+------------------+
|          50.0000 |
+------------------+
1 row in set (0.31 sec)

+------------------+
| avg(length(pad)) |
+------------------+
|          50.0000 |
+------------------+
1 row in set (0.98 sec)

Not bad, one third of the time and no disk IO, the data was already in memcached. For the test, the buffer pool is at 8M (I tweaked 5.4 to lower the minimum). Data length and Index length for both table is approximately 24 MB. Then, just to prove my point about altering tables:

mysql> alter table sbtest engine=innodb;
Query OK, 100000 rows affected (8.50 sec)
Records: 100000  Duplicates: 0  Warnings: 0

mysql> select avg(length(pad)) from sbtest;select avg(length(pad)) from sbtest_non_waffle;
+------------------+
| avg(length(pad)) |
+------------------+
|          50.0000 |
+------------------+
1 row in set (0.92 sec)

+------------------+
| avg(length(pad)) |
+------------------+
|          50.0000 |
+------------------+
1 row in set (0.96 sec)

As you can see, the timings are now similar. If you want to play with WaffleGrid, grab the current code from launchpad at lp:~y-trudeau/wafflegrid/cream-5.4-tree. It is a full tree so… be patient. Use the build scripts showing memc in their name. For more information, you can also go to www.wafflegrid.com.

Remember, the project is still fairly young, don’t put any valuable data on WaffleGrid yet. Coming next, apart from making the table filtering more dynamic, I would like to add an exclusion list instead of just the current inclusion list. Of course, we also need to test WaffleGrid much more and for that we need help. If you test and find bugs, please use Launchpad at https://code.launchpad.net/wafflegrid to report the bugs you found.

You crash MySQL/Innodb and you trace the error to a function that says:

“This function checks the consistency of an index page when we do not
know the index. This is also resilient so that this should never crash
even if the page is total garbage. ”

Oops… I guess its not that resilient after all:)

Yves pointed this out to me:

http://www.internetnews.com/software/article.php/3825426/

“Moving beyond just providing Oracle compatibility, EnterpriseDB is adding new scalability features to their database. One of them is a feature called ‘”Infinite Cache,” which is based on the open source memcached application.

Mlodgenski commented that distributed memory caches are common, though management from a system management and a database perspective is often difficult.

“What we did is we took the distributed memory cache that was sitting in front of the database and put it behind the database,” Mlodgenski said. “This allows a simple SQL (define) interface for developers that allows access to the distributed memory cache and now the system management overhead is handed by the database itself.”

Mlodgenski added that Infinite Cache leads to a 10x to 20x performance gain for read application loads. ”

You can read more here:
http://www.enterprisedb.com/docs/en/8.3R2/perf/Performance_Guide-02.htm

and here:

http://www.enterprisedb.com/products/postgres_plus_as/overview.do

I guess it justifies that Waffle is a good idea.

So Ronald Bradford has been getting some EC2 AMI’s setup for Waffle the last couple of days. Really he is the first person to work with us on testing out the 0.5 cream release. It’s funny but when you are heads down in the code you tend to overlook some of the easy and obvious bugs. Just by having him walk through the install we have opened up several new bugs and come up with some new Idea’s on how to make things easier in the future. I hope by early next week we can have some of annoying bugs fixed and code pushed to launchpad. When that happens I would encourage others to give it a whirl and help us make Waffle Grid better performing, more user friendly, and more stable.

You can check out some of the bugs here.

Thanks to Ronald for his help on this!

In many applications, MyISAM can be used successfully if the proportion of write operations is only a small fraction of the read operations. As the tables grow, the 4 GB limitation in the key_buffer size caused performance issues and other strategies. Wait… did I miss something… From a recent comment to a post from Matt I re-read the MySQL documentation and realized I missed a big change that occurred with versions 5.0.52 and 5.1.23 (http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_key_buffer_size)

      As of MySQL 5.1.23, values larger than 4GB are allowed for 64-bit platforms

So, key_buffer as large or even larger than 32 GB are possible. That is an important design consideration that needs to be taken into account when tuning a database.

I have been asked this numerous times ever since I posted my InnoDB sample configuration files, Do I have sample configuration parameters for a MyISAM setup? We are seeing less and less people use MyISAM, but it is still popular ( especially in prepackaged form i.e. wordpress). So i figured why not adjust my sample InnoDB configs and make them suitable for MyISAM.

The biggest limitation to MySIAM is used to be the default key buffer only can could only be sized up to 4GB ( This was fixed in 5.0.52) . While you can create separate key buffers and assign indexes to them, it’s not very common ( Common as in present in low-end shops who need 5 minute dba help) in part because I think people do not fully understand it and you do have to plan for it. Setting up secondary caches is a database/application specific setup, it can not really be generalized, so I am not going detailing adding additional key caches here. My assumption is if you at the point of adding multiple key caches, you probably should not be copying your config of someone’s blog:)

As for the configs themselves they should look very similar to the InnoDB configs I published. In fact I change very little between the InnoDB and MyISAM configs.

Let me publish my disclaimers here. These are not the optimal settings for all environments, they are just meant to be starting points. The intended audience here are those developers and sysadmins who are DBA’s for 5 minuts at a time. They want a config that’s better then the default, “set it and forget it”. I may have missed a couple parms, so feel free to comment and I will update as needed.

Let get down to it:

On machines with more then 8GB, feel free to increase the key buffer accordingly.

I am probably being overly cautious on some of these, but these are not supposed to be optimal… they are supposed to be better then default. Adjust as needed.

I thought I would put out a quick note about this right now, I am going to be leaving Sun next week. I do have some plans which will start to materialize over the next few weeks and months so stick around for that. Even though I am leaving the company, I will still be lurking and working on all think database and performance related. This should not have any bearing on BigDBAHead.com or Waffle Grid, hopefully this will only mean I will post more and spend more time doing crazy and cool things!

I wanted to thank everyone at MySQL who I worked with and learned from over the past few years.

Lets get down to how the latest version of Waffle Grid performs.

Starting off simple lets look at the difference between the wafflegrid modes. As mentioned before the LRU mode is the “classic” Waffle Grid setup. A page is put into memcached when the page is removed from the buffer pool via the LRU process. When a page is retrieved from memcached it is expired so its no longer valid. In the New “Non-LRU” mode when a page is read from disk, the page is placed in memcached. When a dirty page is flushed to disk, this page is overwritten in memcached. So how do the different modes perform?

You can see here that with 100% of the data fitting in memcached we get about a 3x boost in performance over a non-waffle enabled setup. Note these tests here have the read-ahead enabled as well as the doublewrite buffer. With the ability to have all the data pages in memcached, the Non LRU solution shows up just a small touch faster the the classic LRU.

Read the rest of this entry »

I wanted to let everyone know that we are releasing Waffle Grid 0.5 code named Cream. This release fixes the nasty secondary index bug that plagued the butter release. I have been running tests on this code base for about a week straight with no errors. While I think this release is much more stable I would remind everyone this is still not a fully GA release. This release includes the ability to choose the mode of Waffle grid. By setting innodb_memcached_enable to 1, we will push pages to memcached when a disk read is done or when a page write is done, setting this to 2 will enable the classic LRU mode. If you decide to set this to 1 ( non-lru) I would recommend using the standard memcached, as with previous versions the LRU mode works better with our slightly altered memcached ( expire from memcached on get ). I will be posting benchmarks and more details within the next couple of days. Right now you can grab the patch on launchpad.