Sheeri Dot Org

Liveblogging: Extending MySQL by Brian “Krow” Aker

Brian wins the award for “most frequent great quotes during a talk”.

Before MySQL 5.1 a UDF was the only way to extend MySQL.

All you need in a UDF is: init() execute() deinit()

my_bool id3_parse_init(UDF_INIT *initid UDF_ARGS *args, char *message)

UDF_ARGS tell you about incoming args
char *message is the output that might return
args->arg_count is the # of args

WARNING: use STRICT mode in MySQL, otherwise there are tons of silent failures.

“When you work on databases you start to put everything in databases. Tip, don’t put a DVD into a database, because really long BLOBs aren’t actually supported….”

In MySQL 5.1, you can now install plugins (example is memcache_servers plugin):

mysql> INSTALL PLUGIN memcache_servers SONAME 'libmemcache_engine.so'

In the plugin architecture, there’s a callback mechanism to call a structure and then the structure is self-hosted into the environment. mysql_declare_plugin() is usually found at the bottom of the file because that’s how Brian wrote the example….

A storage engine is really about connecting to anything.

MySQL has a parser, and optimizer, and a retrieve/store part, which talks to the storage engines. The engine interface is written in C++.

Quote about the BLACKHOLE storage engine: “It’s amazing how much money can be made out of something that does NOTHING.”

What about defining features?

tables_flags()
index_flags()
handlerton flags

Table object methods:

base_ext()
create()
delete_table()
rename_table()
optimize(), repair(), etc.

As you can see, the API is pretty specific to actions that are happening at the SQL layer.

Reads
rnd_init() — scan is about to begin
rnd_next() — get the next record
(rnd = random or read next data)
index_first() — index read is about to begin
index_next() — get the next record in the index

Writes:
write_row() — “here’s new data, please store this”.
delete_row()
update_row()

on delete or update, MySQL starts reading the rows and once the predicate matches, then the delete_row() or update_row() is called. update_row() will be passed the before and after image of the row.

What about information_schema?

The information_schema API is an easy way to get more information into the information_schema tables.

(there’s a really nice example of an information_schema plugin)

A quote about his code — “That in the middle there looks like a bug….but it’s never called, so that’s OK. That’s what I get for copying and pasting my own examples.”

The plugin is responsible for all the locking.

Daemon plugins created because otherwise people would “bastardize the storage engine plugins.”

A daemon plugin has full access to the server internals, and it is a simple interface to be able to extend.

“If you’re really going to shoot yourself in the foot, you really want to go full blast.”

UDP Daemon just allows UDP packets to be put into the db (instead of TCP/IP with its darn authentication schema). Simplest interface to MySQL that exists. No connector, just UDP. The point is you can put other listeners to MySQL, such as an HTTP listener.

Daemon plugins are also a way to

code for examples
skeleton engine

my_bool id3_parse_init(UDF_INIT *initid UDF_ARGS *args, char *message)

In 3 words:

They already do.

MySQL Enterprise is more than just a binary. http://mysql.com/products/enterprise/ has the details on the other features MySQL Enterprise includes.

One of these features is the MySQL Enterprise Monitor, which is closed source, proprietary alerting software.

So when bloggers make statements such as:

MySQL will start offering some features (specifically ones related to online backups) only in MySQL Enterprise. This represents a substantive change to their development model — previously they have been developing features in both MySQL Community and MySQL Enterprise. However, with a shift to offering some features only in MySQL Enterprise, this means a shift to development of those features occurring (and thus code being tested) only in MySQL Enterprise.

source

All they are doing is showing their lack of knowledge about what MySQL Enterprise really is. Complaining that MySQL will likely charge for online backups is a valid one, but currently InnoDB Hot Backup is for sale only. There are solutions out there that do not cost anything, but nothing that MySQL produces. The only free [mostly] online backup product is mysqlhotcopy, and that has limitations such as “for MyISAM tables only”.

While I have said that software should be open source and free, where “free” means “free as in water”, that does not mean that we should take it for granted that free software will always be there. The path to that is to have a contrast of software one pays for, and software one does not.

Of course, that is not why MySQL charges for the software. They charge for some of their products because without money, they cease to exist, and then even the free version stops being developed on.

“A necessary evil” is a dangerous phrase, and can lead to a slippery slope….but in this case, I do not envision that it is a big problem. Maybe I will regret saying this if things keep getting more and more closed, but I do not believe so.

And I, too, am frustrated that we have been told that “online backups are coming” only to find out that they may not come in the means I expected. However, software is not bug-free, often does not come out on time (MySQL 5.1 itself is a year late!), and sometimes, promises get broken.

My faith in MySQL is unbroken, and I hope most others reading this feel this way as well.

Architecture of Maria: A New Storage Engine with a Transactional Design

Goals of Maria:

• ACID compliant
  
• MVCC, transactional
  
• default non-transactional and default transactional storage engine for MySQL
  
• MyISAM replacement, including temporary table use
  
• Storage engine good for data warehousing.
  
• Allow any length transactions to take place
  
• all indexes should have equal speed (clustered indexes are not in the plan)
  
• log shipping — incremental backups just by copying the logs
  
• used as a standalone library
  
• fast count(*)
  
• allow copying of Maria tables between different Maria servers
  
• Better blob handling (than MyISAM) — no memory copying, or extra memory used for blobs on INSERT/UPDATE
  
• BLOBs are allocated in big sequential blocks means that there is less fragmentation over time.
  
• BLOBs are stored so Maria can be easily extended to have access to any part of a BLOB with a single fetch in the future

Maria is actually faster than MyISAM.
Will support READ COMMITTED and REPEATABLE READ (was a specific question).

“You should be able to upgrade Maria and have it just work, without ever having to backup and restore as part of the upgrade.”

Why do we need 2 storage engines, Maria and Falcon?
More storage engines = more scenarios to be able to handle. Falcon good for lots of memory, and shorter queries, Maria is to take care of long transactions and data warehousing side, and also to be an overall “good” engine for most purposes (at least as good as InnoDB).

Project plan — MySQL 5.1 and higher. Maria strives to be bug-free.

Maria 1.0 = “crash safe”, released in Jan 2008. Cacheable row format.
Maria 1.5 = “concurrent insert/select”, Apr/May 2008. Will be merged into the MySQL 6.0 release. Non-transactional.
Supports:

• External/internal row CHECK/REPAIR/compressing
  
• Different row formats — fixed size, dynamic length, compressed, page
  
• Fast checksum of a table
  
• maria_check
  

MySQL 6.0 (includes Maria)
Maria 2.0 = “Transactional and ACID” alpha, Q4 2008
Maria 3.0 = “High concurrency + Online backup” alpha, Q1 2009; GA, Q2 2009

Advantages of Maria compared to MyISAM

• Data, index and statistics are crash safe. On crash, everything will rollback to the state of the start of the statement or last LOCK TABLES commands.
  
• Can replay everything from the log. Still need to backup the .frm files though.
  
• LOAD INDEX can skip index blocks for unwanted indexes
  
• most parts of Maria have unit tests.
  
• Supports all MyISAM formats and a new PAGE format, where data is stored in pages — this is:
  
  
  
  • Only crashsafe, transactional row format for Maria
    
  • Versioned, so you can have concurrent insert/select
    
  • Cached by page cache so will give noticeable speed improvement on systems with poor data caching
    
  
  
• MyISAM is non-transactional only, Maria can support transactional (crash-safe) and non-transactional formats
  
• Maria uses big log files by default — 1G
  
• Log control file = maria_log_control
  
• Log files = maria_log.?????
  
• If a log file is already in use by a transaction, create a new one, otherwise, reuse it. SHOW LOGS will show which logfiles exist, which ones are in use, and you can PURGE the logs.
  
• Maria uses 8K pages by default (MyISAM uses 1K pages by default. This should be faster on static size indexes but slower on variable length keys, until Maria 2.0 where there will be a directory for index pages.
  

Before Apr/May release of Maria 1.5

• Statistics and indexes versioned (data is already versioned)
  
• FULLTEXT (boolean mode) and R-TREE indexes need to be made crash-safe
  
• Parallel recovery
  

Disadvantages, not likely to be fixed

• No external locking
  
• One page size for both index and data
  
• only one page cache
  
• index number and checksums require an extra 5 bytes per page
  
• doesn’t support RAID
  
• Minimum data file size for BLOCK format is 16K, with 8K pages
  
• Storage of rows < 25 bytes are not efficient for the PAGE format
  

XDB indexes — group records, and get the min and max for each group (groups are not sorted), and save that in a separate block. This means that stats like min and max are very fast. This is something that will help data warehousing queries.

Resources:

• Maria worklog (tasks to do)
  
• 5.1 Storage Engine documentation — note that Maria docs have not made it to the manual yet.
  
• http://monty-says.blogspot.com
  
• Download Maria
  
• FAQ and answers about Maria
  
• Maria Documentation on the MySQL Forge.
  

Best quote from Monty: “You don’t steal ideas in open source. You respect people.”

Here are my liveblogging notes from MySQL Sandbox: Easily Using Multiple Database Servers in Isolation by Giuseppe Maxia

Giuseppe has been a community member since 2001, and in the past year or so, a MySQL Employee.

He likes to give things away for free — he gave away T-shirts to the early arrivers to the workshop, and that’s why he’s giving away the sandbox as well. The sandbox is NOT an official MySQL product. It is released from GPL, available from http://sf.net/projects/mysql-sandbox.

Why the sandbox? To be able to set up 1 server in under 10 seconds. And to be able to set up multiple MySQL instances very quickly, and to use them quickly.

The sandbox untars in seconds, for installing alternative servers, not main instance, it creates a separated environment (datadir, port, sockets) — for groups of related or unrelated servers. Really good for testing out new server versions. WARNING: If you do not use separated environment (separate datadirs, ports and sockets), you can corrupt your data.

Doing it manually (the old, hard way):
unpack tarball, move to separate directory, create db tables, create .cnf with port, socket, datadir, launch mysqld_safe manually, launch mysql commandline script with options.

OK for doing it once, but a good DBA will automate this if they’re doing this a lot, to avoid mistakes — for example, while doing QA to test several versions.

So the easy automated way — MySQL Sandbox!
Just provide the version # and it creates $HOME/VER/data, VER, /tmp/mysql_VER.sock
Two examples:

Version 5.1.24
datadir = $HOME/5_1_24/data
port 5124
socket = /tmp/mysql_5124.sock

Version 6.0.5
$HOME/6_0_5/data
port 6005
socket = /tmp/mysql_6005.sock

(NOTE: you can have multiple instances of the same version)

Once installed:
In the case of a single sandbox — commands are start, stop and clear (removes all data and files in the datadir except for the mysql system db and tables), and use (instead of typing mysql -S /path/to/socket -u user -p you can use the use shell command and it will read what it needs from the my_sandbox.cnf file).

In the case of multiple sandbox, you can use the same commands as with a single instance, but there are commands that can affect all the instances. These are start_all, stop_all, clear_all, and multi_cmd. The first three have the obvious results; multi_cmd command executes command for all nodes — so you do not have to call it on each node. For example:
multi_cmd "select * from test.t1"

The easy way to install — download the package from Sourceforge. The sandbox doesn’t contain MySQL build, so you have to download a tarball or compile one yourself.

To install a single instance of MySQL 5.1.23:
express_install.pl /path/mysql-OS-5.1.23.tar.gz

Instead of using express_install.pl, to install 1 master and 2 slaves, run:
set_replication.pl /path/mysql-OS-5.1.23.tar.gz

To install multiple servers of the same version at the same time:
set_many.pl /path/mysql-OS-5.1.23.tar.gz
This installs 3 instances by default, but you can specify how many you want with options to set_many.pl.

For multiple servers of different versions, either:

Download the tarballs and run set_custom_many.pl /path/to/mysql-OS-5.0.51.tar.gz /path/to/mysql-OS-5.1.23.tar.gz /path/to/mysql-OS-6.0.5.tar.gz.
or:
Expand the tarballs in $HOME/opt/mysql and run set_custom_many.pl 5.0.51 5.1.23 6.0.5

Fine tuning – express_install.pl /path/mysql-OS-5.1.23.tar.gz [option]… can customize port, datadir, enable federated tables, disable innodb, skip networking, and so on.

You can pick your default my.cnf “size” with the my_file option:
express_install.pl /path/mysql-OS-5.1.23.tar.gz --my_file=small
express_install.pl /path/mysql-OS-5.1.23.tar.gz --my_file=medium
express_install.pl /path/mysql-OS-5.1.23.tar.gz --my_file=large
express_install.pl /path/mysql-OS-5.1.23.tar.gz --my_file=huge
These will use the my_small.cnf, my_medium.cnf, my_large.cnf or my_huge.cnf sample config files bundled with MySQL.

To easily fine tune the 50+ options in the sandbox, you can run
express_install.pl /path/mysql-OS-5.1.23.tar.gz --interactive
to ask you the values for each value — if you want to skip the rest of the questions while you are in the interactive mode and continue the install using default values for the rest of the questions, you can type “default” at any prompt. You can also enter “back” at any prompt to go back to the previous question; or enter “quit” at any prompt to quit the interactive server without completing the sandbox installation.

The use shell command starts the mysql client, using the credentials in my_sandbox.cnf. By the way, the default username/password = msandbox/msandbox, default root password = msandbox

There are shortcuts for using mysqldump, mysqlbinlog and mysqladmin for each instance in a sandbox. These shortcuts start the mysql client using the credentials in my_sandbox.cnf.
my sqldump
my sqlbinlog
my sqladmin

Using a multi-instance sandbox
start_all starts the master, then slaves. stop_all stops the slaves and then the master. clear_all clears all the slaves and then the master.

multi_cmd was already mentioned to run the same command on all the instances. However, there are different commands to run a command on a single instance of a multi-instance sandbox. Instead of the use command, the shell commands to use the multiple instances are:

Replication sandbox
m to use the master
s1 to use the first slave
s2 to use the second slave

Multi-node sandbox
n1 to use the first node
n2 to use the second node
n3 to use the third node

Version 5.1.24
datadir = $HOME/5_1_24/data
port 5124
socket = /tmp/mysql_5124.sock

Version 6.0.5
$HOME/6_0_5/data
port 6005
socket = /tmp/mysql_6005.sock

Yesterday I presented “Best Practices for Database Administrators” at the MySQL User Conference and Expo. I was successful in streaming the live video on www.ustream.tv, and you can see it in totality at http://www.ustream.tv/recorded/352479.

The slides are available in pdf form. And here are some of the links I spoke about:

cacti
nagios
rt

MySQL 5.0 manual
explain
show variables
show status
data types
procedure analyse()
mysqldumpslow’s manual page says “Use mysqldumpslow –help to see the options that this command supports.”

mysqlsla
mysqlreport
Maatkit, contains mk-query-profiler and mk-table-sync

mytop
innotop