Recently we had a case where a customer got some corrupted blocks in his InnoDB tables. His largest tables where quite big, about 30 to 100 Gbyte. Why he got this corrupted blocks we did not find out yet (disk broken?).
When you have corrupted blocks in InnoDB, it is mandatory to get rid of them again. Otherwise your database can crash suddenly.
If you are lucky only “normal” tables are concerned. So you can dump, drop, recreate and load them again as described in the InnoDB recovery procedure in the MySQL documentation [1].
If you are not so lucky you have to recreate your complete database or go back to an old backup and do a restore with a Point-in-Time-Recovery (PITR).
To find out if some tables are corrupted MySQL provides 2 tools: The innochecksum utility [2] and the mysqlcheck utility [3] or you can use the CHECK TABLE command manually (which is used by mysqlcheck).
I wanted to know how CHECK TABLE works in detail. So I looked first in the MySQL documentation [4]. But unfortunately the MySQL documentation does not go into details that much very often on such specific questions.
So I dug into the code. The interesting lines you can find in the files handler/ha_innodb.cc and row/row0mysql.c. In the following snippets I have cut out a lot of detail stuff.
The function ha_innobase::check is the interface between the CHECK TABLE command and the InnoDB storage engine and does the call of the InnoDB table check:
// handler/ha_innodb.ccint ha_innobase::check( THD* thd ){ build_template(prebuilt, NULL, table, ROW_MYSQL_WHOLE_ROW); ret = row_check_table_for_mysql(prebuilt); if (ret == DB_SUCCESS) { return(HA_ADMIN_OK); } return(HA_ADMIN_CORRUPT);}The function row_check_table_for_mysql does the different checks on an InnoDB table:
// row/row0mysql.culint row_check_table_for_mysql( row_prebuilt_t* prebuilt ){ if ( prebuilt->table->ibd_file_missing ) { fprintf(stderr, "InnoDB: Error: ...", prebuilt->table->name); return(DB_ERROR); } index = dict_table_get_first_index(table); while ( index != NULL ) { if ( ! btr_validate_index(index, prebuilt->trx) ) { ret = DB_ERROR; } else { if ( ! row_scan_and_check_index(prebuilt, index, &n_rows) ) { ret = DB_ERROR; } if ( index == dict_table_get_first_index(table) ) { n_rows_in_table = n_rows; } else if ( n_rows != n_rows_in_table ) { ret = DB_ERROR; fputs("Error: ", stderr); dict_index_name_print(stderr, prebuilt->trx, index); fprintf(stderr, " contains %lu entries, should be %lu\n", n_rows, n_rows_in_table); } } index = dict_table_get_next_index(index); } if ( ! btr_search_validate() ) { ret = DB_ERROR; } return(ret);}A little detail which is NOT discussed in the code above is that the fatal lock wait timeout is set from 600 seconds (10 min) to 7800 seconds (2 h 10 min).
/* Enlarge the fatal lock wait timeout during CHECK TABLE. */mutex_enter(&kernel_mutex);srv_fatal_semaphore_wait_threshold += 7200; /* 2 hours */mutex_exit(&kernel_mutex);
As far as I understand this has 2 impacts:
If this is something which should be fixed to get a higher reliability of the system I cannot judge and is up to the InnoDB developers. But when you hit such symptoms during long running CHECK TABLE commands consider this.
For the first finding I have filed a feature request [5]. This “problem” was introduced long time ago with bug #2694 [6] in MySQL 4.0, Sep 2004. Thanks to Axel and Shane for their comments.
If you want to circumvent this situation you have either to recompile MySQL with higher values or you can use the concept of a pluggable User Defined Function (UDF) which I have described earlier [7], [8], [9].
An other detail is that at the end of each CHECK TABLE command a check of all Adaptive Hash Indexes of all tables is done. I do not know how expensive it is to check all Adaptive Hash Indexes, especially when they are large. But having a more optimized code there could help to speed up the CHECK TABLE command for a small percentage?
These information are valid up to MySQL/InnoDB 5.1.41 and the InnoDB plug-in 1.0.5.
[1] Forcing InnoDB Recovery
[2] innochecksum — Offline InnoDB File Checksum Utility
[3] mysqlcheck
[4] CHECK TABLE
[5] Bug #50723: InnoDB CHECK TABLE fatal semaphore wait timeout possibly too short for big table
[6] Bug #2694: CHECK TABLE for Innodb table can crash server
[7] MySQL useful add-on collection using UDF
[8] Using MySQL User-Defined Functions (UDF) to get MySQL internal informations
[9] MySQL useful add-on collection using UDF
In case you have not noticed, MariaDB is joining the list of projects thinking about how to improve MySQL replication. The discussion thread starts here on the maria-developers mailing list.
This discussion was jointly started by Monty Program, Codership, and Continuent (my employer) in an effort to push the state of the art beyond features offered by the current MySQL replication. Now that things are starting to die down with the Oracle acquisition, we can get back to the job of making the MySQL code base substantially better. The first step in that effort is to get a discussion going to develop our understanding of the replication problems we think are most important and outline a strategy to solve them.
Speaking as a developer on Tungsten, my current preference would to be to improve the existing MySQL replication. I suspect this would also be the preference of most current MySQL users. However, there are also more radical approaches on the table, for example from our friends at Codership, who are developing an innovative form of multi-master replication based on group communications and transaction certification. That’s a good thing, as we want a range of contrasting ideas that take full advantage of the creativity in the community on the topic of replication.
If you have interest in improving MySQL replication please join the MariaDB project and contribute your thoughts. It should be an interesting conversation.
If you’re using the latest packaged tornado distribution, tornado-0.2.tar.gz, then you might hit on the following bug when using AsyncHTTPClient:
ERROR:root:Exception in callback >
Traceback (most recent call last):
File “/usr/local/lib/python2.6/dist-packages/tornado/ioloop.py”, line 238, in _run_callback
callback()
File “/usr/local/lib/python2.6/dist-packages/tornado/httpclient.py”, line 214, in _perform
self.io_loop.remove_handler(fd)
File “/usr/local/lib/python2.6/dist-packages/tornado/ioloop.py”, line 133, in remove_handler
self._impl.unregister(fd)
IOError: [Errno 2] No such file or directory
For me, this was in the context of using a load testing script written by my colleague Dan Mesh. The script makes use of AsyncHTTPClient and runs several such objects in parallel, hitting a number of URLs. When the number of concurrent HTTP requests reaches around 100, I start seeing this error.
Solution? Install tornado from the latest source code on GitHub.
Release 1.2.2 of Tungsten Clustering is available on SourceForge as well as through the Continent website. The release contains mostly bug fixes in the open source version but there are also two very significant improvements of interest to all users.
The commercial version has additional features like PostgreSQL warm standby clustering, add-on rules to manage master virtual IP addresses and other niceties. If you are serious about replication and clustering it is worth a look.
This is a good time to give a couple of reminders for Tungsten users. First, Tungsten is distributed as a single build that integrates replication, management, monitoring, and connectivity. The old Tungsten Replicator and Myosotis builds are going away. Second, we have a single set of docs on the Continuent website that covers both open source and commercial distributions.
With that, enjoy the new release. If you are using the open source edition, please post your experiences in the Tungsten community forums or write a blog article. We would love to hear from you.
P.s., We have added Drizzle support thanks to a patch from Marcus Eriksson but it’s not in 1.2.2. For that you need to build directly from the SVN trunk. Drizzle support will be out in binary builds as part of Tungsten version 1.3.
To whom it may concern,
I uploaded a MySQL forge snippet for the f_easter() function. You can use this function in MySQL statements to calculate easter sunday for any given year:
mysql> select f_easter(year(now()));+-----------------------+| f_easter(year(now())) |+-----------------------+| 2010-04-04 |+-----------------------+1 row in set (0.00 sec)
To implement it, I simply transcribed the code of the “Anonymous Gregorian algorithm” from wikipedia’s Computus article.
You might ask yourself: “how does it work?”. Frankly, I don’t know. Much like a tax form, I treat the calculation as a black box. But, it’s wikipedia, so it must be right, right?
I also transcribed the algorithm to javascript, so I could use it in Kettle (a.k.a. Pentaho Data Integration). Of course, nothing should stop you from using it for another environment, such as a webpage.
I don’t have a proper place to host that code, so I’m listing it here:
//Script to calculate Easter day//according to the "Anonymous Gregorian algorithm" function easterDay(year) { var a = year % 19, b = Math.floor(year / 100), c = year % 100, d = Math.floor(b / 4), e = b % 4, f = Math.floor((b + 
/ 25), g = Math.floor((b - f + 1) / 3), h = (19 * a + b - d - g + 15) % 30, i = Math.floor(c / 4), k = c % 4, L = (32 + 2 * e + 2 * i - h - k) % 7, m = Math.floor((a + 11 * h + 22 * L) / 451), n = h + L - 7 * m + 114; return new Date(year, Math.floor(n / 31) - 1, (n % 31) + 1);}easter = easterDay(year);To use this in your kettle transformations, create a stream with an field of the Integer type called year. The year field should take on the value of some year. In the step, create one output field of the Date type to take on the value of the easter script variable. (For usage outside kettle, just use the easterDay() function as you see fit)
The thought may have crossed your mind: “So what, who cares – why should I ever want to know when it’s easter day?”
Apparently, if you think like that, you don’t like eggs very much. That’s ok – I don’t blame you. But I happen to like eggs, and people in the egg business like people that like eggs like me so they can sell them more eggs. In fact, they like selling eggs so much, that it makes a big difference to them whether their business intelligence reports say: “On March 22, 2008, we sold 10 times more eggs than on February 22 and May 22 of the same year” as compared to “In 2008, on the day before Easter, we only sold half the amount of eggs as compared to the day before Easter in 2009″.
In order to report these facts, special events and holidays like easter are stored in a date dimension. (I wrote about creating a localized date dimension, a date dimension that speaks your language some time ago)
So there you go: you could use these solutions in order to build a date dimension that understands easter. The nice thing about easter is that it can be used to derive a whole bunch of other Christian holidays, like good friday, ascension, and pentecost, and in many western countries, these will be special days with regard to the normal course of business. I leave all these as an exercise to the reader, but trust me – calculating easter is the key to a solving a lot of these problems.
Or an active-active fail-over cluster à la VMware.
Today I have learned about a totally crazy/cool looking architecture where the expensive VMware ESX server was replace by a free/cheap VMware Workstation version in combination with DRBD.
Basically DRBD we name “the little man’s SAN” and that is exactly what this customer is doing. He replaced the SAN with DRBD and now he can easily move one VMware instance to the other host. Possibly it is not that flexible and powerful as an ESX Server but also not so expensive…
The architecture looks as follows:
According to this customer it works stable on about a dozed of installations and they have not experienced any troubles during the fail-overs.
Please let me know your experience, thoughts or concerns with this architecture…
PS: When you consider such an architecture do not expect a very good performance!
This week has been kinda rough, and I’m anticipating more stress coming my way. -__-
I’m not sure if I’ll be able to draw much in the coming week or so, which makes me sad. But here are some sketchbook drawings done before icky times began.
I like this one above. I stole the colors from something pretty I saw online and can’t find now.
More girls as usual…haven’t felt like pushing myself to draw men and backgrounds and stuff. I’ll do that in…February. Or maybe March. BLEEEHHHAararrrghgg
Playing the ukulele makes her snarly. I make this same face when I eat cornflakes, even though I really like them.
sketch sketch doodle doodle barf
I kind of like how the big booty blue hair girl came out. And the girl under her has the hair I wish I could pull off!
I hope everyone’s first month of the new year is going good. T u T
This week has been kinda rough, and I’m anticipating more stress coming my way. -__-
I’m not sure if I’ll be able to draw much in the coming week or so, which makes me sad. But here are some sketchbook drawings done before icky times began.
I like this one above. I stole the colors from something pretty I saw online and can’t find now.
More girls as usual…haven’t felt like pushing myself to draw men and backgrounds and stuff. I’ll do that in…February. Or maybe March. BLEEEHHHAararrrghgg
Playing the ukulele makes her snarly. I make this same face when I eat cornflakes, even though I really like them.
sketch sketch doodle doodle barf
I kind of like how the big booty blue hair girl came out. And the girl under her has the hair I wish I could pull off!
I hope everyone’s first month of the new year is going good. T u T
I am sick and goofy on DayQuil. I wanted to do a mini-comic about this weeks hair salon disaster, but I’m already distracted, so here’s some random sketchbook stuff instead.
This background makes no sense, but I like the weird girl!
twirl twirl
Some weird animal drawings…
Also! There is an art show opening this Friday that I have a painting in. I’m hoping to make it, but I’m not sure if I’ll be contagious then or not. We’ll see! Here is more information:
Here’s how my painting looks framed and sitting up on my mantle…I made a blog post with details here if you want to see it closer!
Thanks for reading, stay healthy and don’t let sick people cough on you! I’ve been trying some new things lately when drawing, so let me know what you think.
I’m sleepy! -__-
zzzzzzzz
I am sick and goofy on DayQuil. I wanted to do a mini-comic about this weeks hair salon disaster, but I’m already distracted, so here’s some random sketchbook stuff instead.
This background makes no sense, but I like the weird girl!
twirl twirl
Some weird animal drawings…
Also! There is an art show opening this Friday that I have a painting in. I’m hoping to make it, but I’m not sure if I’ll be contagious then or not. We’ll see! Here is more information:
Here’s how my painting looks framed and sitting up on my mantle…I made a blog post with details here if you want to see it closer!
Thanks for reading, stay healthy and don’t let sick people cough on you! I’ve been trying some new things lately when drawing, so let me know what you think.
I’m sleepy! -__-
zzzzzzzz
