Grep My Mind Wonder what’s on my mind? Now you know.

Posted under Events, Internet, Technology, Tips & Tricks, Web Development, mysql

MySQL Proxy is one of the new products that MySQL released this year that has some real promise. In the MySQL Proxy, the Friendly Man in the Middle session Jan Kneschke went over the existing and planned features of the proxy.

MySQL Proxy has a c-based event driven core that has command line support, event handling and a Lua-based scripting layer. The proxy is loading into MySQL via the plugin interface. With the community edition, you are responsible for writing your own scripts. But with MySQL Enterprise, it will come with several pre-built scripts that add some enhanced functionality.

The most basic use of MySQL Proxy is to rewrite or alter your queries. If you have a costly query that is killing your db, but you’re not sure where it is coming from, you can write a Lua script to either reject the query or to alter it and make it more manageable. It stops the dba from having to wait for a developer to update code and/or deploy the fix.

Another trick you can do with MySQL Proxy is to create new SQL commands. While this is probably not going to be a great idea, you can see a few interesting examples at http://thenoyes.com/littlenoise/?p=63. It is a good way to expand on functionality at the lowest level. Or to lose/forget where you put it when you switch to another db.
Read More…

Posted under Events, mysql

Wednesday afternoon Monty went through the Architecture of Maria, what the new features are and what its roadmap will be. For those of you who are not aware of Maria, it is a new db engine that Monty announced back in January.

Maria was designed to be a crash-safe replacement for MyISAM. Maria supports the same row formats and features as MyISAM. This means it can be fully compatible with MyISAM by simply running it in non-transactional control. It will be ACID compliant and have multi-version concurrency control (MVCC).

Read More…

Posted under Events, mysql

The Applied Partitioning and Scaling Your Database System session by Phil Hildebrand appears to be a very popular session. The line outside the door was more of a crowd than a line. The talk was about the new partitioning abilities of MySQL 5.1.

The new table partitioning system has several benefits for your large datasets where you are having performance issues due to the volume of data. It can reduce the seek & scan set sizes and reduce the INSERT/UPDATE transaction durations. If you are already performing UNION queries or secondary queries to find data in an archive table, this functionality may be for you.

There are four (point 5) partitioning styles available for your use:

1. Range
   • Very useful for situations where the data is accessed by date
   • Usually used when a limited number of primary partitions are needed
   • It supports sub-partitions (the .5 partition type)
2. List
   • Good for when you are grouping data in partitions out of order (e.g., SELECT 1,5,7 in partition X)
   • As the name implies, you list which data goes into which partition
   • Higher maintenance costs in many situations
   • Supports sub-partitions
3. Hash
   • Usually has a lower maintenance cost
   • Partitions the data based on the hash that you tell it
   • Good for when you have non-intelligent keys
   • Works with both a limited and a large number of Partitions
4. Key
   • Similar to Hash partitioning, but based on one or more of the fields in the primary key instead of a user-defined hash
   • If there is no primary key, then the first unique index is used
   • Does not require the key to be integer based
5. Subpartitions
   • As the name implies, a partition within a partition
   • You can only create subpartitions for Range & List
   • If needed, subpartitions can be spread across volumes

The alter table statements are fairly simplistic; “ALTER TABLE my_table PARTITION BY KEY() PARTITIONS 50″ will create 50 partitions for that table.  If you need to add more partitions, you can just run “ALTER TABLE my_table ADD PARTITION PARTITIONS 25″ and your table will now have 75 partitions.  Of course you can shrink the size of the partitions as well.  One of the nice features of the partition modification is that the data is re-allocated as appropriate.

All in all, this looks like a much better solution than the standard 10/90 tables.

Posted under Events, mysql

This morning’s first session is hosted by Giuseppe Maxia about MySQL Sandbox. This tool was created so that you can quickly & easily setup sandbox servers for testing MySQL. It installs side servers on a machine that is already running MySQL. Sandbox installs quickly without disturbing the existing MySQL installation.

The home for the MySQL Sandbox project is hosted on SourceForge.net. It is not an officially supported product which is why Giuseppe keeps it on SourceForge.

The Sandbox was designed so that you don’t have to go through the hassle, and potentially error prone, steps of setting up multiple sandbox databases. It’s very common for these manual installations to accidentally install in the same data directory (can corrupt your data) or install with the same port/socket (MySQL won’t start).

Details of installing and using after the break… Read More…

Posted under Events, mysql