The first is on building a MySQL Data Access Layer with Ruby and Sinatra. While this may seem a bit odd, its actually very cool and useful. With < 100 lines of code you can build some pretty awesome web services to expose your data to the outside world or even better yet build web services to replace developer maintained ORM’s! Additionally if you use this as part of a broader framework you can achieve all kinds of coolness. In fact all the code that I will show was written in < 2 hours, and it only took that long because I was trying to make it flow into the presentation.

The second presentation, How I learned to stop worrying and Love Big Data should be fun. I promise the following: An interesting take on Big data, Dr Strangelove, Simpson’s Humor, Daleks, wacky signs, Darth Vader, and a Monty Python reference… oh yeah this should be fun.

You can see that adding memory to the innodb buffer pool slows things down. Looking at the Innodb status, you can see things getting locked up. What interesting though is you can mitigate alot of this simply by making use of multiple buffer pools.

Here is where it waiting:

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 529411, signal count 133604
--Thread 140360422000384 has waited at /var/lib/buildbot/slaves/percona-server-51-12/DEB_Ubuntu_maverick_amd64/work/Percona-Server-5.5.10-rc20.1/storage/innobase/buf/buf0buf.c line 3483 for 0.0000 seconds the semaphore:
S-lock on RW-latch at 0x412fb68 '&buf_pool->page_hash_latch'
a writer (thread id 140360421799680) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file /var/lib/buildbot/slaves/percona-server-51-12/DEB_Ubuntu_maverick_amd64/work/Percona-Server-5.5.10-rc20.1/storage/innobase/buf/buf0buf.c line 3483
Last time write locked in file /var/lib/buildbot/slaves/percona-server-51-12/DEB_Ubuntu_maverick_amd64/work/Percona-Server-5.5.10-rc20.1/storage/innobase/buf/buf0lru.c line 1626
--Thread 140355466110720 has waited at /var/lib/buildbot/slaves/percona-server-51-12/DEB_Ubuntu_maverick_amd64/work/Percona-Server-5.5.10-rc20.1/storage/innobase/buf/buf0lru.c line 813 for 0.0000 seconds the semaphore:
Mutex at 0x412fb28 '&buf_pool->LRU_list_mutex', lock var 1
waiters flag 1
--Thread 140355465910016 has waited at /var/lib/buildbot/slaves/percona-server-51-12/DEB_Ubuntu_maverick_amd64/work/Percona-Server-5.5.10-rc20.1/storage/innobase/buf/buf0buf.c line 4398 for 0.0000 seconds the semaphore:
Mutex at 0x412fb28 '&buf_pool->LRU_list_mutex', lock var 1
waiters flag 1
--Thread 140360422201088 has waited at /var/lib/buildbot/slaves/percona-server-51-12/DEB_Ubuntu_maverick_amd64/work/Percona-Server-5.5.10-rc20.1/storage/innobase/buf/buf0buf.c line 4398 for 0.0000 seconds the semaphore:
Mutex at 0x412fb28 '&buf_pool->LRU_list_mutex', lock var 1
waiters flag 1
--Thread 140355466311424 has waited at /var/lib/buildbot/slaves/percona-server-51-12/DEB_Ubuntu_maverick_amd64/work/Percona-Server-5.5.10-rc20.1/storage/innobase/buf/buf0buf.c line 4398 for 0.0000 seconds the semaphore:
Mutex at 0x412fb28 '&buf_pool->LRU_list_mutex', lock var 1
waiters flag 1
--Thread 140355466512128 has waited at /var/lib/buildbot/slaves/percona-server-51-12/DEB_Ubuntu_maverick_amd64/work/Percona-Server-5.5.10-rc20.1/storage/innobase/buf/buf0buf.c line 4398 for 0.0000 seconds the semaphore:
Mutex at 0x412fb28 '&buf_pool->LRU_list_mutex', lock var 1
waiters flag 1
Mutex spin waits 455865, rounds 16856962, OS waits 524701
RW-shared spins 29949, rounds 192489, OS waits 3591
RW-excl spins 499, rounds 26672, OS waits 117
Spin rounds per wait: 36.98 mutex, 6.43 RW-shared, 53.45 RW-excl

Long story short, if your using compression + Innodb you may want to look into using multiple buffer pools until this is fixed.

I am going through some of the sessions for IOUG’s Collaborate 2011 Conference and trying to fill in slots for the bootcamp, and while we have some great sessions we could use a few more sessions. Specifically I would love to get a couple of sessions on a few the following topics:

• InnoDB in General
• InnoDB Internals & scalability
• General Overview of available Storage Engines (Pros/Cons)
• MySQL options for Very Large databases ( ala Partitioning, Sharding )
• MySQL Monitoring Options
• NDB Cluster

These are just a few suggestions, please feel free to submit any topic thats related to MySQL…  even if its not a fit for the bootcamp, there is a MySQL track that it would fit into.  IOUG extended the MySQL Deadline until next Monday for us, so let’s get some more papers in!  You can submit here …

Now I know  many folks are torn because the MySQL UC falls during this same time…  but there should be enough room for both conferences.  I know many DBA’s and Developers who are torn when it comes to which conferences to attend as they are forced into supporting multiple database technologies (Oracle, SQL Server, DB2, MySQL, etc) .  Collaborate gives these people a place to go and learn about more then Just MySQL.  Recently I have seen many “Classic Oracle DBA’s”  asking more questions and trying to Learn more about MySQL.  This is one opportunity where the MySQL community has the chance to leave a lasting impression on Oracle users who have not had any MySQL experience before.

This is untamed territory!  Come on down to Orlando and help Oracle users understand the benefits of MySQL!

Matt

The reason I am posting is we are going to need lots of help from you. We are looking for speakers for the conference at the moment… you can submit your papers here: http://www.ioug.org/callforspeakers. Think of it like this, this is an opportunity to be a pioneer… reaching people who may have never attended a MySQL Related conference, heard the good word of MySQL, or maybe even know much about Open Source. Hmmm, maybe I should dress up as a cowboy or pioneer for the event! The question is will that attract or turn people away. Maybe we should vote on it!

You can register here:
http://www.ioug.org/Events/IOUGWelcomesMySQL/tabid/164/Default.aspx

I hope to see you there.

Also you have to “warm” cassandra like other databases… after loading my 1M rows, I ran some quick tests. 125 ops/s, 288 ops/s, 311 ops/s, 1530 ops/s, 1872 ops/s, 1868 ops/s…

It looks like I am really bottlenecked by the thrift calls in ruby ( per profile )… strange I am seeing the CPU tap out at 1 core when testing with this data set, testing with a smaller dataset or with python I use multiple cores… must just be a red haring.

Getting occasional ruby socket timeouts from thrift… need to look into that.

The fact of is if a solution meets your needs and it works it is not wrong (it may have flaws or risks to different degrees). In the case of an RDBMS -vs- NOSQL, for some applications one is better then others. The issue I think we all run into is not really the merit of NOSQL -vs- a traditional RDBMS its the willingness to accept alternative views. Too many shops out in the world are all about the new hotness and not about what’s best for their application or organization. While other people would rather die then allow there database to be taken away from them. For some apps, durability is not a big deal for others it is. Everyone has different requirements. Just because Digg or Twitter or Rackspace is doing NOSQL and it works for them does not mean you have to use it, or that it will even work for you. In fact, if you leap without thinking you may in fact hurt yourself more then solve your problems. Every situation is unique and before you jump head first into one solution or another take a breath and analyse the situation. Ask questions like : Why are we thinking about NOSQL? Is just because of HA ( hey RDBMS’s can handle that! ), is it to replace sharding? Is it to do something else? … Ask yourself about the work you need to do: do you need to do complex joins? How much data will your really have? What sort of workload do you have? Really define your goal, then research and test solutions. I am sure that the big names using Cassandra or Hbase did not read a blog post somewhere and start converting everything that day, and you should not either.

Also Be careful of all the analysis, all the opinions, benchmarks, etc you see on the web on the topic. These are specific to a certain workload or user. Take Joe’s post (pro nosql from above), he says “Anyone out there running an EC2 large instance with a RDBMS on it that’s doing 1,800 reads/second? I’ve got a Cassandra node that was getting hammered with a load of 6 serving that much traffic without falling over..” taken out of context I could say, well hell my laptop this morning got 1200 reads/second on Cassandra and 4,000 reads/second with innodb. Does that mean MySQL is 4x better then cassandra? Well in a certain workload, under certain conditions sure… but I can write another benchmark that shows the opposite. By the way yes I have gotten well more then 1800 reads/second on an ec2 large instance…. but the workload is probably so different it’s a worthless comparison.

Facts and figures can be used to sway opinions, especially when variables are unknown. Let me show you what I mean. One of my colleagues was getting 55K read/write operations per second on a new server the other day. Joe ( Joe I am not picking on you directly, really ) posted he gets 1800/s on a large ec2 server. That **could** mean that Cassandra would need 31 large ec2 instances to match the power of that one server. That’s a cost of ( $2978.40 per aws large instance) of $92,330 per year. It’s over 3x the cost of the particular server that achieved 55K ops. Who would want to pay 3x more for the same performance right? This Proves SQL is awesome and NOSQL Sucks right? The answer is NO. Again the workloads are probably so different one may lend itself better to SQL. What if Joe has 1TB of data and I only had 100G, well that changes the equation and we would have to adjust to account for that. In this case with 31 servers if I could process 31TB of data at that consistent speed, then it maybe worth it, depending on how long it takes a single RDBMS to deliver results over 31TB.

I guess I am trying to say, make a decision based on your own tests and your own workload. There is nothing wrong with you considering either option as they have their merits and their place in the world:) There certainly is nothing wrong with listening to all of the banter about our experiences and our opinions. But even if really smart people tell you all kinds of reasons why NOSQL is better then a RDBMS, or other Equally Smart people tell you why an RDMS is better then a NOSQL Solution, evaluate for yourself and make an informed decision. A lot of these smart people are looking at the problem from there own unique experience. If someone had a bad experience with MySQL and did not have a good DBA, they may view MYSQL in a very negative light. Similarly if you have optimized, developed, and improved MySQL over the years you may view NOSQL solutions as foreign and filled with risk. Also remember sometimes really smart people sometimes do really dumb things ( I could talk about all the really smart people I know, and the rather non-common sense approaches they have tried because they are so close to a problem).

When I started really digging into some of the other popular nosql solutions to expand my benchmarks it became apparent that most of these tools have fairly well defined API’s for Ruby, however in general the API’s for perl in some cases may not exist at all or are rather immature at this point. So I decided to rewrite my initial benchmark suite in Perl. With the help of my co-presenter for this talk ( Yves ) we are writing a tool that will hopefully be able to test the same basic tests against a wide variety of solutions. Currently I have tests written for Tyrant, Memcached, Cassandra, and MySQL. We will be expanding these tests to include Redis and MongoDB for sure (Maybe NDB) … beyond that I am not 100% sure. The challenge is going to be writing code that not only tests basic features, but also can test the advanced features of these solutions. After all a simple PK lookup can be done on all of these solutions, but that’s not necessarily the bread and butter of a solution like MongoDB or even Cassandra. Its the extra features that make these more compelling. We will be releasing the code when its ready.

I have not started my more exhaustive benchmarks yet… as I am still writing parts of the benchmark, but I have been running a few benchmarks. I generally hate publishing or mentioning results until I have taken the time to analyse them and ensure I did not miss anything, but what the hell. In a very short read only test, using PK based lookups to compare Innodb -vs- cassandra -vs- memcached ( a really small data set that should easily fit into memory on both on my laptop **single node **) I end up averaging ~1.2K reads per second from Cassandra, ~ 4K reads per second from Innodb, and ~ 17K reads per second in memcached. Now as I setup more benchmarks I will test multi-node performance, tune the configs for the workload, etc… but it is interesting to see the early performance difference.

More later.

A quick overview, Tokyo Tyrant is the network daemon that sits ontop of Tokyo Cabinet. This means that in order to access cabinet from another server you have to access it though Tyrant. In the context of this post consider when I say Tokyo to mean the entire stack.

#1. Tokyo Cabinet allows for a single write thread. Multiple processes can try and write through tyrant but they will wait. In order to get around this limitation you need to shard your data. Using something like a memcached api ontop of a hash table is one effective way to do this.

#2. Tokyo is not durable. This means in the event of system crash you will lose data. You can call a sync process to sync data to disk, but this locks the writer process. Your best bet is to use replication to ensure you have a copy of the data and backup often.

#3. Settings for Tokyo Cabinet Files can be set via Tokyo Tyrant by adding the settings after the cabinet file: i.e.

/var/lib/tokyo/data.tch#BNUM=20000#xmsiz=10485760

Some of these settings only take place on file creation or on optimize so make sure you check the documentation.

#4. By Default there is a limit of 2GB per file to Cabinet files, this can be worked around by setting the #opt setting for your table type. For instance #opt=HDBTLARGE enables large files for the hash table. This setting takes place on creation or when you optimize. You will corrupt your file if you hit 2GB without this setting. If you experience this, your best bet is to restore from a backup that is < 2GB and switch the large file flag. (Note if I am correct you can only change the file to support large tables by using the cabinet mgr tools, i.e. running tchmgr -tl cabinet.tch against an offline file )

#5. Run optimize on a regular basis, I have seen files shrink by as much as 90% from running optimize.

* To run optimize on a table from tyrant you can run tcrmgr optimize -port xxx localhost ( This will lock writes )
* To run optimize a table from the cabinet command use the mgr for the correct table type ( i.e. tchmgr for the has table ).

#6. Increase the number of Tyrant threads from the default 8 if your having issues with refused connections. This is done on the command line when starting tyrant: ttserver -thnum 16

#7. Log your Tyrant errors to a log file by using the -log flag when starting Tyrant. By default just setting the log will also log info/warning messages, disable this by setting the -le flag which tells tyrant to only log errors.

#8. If your using a cabinet “table” database make sure you build the indexes you need otherwise your probably going to get rather slow performance.

#9. In terms of performance the BNUM setting typically has the largest impact on performance. According to the docs “specifies the number of elements of the bucket array”. Every table type is a bit different, so check the docs for the exact settings.

#10. For hash tables setting xmsiz can make a huge difference. This defines the memory allocated to mapping objects.