MySQL

Links

7 links submitted so far. Submit your own links.

Read all project links...

News

Edit RSS feeds.

Do you need the InnoDB doublewrite buffer?

Posted about 2 hours ago by Mark Callaghan

Prior to updating pages in place, InnoDB writes the data to a sequential log. If there is a crash when the pages are updated in place the writes are replayed using the data from the sequential log. Note that this log is space reserved in the system ... [More] tablespace and is not the InnoDB transaction log. This guards against partial page writes (torn pages, fractured writes).

This feature has a cost. Is it needed? I am not an expert on Oracle, DB2 and SQL Server but I don't think they have anything equivalent to this. All of them can detect partial page writes by storing a checksum on each disk page or by storing a counter at the start and end of the page. But how do they recover from partial page writes?

The real problem that must be solved is recovery from anything that may cause page corruption on disk. There are many sources of corruption and I think that partial page writes are far from the most frequent. Oracle provides tools that can recover a page using flashback, archived backups and archived redo logs. Such a feature does not exist for InnoDB today.

How much work is it to build a tool to recover corrupt disk pages for InnoDB so that we can disable the doublewrite buffer? [Less]

Filtering by table is now possible with WaffleGrid

Posted about 8 hours ago by Yves Trudeau

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. ... [More] 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 = 2, caching around the disk IO, sbtest is in the filter list while sbtest_non_waffle is not. I really like mode 2, 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. [Less]

Helping The US Department Of Justice

Posted about 9 hours ago by Michael "Monty" Widenus

I was yesterday, for the second time, on a call with the US Department Of Justice regarding how the Oracle / Sun deal could affect Open Source software, in particular MySQL and Java.I told them that I still think that my original scenarios from April ... [More] are still valid. What has been worrying me lately is that Oracle has been quite vocal regarding their plans for most things related to the deal, like Sun hardware and Java, but has not said anything related to their plans regarding MySQL.During the MySQL conference and at other conferences afterwards I have been approached by numerous MySQL users that have been very worried about the future of MySQL. From this it's clear that most MySQL users are very interested to know what Oracle is up to, but those that have tried to inquire Oracle about this, myself included, have been met with complete silence.I strongly encourage Oracle to start talking publicly about their intentions regarding MySQL. If your plan is to continue developing MySQL as a true open source project and take it to new heights, I think it's critical to inform us, the MySQL community, about it ASAP. The more positive information we get, the more supportive we, the MySQL developers and users, can be about the deal.For those that are worried about the future of OSS software as part of the Oracle / Sun deal, and the affect (both good and bad) it may have on their business, the US Department of Justice is encouraging companies that are dependent on MySQL / Java to contact them and tell them how the deal may affect their business. The more information the department gets, the better equipped they will be in deciding what their recommendation for the deal will be.You can either contact the Department of Justice directly or send an email to me at 'info at askmonty dot org' and I will forward it to those in charge.We at Monty Program Ab and The Open Database alliance are doing our best to ensure MySQL's future survival as one of the leading open source databases. By making your voice heard, you can make all our lives easier! [Less]

Workbench loses its crutches

Posted about 9 hours ago by Scott Noyes

When MySQL Workbench was still in alpha and beta stages, some dark murmurs in the community suggested that the OSS (free) version was crippleware, part of an evil plot to make you pony up $99 if you wanted to get any real benefit. Closed source and ... [More] profits and bears, oh my!
The new GA release of Workbench gets some of those “crippled” features back. In particular, the OSS version now allows connections to a live server, so you can reverse and forward engineer without having to go through a dump file. I also have it on good authority that the OSS version will be able to print diagrams directly, instead of you having to export an image.
If you see any merit in a visual diagram of your database, but you wrote off Workbench based on earlier experiences, give it another try now. [Less]

Free Book Giveaway

Posted about 11 hours ago by Keith Murphy

Now that I have your attention, this is actually an update to my last post.  In about four hours I have an interview with Brian Aker for the new podcast I am putting together in conjunction with Open Source Database Magazine.  While I have enough ... [More] questions for a good interview, I am always looking for more possibilities. Yesterday I said I would randomly choose a name from people who emailed me or commented on the post with a question for Brian about Drizzle and the winner would receive a free copy of MySQL Administrator’s Bible. While I have received some response, it has not been overwhelming. So here is your last chance!! Send me an email (bmurphy AT paragon-cs.com) or comment on the post. Throw your hat in  ring.  As I said previously, because of shipping cost, I have to limit this to people in the United States.
Good luck!! [Less]

Read all MySQL articles…