Diamond Notes

I blogged several times about some testing I have been doing recently. Well, I have run across something that just doesn’t make sense. I checked kernels and I/O schedulers and worked out what seems to do the best (2.6.22 kernel with deadline for those who are curious — I will post results soon). I had done all this with two servers that were identical and had 8 gb of RAM each. I then moved the 8 gb of RAM from one server to the other so one server had 16 gb of RAM. I then re-ran the last test with the only change being I raised the innodb buffer variable from four gb to eight gb.

I would have expected if there was any change it would be faster. I ran querybench five times and averaged the results as I had done with previous test. It was almost exactly 1500 qps slower than the previous run.

Anyone have any thoughts? I have run the test several times. I am rerunning the tests while closely examining the output of some of normal profile tools — top and iostat. It appears to me that the runs are io-bound if anything. The iowait percentage runs between the 25% - 40+ %. Top shows memory usage isn’t excessive (30% or 4.8 GB) and CPU percentage never tops 100% (so it doesn’t even max out one core of eight total cores).

My hunch is that the server is trying to fill up the buffer and IO is just bogging down the system. If I had a larger input file (I have 135 megs total of queries I am feeding into querybench) it might level out after “warming up” more.

My current formulae:

• I split the input queries into two files to warm the server up with the first set of queries

• I time both runs and then record the results of the second part (which is always higher than the warm up run)

Results show that . However I could be really wrong

Feedback would be very appreciated!!

When it comes to benchmarking it is useful to turn your query cache off sometimes. This can be done on both client connection level and the global/server level.

Client level:

to turn the cache off

SET SESSION query_cache_type = OFF;

and then to turn it back on

SET SESSION query_cache_type = ON;

Server level:

to turn off the cache

SET GLOBAL query_cache_type = OFF;

and to turn it back on

SET GLOBAL query_cache_type = ON;

Been a bit behind on tip of the days if you haven’t noticed. Sorry about that . It’s getting crazy trying to get out the next issue of the magazine, training our new jr dba (who suggested this tip..thanks David!!) and trying to get things in line for both a short vacation and then the Users Conference next month.

Excuses over, here is the tip. Today’s tip is dealing with the ENUM (enumeration) data type. ENUM is very similar to the SET data type. It can hold a list of allowable values. The values in the list are called members. An ENUM list can hold up to 65,535 members. Try typing that many in!! As an example of a very simple table:

CREATE TABLE enums (

userid INT NOT NULL AUTO_INCREMENT

value ENUM( ‘FALSE’, ‘TRUE’ )

PRIMARY KEY (userid)

);

Here is what is important to understand. Internally to the MySQL database enums are stored as integers beginning with a value of one and going to N where N is the number of members in the list. In our example above ‘FALSE’ would be represented internally as a ‘1′ and ‘TRUE’ is represented as a ‘2′.

There can be subtle “problems” (errors) when you don’t take into account the string nature of what you are inserting into a ENUM type and its internal integer representation.

If, instead of the above table example, I used the following table:

CREATE TABLE enums (

userid INT NOT NULL AUTO_INCREMENT

value ENUM(’0′,’1′)

PRIMARY KEY (userid)

);

Here I stored the values as ‘0′ for false and  ‘1′ for true which is a fairly common programming practice. I would argue that you shouldn’t, but let us assume you did. Internally these strings of ‘0′ and ‘1′ are stored as integers of ‘1′ and ‘2′.  Confused yet?

Do you see the problem?

What if I do this:

INSERT INTO enums (value) VALUES (’1′);

I get the expected value returned when I SELECT:

userid = 1

value = 1

ie we just set userid of one to “true”.

Now we insert this way:

INSERT INTO enums (value) VALUES (1);

meaning to insert a value of TRUE into the value. However, that isn’t what happened. The 1 is an integer, so MySQL uses the first value of the list which is a ‘0′ .. ie false

While the example is a bit contrived, it goes to show you that you have to be careful how you insert when using enums.

Bill McColl has a rather interesting blog post http://www.computingatscale.com/?p=54 on parallelism in software. His points are all valid. I was a bit suprised to see the number of software developers with parallel programming experience being put at such a low number. Just goes to show that people are resistant to change. We have had multiple CPUs for a decade or more now.

I love the bit about how disk drives are going to be the new tape drive (in the sense that the hard drives would only be used for backup). While that might be a bit of an overstatement for quite some time, it is interesting to note that until some dramatic new storage technology comes about memory will continue to increase relatively exponentially while hard drive improvements are incremental.

And it is a very cool paradigm to have your entire OS and DB in memory and operate out of RAM with no data being pulled into memory from disk after the initial startup. Then you just write out the changes to your hard drive. If a server were built with battery-backed system RAM like they build battery backed caches for RAID controllers this would be very possible and would sidestep the issues brought up by readers on this post I wrote a few days ago. Servers with battery-backed system RAM might already be available, but I am not familiar with them.

For those who require the ultimate in DB performance this is certainly something to consider.

Always something to think about!!

Update:  I love google.  Should have done this before posting.  If you have any interest in this topic take a look here.  Plug it into your server and have up to either 500 GB or a terabyte (not sure..both seem to be indicated) of what amounts to RAM based storage.  The price is astronomical, but hey we are dreaming right?

This might not be quite what you think.  One of the sys admins here at work ran into a small problem when moving some databases from one server to another.  When using mysqlimport to bring in the databases on the new server it (mysqlimport) choked on a table that had a space in the name.  He couldn’t get it to work until he used load data infile and put quotes around the table name: “table name”.  I just checked the man pages for mysqlimport and didn’t see any options that would help.

Now I would recommend that you never use spaces in your table names, but if you do and try to restore a backup keep this in mind!!

Thanks for the tip Justin!!

This is the best “set” of information in one place about the future plans for MySQL server.  It looks like it is from a presentation give by Robin Schumacher.  I don’t remember where I first saw this so I apologize for not linking to the source of the news.

http://www.day32.com/MySQL/MySQL_Roadmap_2008_2009.pdf

Since Ronald Bradford mentioned that Kickfire was a major sponsor of the MySQL Users Conference I wanted to throw in a few words about them. I have been talking to Kickfire for several months about their upcoming product line and let me say it is going to be fantastic.

They will have a major presence at the conference and you will need to check out their data warehouse product. I think, as George Truijillo expresses very well here, that small and medium platform data warehousing is going to be an area of great growth over the next few years. The product that Kickfire is introducing will only help to accelerate that growth.

In the upcoming issue of MySQL Magazine I will have a full length article on Kickfire and their new product line. I have spoken extensively with the people at Kickfire and I will have a chance first-hand to see the product in a few weeks. I might be speaking a little prematurely, but I believe there will be benchmarks included.

Don’t miss the article (or the magazine). It will be released on the website and at the conference on April the 15th.

Thanks to David, our Jr. DBA, we get the tip of the day. Did you realize that NULL (the topic of another post here) is not equal to the empty string (’ ‘). It is true. If you compare a NULL and an empty string you can see this:

mysql> select ‘ ‘ is NULL;
+————-+
| ‘ ‘ is null |
+————-+
| 0 |
+————-+
1 row in set (0.05 sec)

mysql> select ‘ ‘ is not NULL;
+—————–+
| ‘ ‘ is not NULL |
+—————–+
| 1 |
+—————–+
1 row in set (0.00 sec)

The zero returned means false, the one means true. This has implications to your code. For example, since an empty string is not NULL it can be inserted into a column that is set to NOT NULL.

For a more in depth discussion take a look here: http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

This was put up on LWN from a few days ago.  Very interesting information about what the author is call Ramback.  Basically, it is a RAM disk that automatically syncs to hard drive.  Of course this provides for durability.  While not so significant now, it might be something interesting in the future mix with massive amounts of RAM available.  He is working on getting it into the Linux kernel evidently.  Very, very alpha, but could be promising and worth a read:

article 

So when my server has 256 gigabytes of RAM I can allocate 128 to the server/mysql daemon and 128G to Ramback and use it as the hard drive.  HMMMMMMM 

If you are a newbie here is one for you…

Go to amazon.com and have them send this out: MySQL Tutorial

It is an excellent introduction to MySQL, SQL and  even some database administration thrown in for good measure.

The book is a little dated because it was released in December 2003, but is still a great way to get involved with the MySQL world.  At around 250 pages it isn’t so big that it overwhelms you and the exercises at the end of each chapter are a very helpful way to insure that you understand the material.