Do you have skip_name_resolve set in your /etc/my.cnf? If not, consider it. DNS works fine, until it doesn't. Don't let it catch you off guard.

Do you really need to restrict MySQL users based on hostnames? If you don't, you should probably disable this feature of MySQL's authentication system. You never know when your hosting provider's DNS (or your own for that matter) will go into the toilet. And when that happens, MySQL mysteriously stops letting users log in, and all kinds of chaos ensues. Worse, it can be kind of hard to know that this is the problem, and diagnosing adds to your downtime.

Here's another scenario: DNS doesn't really fail. It just gets a little bit slow. Subtle enough that you don't really notice it, but enough to cause connection problems every now and then.

I've seen both scenarios recently when working with clients. Oh, and did I mention that not enabling skip_name_resolve actually leaves you open to DoS attacks, if your servers are externally accessible?

To disable two DNS lookups per authentication attempt, you just need to set skip_name_resolve in your my.cnf file and restart MySQL. But before you do that, run the following command:

1. mysql> SELECT user, host FROM mysql.user
2.     -> WHERE host <> 'localhost' AND host RLIKE '[a-z]';
3. +------+--------+
4. | user | host   |
5. +------+--------+
6. | foo  | my.com |
7. +------+--------+

Any users you see here need to be converted to use IP addresses, IP address wildcards, or 'localhost' or they won't be able to log in after you disable DNS resolution.

For more information on how and why MySQL does both a forward and reverse DNS lookup on authentication attempts by default, read the MySQL manual page.

Entry posted by Baron Schwartz | One comment

Add to: | | | |

I usually get strange looks when I complain about memory handling inside InnoDB. It seems as if terabytes of RAM are so common and cheap, that nobody should really care about memory efficiency. Unfortunately for me, I do.

Examples:

• The infamous Bug#15815 - buffer pool mutex contention. The patch for the bug added lots of small mutexes, and by ‘lots’ I mean really really lots - two mutexes (and rwlock structure) for each buffer pool page. That makes two million mutexes for 16GB buffer pool, um, four million mutexes for 32GB buffer pool, and I guess more for larger buffer pools. Result - 16GB buffer pool gets 625MB locking tax to solve a 8-core locking problem. Solution? Between giant lock and armies of page mutexes there lives a land of mutex pools, where locks are shared happily by multiple entities. I even made a patch, unfortunately it gets some ibuf assertion after server restart though at first everything works great :)
• InnoDB data dictionary always grows, never shrinks. It is not considered a bug, as it isn’t memory leak - all memory is accounted by (hidden) dict_sys->size, and valgrind doesn’t print errors. 1-column table takes 2k of memory in InnoDB data dictionary, a table with few more columns and indexes takes already 10k. 100000 tables, and 1GB of memory is wasted. Who needs 100000 tables? People running application farms do. Actually, there even is a code for cleaning up data dictionary, just wasn’t finished, and is commented out at the moment. Even worse, the fix for #20877 was a joke - reducing the in-memory structure size, still not caring about structure count. And of course, do note that every InnoDB partition of a table takes space there too…

So generally if you’re running bigger InnoDB deployment, you may be hitting various hidden memory taxes - in hundreds of megabytes, or gigabytes - that don’t provide too much value anyway. Well, memory is cheap, our next database boxes will be 32GB-class instead of those ‘amnesia’ 16GB types, and I can probably stop ranting :)

How many of you use the mysql command-line client?  And did you know about the pager command you can give it?  It's pretty useful.  It tells mysql to pipe the output of your commands through the specified program before displaying it to you.

Here's the most basic thing I can think of to do with it: use it as a pager.  (It's scary how predictable I am sometimes, isn't it?)

1. mysql> pager less
2. mysql> SHOW innodb STATUS\G

For big result sets, it's a pretty handy way to be able to search and scroll through. No mouse required, of course.

But it doesn't have to be this simple! You can specify anything you want as a pager. Hmm, you know what that means? It means you can write your own script and push the output through it. You can't specify arguments to the script, but since you can write your own, that's not really a limitation. For example, here's a super-simple script that will show the lock waits in the output of SHOW INNODB STATUS. Save this file as /tmp/lock_waits and make it executable.

1. #!/bin/sh
2.  
3. grep -A 1 'TRX HAS BEEN WAITING'

Now in your mysql session, set /tmp/lock_waits as your pager and let's see if there are any lock waits:

1. mysql> pager /tmp/lock_waits
2. PAGER SET TO '/tmp/lock_waits'
3. mysql> SHOW innodb STATUS\G
4. ------- TRX HAS BEEN WAITING 50 SEC FOR THIS LOCK TO BE GRANTED:
5. RECORD LOCKS space id 0 page no 52 n bits 72 INDEX `GEN_CLUST_INDEX` of TABLE `test/t` trx id 0 14615 lock_mode X waiting
6. 1 row IN SET, 1 warning (0.00 sec)

Pretty useful, isn't it? But we can do even more. For example, the Maatkit tools are specifically designed to be useful at the command line in the traditional Unix pipe-and-filter manner. What sort of goodies can we think of here?

1. mysql> pager mk-visual-EXPLAIN
2. PAGER SET TO 'mk-visual-explain'
3. mysql> EXPLAIN SELECT * FROM sakila.film INNER JOIN sakila.film_actor USING(film_id) INNER JOIN sakila.actor USING(actor_id);
4. JOIN
5. +- Bookmark lookup
6. |  +- TABLE
7. |  |  TABLE          actor
8. |  |  possible_keys  PRIMARY
9. |  +- UNIQUE INDEX lookup
10. |     KEY            actor->PRIMARY
11. |     possible_keys  PRIMARY
12. |     key_len        2
13. |     ref            sakila.film_actor.actor_id
14. |     rows           1
15. +- JOIN
16.    +- Bookmark lookup
17.    |  +- TABLE
18.    |  |  TABLE          film_actor
19.    |  |  possible_keys  PRIMARY,idx_fk_film_id
20.    |  +- INDEX lookup
21.    |     KEY            film_actor->idx_fk_film_id
22.    |     possible_keys  PRIMARY,idx_fk_film_id
23.    |     key_len        2
24.    |     ref            sakila.film.film_id
25.    |     rows           2
26.    +- TABLE scan
27.       rows           1022
28.       +- TABLE
29.          TABLE          film
30.          possible_keys  PRIMARY
31. 3 rows IN SET (0.00 sec)

Now, that's handy.

What are your favorite ideas?

Entry posted by Baron Schwartz | No comment

Add to: | | | |

Recently I added the first code to support multiple MySQL backends in HSCALE (see svn.hscale.org). As a “side note” I have to thank Giuseppe Maxia for MySQL Sandbox which made multi server testing a bliss!

While coding this I started to feel that writing HSCALE on top of MySQL Proxy is no more as easy and clean as it started out to be. And finally I reached the point where I have to say:

HSCALE (and maybe other advanced multi-server applications) and (current) MySQL Proxy don’t fit very well.

Before I go into details please let me make clear that this is mostly due to the specific nature of MySQL Proxy being a connection and protocol based proxy. MySQL Proxy is great and there are a lot of cool applications that fit perfectly with it.

Aside from some other minor glitches (like missing tokens in SQL tokenizer) the biggest show stopper is:

The biggest problem I had to face is the handling of backend connections in MySQL Proxy. What I would need for HSCALE are dedicated connections to every backend for every proxy connection. So if a client connects to the proxy, a connection to each backend is opened and attached to this particular client connection. This way I easily could maintain the state of the connection by sending commands like SET variable or USE database to all backends.
Dedicated connections are vital for XA or transactions in general, too.

The way the proxy handles backend connections right now is somewhat cumbersome (See an example.). The way you have to maintain your own connection pool is hard to understand and uses too much “magic” in my opinion. And the pool only grows with the number of connections made to the proxy. People are having problems with this approach (read the MySQL Proxy forum, for instance this thread).

The current experimental multi-server code in HSCALE uses this connection pooling technique since it is the only way to establish connections to other backend servers.

1. Wait until MySQL Proxy supports dedicated backend connections This would be the easiest and most elegant solution. But it could be that Jan Kneschke and the other developers decide that this is not what they intended to do with the proxy. And this would be totally ok from their point of view! Even if they decide that this is a cool feature it could take a long while until it is implemented. (Hint: I would gladly help out here ).
2. Work around this using luasql While possible this solution would not be preferable since we are using two technologies for the same thing.
3. Switch to another proxy There are quite a few. This would be no fun and I did not take a look at them all to see if it is even possible. It would be complete rewrite though.
4. Fork MySQL Proxy or implement a plugin The new plugin technology could be used to implement the multi-backend connection stuff myself. As a last resort, I could fork the whole project (like Spock Proxy did, see below). This would still mean a lot of work though and loss of the MySQL efforts put into the proxy.
5. Use other sharding technologies and eventually abandon HSCALE As an example Spock Proxy, a fork of MySQL Proxy does a great deal of what we intend to do with HSCALE.
6. Re-implement HSCALE as a JDBC driver Since our applications are written in Java exclusively we could do that.

Of course I would love to continue HSCALE as a MySQL Proxy application! The main reasons are the efforts MySQL is putting into MySQL Proxy, the extensibility we gain combining multiple LUA scripts (there are more things we do with the proxy apart from HSCALE) and last but not least the community echo HSCALE already received.

As a next step I will take a deeper look into the proxy code and evaluate the efforts needed to add dedicated connections.

Robin just wrote a new article, titled A Quick Look at MySQL 6.0’s New Backup, and I thought, that maybe you’d like to also see this in presentation/video format…

At OSCON, Giuseppe actually gave a quick talk at the Sun booth, about our online backup. He also showed how to use it. All examples there, were done with the test-db sample database.

<embed id="VideoPlayback" src="http://video.google.com/googleplayer.swf?docid=4616374833721669976&amp;hl=en&amp;fs=true" style="width:400px;height:326px" allowfullscreen="true" allowscriptaccess="always" type="application/x-shockwave-flash"> </embed>
(MySQL Online Backup in Practice, video if the above doesn’t appear)

The following maintenance jobs are typically run against a MySQL database:

• ?Backup?

• Clean-up binary logs.

• Optimize tables

• Purge query cache

• Rotate binary logs

A backup is not a typical maintenance job. But it behaves more or less like one. The backup should be done regularly depending on the restore/PITR (Point in Time Recovery) requirements.

Make sure, that in the backup all the necessary files (data files, transaction log files, configuration files and binary log files) are included. To prove that the backup process is working properly a regular restore should be performed. This can ideally be combined with the set-up of new database instances for developers or testing.

The binary logs can be cleaned-up in two ways:

a) Passive by MySQL itself:

b) Active by the customers environment:

Make sure NO binary logs are purged which are still needed by a slave. In this situation the slave is lost and has to be set-up from scratch.

Make also sure binary logs are not removed by a file system operation (rm bin-log.*). Otherwise the database gets confused.

This operation internally copies the whole table and therefore can take a long time!

When there are SELECT queries with different sizes of result sets the query cache gets de-fragmented. This is shown by a lot of free space in the query cache but also a lot of not cached queries. Here it makes sense to purge the query cache from time to time.

What other MySQL maintenance jobs are you performing (not application related) I am very interested in...

As part of a project of Technocation, Inc I took a whole bunch of videos at OSCon 2008. The conference was about a month ago, and about 2 weeks ago I’d finished processing and uploading all the videos, but it was only today where I had the 5-6 hours I needed to finish posting all the video, and making this matrix of video.

The video may not be the quality that the O’Reilly folks took and put up on blip tv’s OSCon site, but all the videos here are freely downloadable or playable in your browser.

(more…)

Here's my presentation I gave May 12, 2008, at the Twin Cities MySQL and PHP User Group about my experience at the 2008 MySQL Conference and Expo.

Thanks to all of those that came. I had a great time!

Storage Snapshots are excellent tools in arsenal of a system administrator to create quick and consistent backups of their databases and applications. Snapshot is a “picture” of a filesystem at a point-in-time. In most modern snapshot implementations, this “picture” is not a full copy of the data, but rather a set of pointers to the data.

Here is a list of current industry leading snapshot technologies:

• LVM Snapshots: Snapshot capability built into the Linux Volume Manager (LVM) - the default option on a Linux system.

• ZFS Snapshots: Snapshot capability built into the ZFS filesystem on Solaris and OpenSolaris

• NetApp SnapShots: A feature of the WAFL® (Write Anywhere File Layout) filesystem

• Windows VSS: Volume Shadow Copy Service from Microsoft introduced in Windows Server 2003. VSS is also available with Windows XP, Vista, and 2008 Server.

• VxFS Snapshots: Snapshot capability built into the Veritas File System

• EMC SnapView: Snapshot capability on the EMC CLARiiON storage systems

• IBM FlashCopy: Available in the IBM Storage Subsystems (DS8000, DS6000, ESS 800, SAN VC)

• EqualLogic Smart Copy: Feature in the Dell EqualLogic PS Series

Our Zmanda Recovery Manager for MySQL product uses storage snapshots as one of the technologies to do a quick full backup of the MySQL database. We currently support LVM, ZFS, NetApp, VSS and VxFS Snapshots. Rest are coming soon…

It has been a while since the last Weekly Falcon Test Overview also called WFTO.

Our Falcon team was with the Falcon Developers Meeting in London last week. Other than that, I updated my workstation running the WFTO test suite from RHEL4 to RHEL5. This caused some delay in the weekly reporting.

The WFTO test suite now contains RHEL5 64-bit (native), Ubuntu 32-bit (vm), Windows 2003 Server 64-bit (vm), and Mac OS X/PPC 32-bit (native).

We have one new test case falcon_bug_36294 and three tests are not failing anymore.

• Jim Starkey fixed
  • Bug#36294 Assertion in Cache::writePage
  • Bug#36367 Falcon DBT2 crash in Cache::ioThread.

• Kevin Lewis fixed
  • Bug#36097 Be sure the highWater index is set to zero when a record is thawed
  • Bug#36636 Move fsync out of the #ifdef HAVE_PREAD scope so that it works in either case.
  • Bug#36603 Adjust age group values to uint32

• Vladislav Vaintroub fixed
  • Bug#36467 Falcon assertion in ha_partition.cc: virtual int ha_partition::extra()

• Christopher Powers fixed
  • Bug#36330 Falcon DBT2 crash in Transaction::needToLock

• Sergey Vojtovich fixed
  • Bug#33607 Falcon allows two mysqld instances to share a tablespace
  • Bug#33404 Falcon ignores partition-level TABLESPACE option

• Hakan Küçüky?lmaz fixed
  • Bug#36486 Falcon compilation fails on Solaris 10/x86
  • Bug#36400 Compiling Falcon on Solaris 10/x86 fails with Sun Studio 12

We are interested in you! Where do you use Falcon? What do you do with Falcon? Are there any features you want to see in Falcon? You can test Falcon and get famous by providing valuable bug reports or even test cases for Falcon!

• The test language we use is described here: MySQL Test Framework
• List of Falcon related bugs you will find here: http://bugs.mysql.com/saved/FalconBugs
• A preview version of Falcon you will find here: http://forge.mysql.com/wiki/Falcon_Feature_Preview
• Our MySQL Forum dedicated to Falcon related questions is here: http://forums.mysql.com/list.php?133
• To get latest Falcon source code, please see: http://dev.mysql.com/doc/refman/6.0/en/installing-source-tree.html

View original post | Add to del.icio.us| Updated 3 months ago | Share

After my previous post there were questions raised about Index Merge on Multiple Indexes vs Two Column Index efficiency. I mentioned in most cases when query can use both of the ways using multiple column index would be faster but I also went ahead to do some benchmarks today.

I'm using couple of simple tables:

1. CREATE TABLE `t1000idxmerge` (
2.   `i` int(11) NOT NULL,
3.   `j` int(11) NOT NULL,
4.   `val` char(10) NOT NULL,
5.   KEY `i` (`i`),
6.   KEY `j` (`j`)
7. ) ENGINE=MyISAM DEFAULT CHARSET=latin1
8.  
9. CREATE TABLE `t1000idx2` (
10.   `i` int(11) NOT NULL,
11.   `j` int(11) NOT NULL,
12.   `val` char(10) NOT NULL,
13.   KEY `i` (`i`,`j`)
14. ) ENGINE=MyISAM DEFAULT CHARSET=latin1

I have populated this table with random data for i and j having both of them having 1000 of distinct values, independent on each other. I also created couple of other tables with same data just with really low cardinality with i and j having just 3 values each. The table contained about 18M rows though was small enough to fit in the systems memory.

I've benchmarked simple queries using where clause which covers multiple columns:

1. Q1 SELECT sum(length(val)) FROM  T  WHERE i=2 AND j=1
2. Q2 SELECT sum(length(val)) FROM  T  WHERE i=2 AND j BETWEEN 1 AND 2
3. Q3 SELECT sum(length(val)) FROM  T  WHERE j=2 AND i BETWEEN 1 AND 2
4. Q4 SELECT sum(length(val)) FROM  T  WHERE i=2 OR j=1
5. Q5 SELECT sum(length(val)) FROM  T  WHERE j=2 AND i BETWEEN 100 AND 200

As some of them there way too fast if run once I ran them multiple times and measured time appropriately. To remove the overhead of starting MySQL etc from equation I also measured execution of "SELECT 1" query using same script and subtracted this time from result in the table.

time for ((i=0;i<100;i+=1)); do mysql test -e "SELECT sum(length(val)) FROM t1000idxmerge WHERE i=2 AND j=1"; done > /dev/null

In the result table I compute per query results and present results in milliseconds.

Note1: Q1 will not use Index Merge technique for low cardinality table but instead pick to do single index scan. I'm not aware of the optimizer hint which would allow to force index merge as you can do with index accesses in general.

Note2 Q2/Q3 can't use Index Merge however as it is currently implemented so they would use single index range scan. The 2 indexes however benefits to Q3 because it can only use first keypart of index (j,i) to resolve BETWEEN part of the clause which may not be very selective.

Note3 You may be surprised why 2 column index is faster for Q3 in case of low cardinality even though MySQL can't use index well. You're right MySQL can't and MySQL does not - Full table scan is performed and in this case turns to be faster than scanning 1/5th of the table using index. Also Full Table Scan is preferred for Q4 in all cases but in case of high cardinality multiple index configuration.

Note4 Q5 was just run on high cardinality tables to show what difference large BETWEEN can make.

Conclusion: For benchmarked queries we can see Multiple Column index beats Index Merge in all cases when such index can be used. It is also worth to watchout a MySQL may decide not to do Index merge (either intersection or union) but instead do full table scan or access table picking only one index on the pair.

Entry posted by peter | No comment

Add to: | | | |

Last week, at the end of JavaOne day one, I was able to grab the JRuby dynamic duo for a podcast.  Tom Enebo and Charlie Nutter turned out to be really nice guys, must be their midwestern roots, and were a pleasure to talk to (I also learned a lot :). 

My interview with Tom and Charlie (14:19)  Listen (Mp3)   Listen (ogg)

Charlie Nutter and Tom Enebo -- the JRuby dyanmic duo (and wearing the shirts to prove it).

Some of the topics we tackle:

• A historical background, Ruby, then Rails, then JRuby.
• What the guys were doing pre-Sun and how Tim Bray lured them to the fiery orb.
• Three licenses is better than one (GPL, CPL, LGPL)
  
• How JRuby is working with GlassFish and NetBeans.
• The DaVinci machine project, jMaki and what ever happened to AJAX?
  
• Might we see a JPerl?
• Looking to get more involved with MySQL

New features for the next release. As follows:

1. Status page now reports daily error code summarizations, connection failures to hosts
2. Talkback report page now reports on myback_talkback script version number
3. Talkback report page now tracks the my.cnf file during the backup process, and the cnf file contents for each host are stored as LONGTEXT in the monitor database
4. Multi-tier access levels, admins (rw) and general users (ro)
5. View tables for the status page summarizations speed up reporting
6. Daily summary email - similar to the status page, instead of singular emails for each backup success/fail notification

Look for 1.3 being released very soon! You’ll be able to find it here: http://sourceforge.net/projects/monolith-mysql

Quite common beginners mistake is not to understand how indexing works and so index all columns used in the queries…. separately. So you end up with table which has say 20 indexes but all single column ones. This can be spotted with a glance view. If you have queries with multiple column restrictions in WHERE clause you most likely will need to have multiple column indexes for optimal performance. But wait. Do not go ahead and index all combinations. This would likely be poor choice too

Entry posted by peter | No comment

Add to: | | | |

In April this year, Kickfire announced the first high-performance appliance for MySQL. As part of the announcement, the company released data warehouse benchmark results that broke prior records in terms of price/performance and performance in a non-clustered environment. While the creation of a new appliance built exclusively for MySQL along with the benchmark records was noteworthy, perhaps the bigger story lies in what we believe to be the beginning of a paradigm shift in the database world - one marked by the advent of the first SQL chip.

To give some context to this story I have included a graph below which depicts the evolution of VLSI (Very-Large-Scale Integration) semiconductor technology and its growing impact on a broadening range of industries.

Specifically, this diagram shows that as VLSI density (# transistors per sq millimeter) has increased over time per Moore’s Law, it has been possible to transition an increasing number of applications from a “software/CPU” model to a “custom chip” model. Starting with Digital Signal Processing in the 1970’s through to SQL Processing today, there has been a long history of industries that have witnessed this transition and seen a major upheaval in the status quo.

Take graphics processing as an example. Initially, the graphics market was led by companies such as Silicon Graphics with their high-end terminals built on a combination of proprietary software and general-purpose CPUs. This all changed with the arrival of the graphics chip. Designed by companies like ATI and Nvidia, the graphics chip delivered a much higher price/performance ratio, which opened up high-end graphics processing to a much broader audience (e.g. gamers) and transformed the industry. Silicon Graphics, now called SGI, is worth $73M today. Nvidia is worth 100 times more at $7.3B.

The question you might be asking yourself is why these particular applications? What is it about these applications that made them suitable for such a transition? In a word, Dataflow.

The common characteristic underlying these application domains is that they all deal with the need to process large volumes of data at high speed. Now, general-purpose CPUs are based on the von Neumann architecture which was conceived in the 1940’s at a time when data volumes were much much smaller than today. This architecture is an instruction-centric or control flow one that is good at processing large numbers of instructions quickly but not well suited to processing large data sets due to the so-called von Neumann bottleneck.

What the pioneers in each of the application domains we mentioned discovered is that a Dataflow architecture is much better suited to solving the problem of high-volume data processing because it eliminates the von Neumann bottleneck. In a dataflow architecture the data, as opposed to instructions, flows directly through the execution engine. There are no wasted clock cycles spent waiting for data to arrive into the registers as in the case of the von Neumann architecture. The difference is significant. As an example, a single SQL chip from Kickfire provides better performance than 10’s of CPU cores, as demonstrated in the data warehouse benchmark results we published.

In my next post I’ll discuss this topic a little more, explaining why the transition from general-purpose CPU to custom chip is only happening now in the database world and why we believe this will be an irreversible trend.

Quite common beginners mistake is not to understand how indexing works and so index all columns used in the queries…. separately. So you end up with table which has say 20 indexes but all single column ones. This can be spotted with a glance view. If you have queries with multiple column restrictions in WHERE clause you most likely will need to have multiple column indexes for optimal performance. But wait. Do not go ahead and index all combinations. This would likely be poor choice too

Entry posted by peter | No comment

Add to: | | | |

So you need to design highly available MySQL powered system… how do you approach that ?
Too often I see the question is approached by focusing on expensive hardware which in theory should be reliable. And this really can work quite well for small systems. It is my experience - with quality commodity hardware (Dell,HP,IBM etc) you would see box failing once per couple of years of uptime which is enough to maintain level of availability needed by many small systems. In fact they typically would have order of magnitude more availability issues caused by their own software bugs, DOS attacks and other issues.

However as your system growths the reliability goes down. If you have 100 servers with each failing every 2 years this is about a server a week which is bad and if you’re into thousands and tens of thousands of servers server failures are becoming common place so it is important to make sure failing server does not affect your system and also what you can recover from server failure easily

So you should assume every component in the system can fail (if it is Server,Switch,Router,Cable, SAN) etc and you’re ready to deal with this. It does not mean you always have to ensure you stay fully operational after any failure but at least you should understand the risks. For example you may want to choose to keep single Cisco router because it has its own internal high availability on the component level which makes it extremely unlikely to fail, because you have 4 hour onsite repair agreement and because it is just freaking expensive. Though may be redundant less expensive systems could be better choice.

I would highlight again every component can fail it does not matter how redundant it is inside. The SAN is very good example - I’ve seen Firmware glitches causing failure in the SAN which was fully redundant on the component level. It is not every hardware component but also any code may fail as well. This is actually what makes your own code often the weakest link in availability.

Depending on failure rate you also should be thinking about automation - for frequent failures you want to recovery (like getting spare Web server and putting it online) to be automatic or done with simple manual command. For complex and rare failures you may have less automation - if certain type of failure happens once per couple of years for many evolving systems there is very high chance the old automation tools may not work well (this is of course unless you always test all automated failure scenarios regularly).

So if we’re designing the system so it can tolerate hardware failures should we bother about hardware quality at all ? The answer is yes in particular for classic database/storage systems. Few systems are design with so much error detection and automated handling in mind as Google File System.

In particular you want to make sure Error Detection is on the good level. For example if you’re running the system without ECC memory chances are your data will melt down and you will not notice it for long time (in particular if you’re using MyISAM tables) which can cause the error to propagate further in the system and make recovery much more complicated than simply swapping the server. This is exactly one of the reasons many high scale installations prefer Innodb - it is paranoid and this is how you want your data storage to be. This is also why Sun is so proud about checksums on the file system level in ZFS.

What is about RAID when ? As strange as it may sound but you should not relay on RAID for your data safety. There are many ways to loose data on RAID system even if you’re running RAID6 with couple of hot spare. The RAID is just dramatically reduces chance of data loss in case of hard drive failure and this is good because recovering database servers is not fully automated in most cases. Plus there may be system performance impact and (in particular if you use MySQL Replication for HA) the switch to the new server may not be 100% clean with few updates lost. RAID, especially with BBU also makes a good sense to get extra performance out of the box.

Some installations are using RAID0 for slaves - in these cases there are typically many slaves and recovery of the slave is very easy and causes no business impact. This is fine assuming you do the math and the performance gains or cost savings are worth it.

Another good RAID question is if Hot Spare should be used. I normally do not use it because it a large waste, especially as most of systems have even number of drives, so if you’re looking for RAID10 setting up hot spare costs you 2 drives. Having hot spare does not add a lot to high availability - if you have proper RAID monitoring in place and keep couple of spare hard drives on the shelf in the data center we’re speaking about couple of extra hours running in degraded mode. Even if you do not have spare hard drive you can often pool the one from the spare server and have the “warranty man” to replace it instead.

It is also a good question if you need redundant power supplies. In my experience they rarely fail so having redundant power supplies does not increase availability when it comes to hardware failures that much and so if you just look from this angle it may be justified only for the most critical servers. Do not forget redundant power supplies also increase server power usage a bit. Redundant power supplies however are helpful if you have multiple power feeds, so server can stay up if one of the phases has a power loss. Another benefit is - in redundant power supply will often allow to do some power work (like moving server to different circuit) without downtime which may be or may not be something important for you.

Finally I should mention about spare component. These are paramount if you’re designing highly available system. Having spare drives on the shelf, spare switches, spare servers (which are same as better as servers which are in production) is paramount. It is important promotion happens easily and there are no performance gotchas (ie 8 core server can be slower than 4 core with MySQL). It is best if you just put couple of spare servers in each purchase batch so they are absolutely same configuration but I know it is not always possible. Dealing with spares is yet another reasons to avoid the “server zoo” and have limited set of purchased configurations which are reviewed yearly (on other regular interval) rather than finding different best configuration each week.

Having spare servers also means you often do not need most expensive support agreements and Business Hours Monday-Friday is good enough for you - you’re not waiting for support for production anyway just fall back to another server and use it. Of course you can imagine cases when problem could affect all servers of the same type but it is not that frequently seen in practice.

To avoid multiple servers failing at the same time it is of course important to QA/Stress test servers before you put the load on them. I’ve seen multiple cases when something would go wrong and all servers of same configuration will experience the same problem. Proper QA/Stress test reduces the chance of this but you better to be testing with load similar to what you expect in production.

Requirement to have Spare hardware is also the reason why commodity inexpensive hardware is often better choice. If you have couple of $1M in production you need another $1M server as a spare and this is expensive. If instead you have 10 pairs $10K boxes having couple of spares would cost you only $20K plus I found it in many cases much easier to convince “finance” people to buy something cheap which is not used most of the time when to spend a lot of money on the server which will be where sitting doing idle.

How many spare servers you need - you would see it in practice. As I mentioned at least one for any hardware class you have. If you have many failures you need more of course. You may also decide to keep more spare systems when you can use them to help capacity management, especially if you have multiple applications which do not share hardware but share the data center. You may have “spares” to provide extra on demand capacity for web servers or memcached quite easily, or say increase number of slaves if you have unexpectedly high number of reports launched by users etc.

Entry posted by peter | No comment

Add to: | | | |

Send in by Ruben Daniels

Lustre is one of the most popular upcoming open source cluster file systems out there. When you want to run Xen’s from a SAN using Lustre you need to support both in the Linux kernel. Both XEN and Lustre are near mature products. This means there is support for it. But it’s quite difficult to find the right source and to combine it with the right kernel source of each. It took me a week of trial and erroring until I found a combination that worked. Since Google wasn’t much help I wrote this article so it might help you. This installation is Ubuntu Gutsy specific. You can start out with a basic Gutsy installation. Hardy is getting Lustre support, but at the time of this writing the package doesnt match the default kernel of Hardy.

First we apt-get some utilities and Xen packages

Now we get the kernel from kernel.org

The best matching (and stable) lustre source I’ve found is in the Hardy Lustre source package. So we get it and extra it manually:

The official XEN kernel version is 2.6.18. This is way too old for newer machines. So we’ll get the Gutsy kernel source which contains XEN patches for our 2.6.22 kernel and apply them:

Now we apply the lustre patches:

Make the Kernel:

If you use kernel modules you need to run the following command to create the ram fs.

Add the kernel to grub’s menu.lst. You could do this manually, but let’s just use the ubuntu script.

Reboot the machine. After reboot build Lustre:

Reboot the machine again. You should now have a working lustre/xen kernel.