'MyISAM' Tag

• Getting rid of huge ibdata file, no dump required, part II

  May 30, 2012

  This post continues Getting rid of huge ibdata file, no dump required, part I, where I describe way of converting your single-tablespace InnoDB database into a file-per-table one, without the pain of exporting and importing everything at once. In previous part we put aside the issue of foreign keys. We address this issue now. What [...]

  posted in MySQL by shlomi

• Getting rid of huge ibdata file, no dump required

  May 22, 2012

  You have been told (guilty as charged), that the only way to get rid of the huge InnoDB tablespace file (commonly named ibdata1), when moving to innodb_file_per_table, is to do a logical dump of your data, completely erase everything, then import the dump. To quickly reiterate, you can only delete the ibdata1 file when no [...]

  posted in MySQL by shlomi

• Auto caching tables

  March 6, 2012

  Is there a way to create a caching table, some sort of a materialized view, such that upon selecting from that table, its data is validated/invalidated? Hint: yes. But to elaborate the point: say I have some table data_table. Can I rewrite all my queries which access data_table to read from some autocache_data_table, but have [...]

  posted in MySQL by shlomi

• Self throttling MySQL queries

  November 1, 2011

  Recap on the problem: A query takes a long time to complete. During this time it makes for a lot of I/O. Query's I/O overloads the db, making for other queries run slow. I introduce the notion of self-throttling queries: queries that go to sleep, by themselves, throughout the runtime. The sleep period means the [...]

  posted in MySQL by shlomi

• Limiting table disk quota in MySQL

  March 7, 2011

  Question asked by a student: is there a way to limit a table's quote on disk? Say, limit a table to 2GB, after which it will refuse to grow? Note that the requirement is that rows are never DELETEd. The table must simply refuse to be updated once it reaches a certain size. There is [...]

  posted in MySQL by shlomi

• Personal observation: more migrations from MyISAM to InnoDB

  June 16, 2010

  I'm evidencing an increase in the planning, confidence & execution for MyISAM to InnoDB migration. How much can a single consultant observe? I agree Oracle should not go to PR based on my experience. But I find that: More companies are now familiar with InnoDB than there used to. More companies are interested in migration [...]

  posted in MySQL by shlomi

• A MyISAM backup is blocking as read-only, including mysqldump backup

  May 18, 2010

  Actually this is (almost) all I wanted to say. This is intentionally posted with all related keywords in title, in the hope that a related search on Google will result with this post on first page. I'm just still encountering companies who use MyISAM as their storage engine and are unaware that their nightly backup [...]

  posted in MySQL by shlomi

• The depth of an index: primer

  April 9, 2009

  InnoDB and MyISAM use B+ and B trees for indexes (InnoDB also has internal hash index). In both these structures, the depth of the index is an important factor. When looking for an indexed row, a search is made on the index, from root to leaves. Assuming the index is not in memory, the depth [...]

  posted in MySQL by shlomi

• LOCK TABLES in MyISAM is NOT a poor man's tranactions substitute

  March 18, 2009

  I get to hear that a lot: that LOCK TABLES with MyISAM is some sort of replacement for transactions; some model we can work with which gives us 'transactional flavor'. It isn't, and here's why. When we speak of a transactional database/engine, we check out its ACID compliance. Let's break out the ACID and see [...]

  posted in MySQL by shlomi

• MySQL User Group Meetings in Israel

  March 11, 2009

  This is a short note that the MySQL User Group Meetings in Israel are established (well, re-established after a very long period). Thanks to Eddy Resnick from Sun Microsystems Israel who has set up the meetings. So far, we've had 2 successful meetings, and we intend to have more! First one was in Sun's offices [...]

  posted in MySQL by shlomi