PlanetMysql.ru — информация о СУБД MySQL

This is the second and final part of my notes from the MySQL conference. In this part I'll focus on the technical substance of talks I saw, and didn't see.

More than ever before I was a contributor rather than attendee at this conference. Looking back, this resulted in seeing less talks than I would have wanted to, since I was speaking or preparing to speak myself. Sometimes it was worse than speaking, for instance I spent half a day picking up pewter goblets from an egnravings shop... (congratulations to all the winners again :-) Luckily, I can make up for some of that by going back and browse their slides. This is especially important whenever 2 good talks are scheduled in the same slot, or in the same slot when I was to speak. So I have categorized topics here along various axes, but also along the "things I did see" versus "things I missed" axis.

My own talks

Tutorial: Evaluating MySQL High Availability alternatives
Using and Benchmarking Galera in Different Architectures

read more

Have you ever wanted to get a list of indexes and their columns for all tables in a MySQL database without having to iterate over SHOW INDEXES FROM ‘[table]‘? Here are a couple ways…

The following query using the INFORMATION_SCHEMA STATISTICS table will work prior to MySQL GA 5.6 and Percona Server 5.5.

SELECT table_name AS `Table`,
       index_name AS `Index`,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE table_schema = 'sakila'
GROUP BY 1,2
ORDER BY 1,2;

This query uses the INNODB_SYS_TABLES, INNODB_SYS_INDEXES, and INNODB_SYS_FIELDS tables from INFORMATION_SCHEMA and is only available in MySQL 5.6 or Percona Server 5.5. However, it is much much faster than querying the STATISTICS table. It also only shows InnoDB tables.

SELECT t.name AS `Table`,
       i.name AS `Index`,
       GROUP_CONCAT(f.name ORDER BY f.pos) AS `Columns`
FROM information_schema.innodb_sys_tables t
JOIN information_schema.innodb_sys_indexes i USING (table_id)
JOIN information_schema.innodb_sys_fields f USING (index_id)
WHERE t.schema = 'sakila'
GROUP BY 1,2
ORDER BY 1,2;

Assuming that all your tables are InnoDB, both queries will produce identical results. If you have some MyISAM tables in there, only the first query will provide complete results.

+---------------+-----------------------------+--------------------------------------+
| Table         | Index                       | Columns                              |
+---------------+-----------------------------+--------------------------------------+
| actor         | idx_actor_last_name         | last_name                            |
| actor         | PRIMARY                     | actor_id                             |
| address       | idx_fk_city_id              | city_id                              |
| address       | PRIMARY                     | address_id                           |
| category      | PRIMARY                     | category_id                          |
| city          | idx_fk_country_id           | country_id                           |
...
| rental        | rental_date                 | rental_date,inventory_id,customer_id |
| staff         | idx_fk_address_id           | address_id                           |
| staff         | idx_fk_store_id             | store_id                             |
| staff         | PRIMARY                     | staff_id                             |
| store         | idx_fk_address_id           | address_id                           |
| store         | idx_unique_manager          | manager_staff_id                     |
| store         | PRIMARY                     | store_id                             |
+---------------+-----------------------------+--------------------------------------+
42 rows in set (0.04 sec)

Have you ever wanted to get a list of indexes and their columns for all tables in a MySQL database without having to iterate over SHOW INDEXES FROM ‘[table]‘? Here are a couple ways…

The following query using the INFORMATION_SCHEMA STATISTICS table will work prior to MySQL GA 5.6 and Percona Server 5.5.

SELECT table_name AS `Table`,
       index_name AS `Index`,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE table_schema = 'sakila'
GROUP BY 1,2
ORDER BY 1,2;

This query uses the INNODB_SYS_TABLES, INNODB_SYS_INDEXES, and INNODB_SYS_FIELDS tables from INFORMATION_SCHEMA and is only available in MySQL 5.6 or Percona Server 5.5. However, it is much much faster than querying the STATISTICS table. It also only shows InnoDB tables.

SELECT t.name AS `Table`,
       i.name AS `Index`,
       GROUP_CONCAT(f.name ORDER BY f.pos) AS `Columns`
FROM information_schema.innodb_sys_tables t
JOIN information_schema.innodb_sys_indexes i USING (table_id)
JOIN information_schema.innodb_sys_fields f USING (index_id)
WHERE t.schema = 'sakila'
GROUP BY 1,2
ORDER BY 1,2;

Assuming that all your tables are InnoDB, both queries will produce identical results. If you have some MyISAM tables in there, only the first query will provide complete results.

+---------------+-----------------------------+--------------------------------------+
| Table         | Index                       | Columns                              |
+---------------+-----------------------------+--------------------------------------+
| actor         | idx_actor_last_name         | last_name                            |
| actor         | PRIMARY                     | actor_id                             |
| address       | idx_fk_city_id              | city_id                              |
| address       | PRIMARY                     | address_id                           |
| category      | PRIMARY                     | category_id                          |
| city          | idx_fk_country_id           | country_id                           |
...
| rental        | rental_date                 | rental_date,inventory_id,customer_id |
| staff         | idx_fk_address_id           | address_id                           |
| staff         | idx_fk_store_id             | store_id                             |
| staff         | PRIMARY                     | staff_id                             |
| store         | idx_fk_address_id           | address_id                           |
| store         | idx_unique_manager          | manager_staff_id                     |
| store         | PRIMARY                     | store_id                             |
+---------------+-----------------------------+--------------------------------------+
42 rows in set (0.04 sec)

Quoting from Vagrant’s web site:

Vagrant is a tool for building and distributing virtualized development environments. By providing automated creation and provisioning of virtual machines using Oracle’s VirtualBox, Vagrant provides the tools to create and configure lightweight, reproducible, and portable virtual environments.

A complementary technology called Veewee makes building VirtualBox VMs easier by automating away a lot of manual steps. Marius Ducea has a great blog post on how to use it.

My observations:
1. According to Vagrant’s web site, it should work on Windows. I’ve tried that with unsatisficatory results. I think it works better on Linux and Mac;
2. Vagrant 0.9 was released recently, and is the version that I am using. However, 0.9 broke some backward compatibility such that a lot of examples on Vagrant configuration file in its web site no longer works. But it does provide good cenough error message for you to fix and adjust;
3. Just a couple of weeks ago, Veewee was not compatible with Vagrant 0.9, but that has since been fixed. I mention this because things appear to be a bit fluid, and you may need to roll up your sleeves and tweak a few things yourself to get what you want;
4. Using Veewee to build Windows VM is not as smooth as doing the same for Linux VMs. In fact a few of the templates do not have the Cygwin setup file that definition.rb refers to. Another drawback is that the initial VM harddisk size is too small (10 gig I believe). Darn it, I forgot where I changed the setting to make it bigger before building it. Share that with me if you know please
5. In addition, it seems hard to build a Multi-VM environment with Windows in the mix. It looks possible, though. Once again please share that with me if you know!

Use cases:
1. Hacking, learning, and development. It provides a safe environment to play around, build and compile things, learn new technologies, etc. For example, I’ve used it to test and play with Tengine and Nginx. I’ve also built a Multi-VM environment where I tested MariaDB 5.3 and the new Percona Tools, pt-table-checksum and pt-table-sync in particular. I think Vagrant is a great tool to learn Chef, Puppet, and Hadoop;
2. Testing: testing software on different distros, continuous integration testing for server software (web server such as Tengine, database server such as MariaDB and Percona Server). In fact, using a Centos 6.2 VM, I found Percona’s 5.5 RPM packages still haven’t resolved header file conflicts, an old problem.

Sample Multi-VM Vagrant file
It is really convinient to have a Multi-VM environment for development and testing. Vagrant can do that but there is a shortage of good samples, especially for Vagrant 0.9. Here is a sample that worked for me. Note that:
1. The VMs in the environment can be made from the same image. In the sample below, “debian” and “xiaosaier”‘s daddy is “debian6″. No joke intended;
2. Use “vagrant ssh debian”, “vagrant ssh centos”, and “vagrant ssh xiaosaier” to get to each VM. Each VM can talk to each other via the IP address defined. Therefore it can be used for good, realistic testing. After having good Chef recipe or Puppet files, the VMs can be made in a certain way, which can be used for Continuous Integration Testing.
3. The hostonly network IP address works very well. However, the VM takes the name of the image it is based on by default. It will be nice to be able to customize the VMs’ names. Let me know if you know how;

Vagrant::Config.run do |config|

config.vm.define :debian do |debian_config|
debian_config.vm.box = "debian6"
debian_config.vm.network :hostonly, "33.33.33.10"
end

config.vm.define :centos do |centos_config|
centos_config.vm.box = "centos62"
centos_config.vm.network :hostonly, "33.33.33.11"
end

config.vm.define iaosaier do |xiaosaier_config|
xiaosaier_config.vm.box = "debian6"
xiaosaier_config.vm.network :hostonly, "33.33.33.12"
end

PS. Note, the combination of colon and letter x turns into an emotion icon in the web page. Watch out!

I was doing some research on Percona Server, and came across this great tip by Baron: if you are using Oracle’s MySQL and want to test out and learn new/improved features that are present in Percona Server, you can just stop the mysqld instance, extract Percona Server binary from its rpm/deb package or tarball file, swap the binary, and do a successful restart. You can then do a test drive, kick the tires, learn and observe to your heart’s content. Swap the original mysqld back after you are done, as necessary.

I tested it and it worked great for me. Specifically, I did the following:
1. sudo /etc/init.d/mysql(d) stop
2. sudo cp /usr/sbin/mysqld /location/mysqldFromOracleOrWhatever
3. sudo cp /perconaBinaryDirectory/mysqld /usr/sbin/mysqld
4. sudo /etc/init.d/mysql(d) start
5. Test drive
6. Swap the original mysqld back as necessary

You can tell which binary you are using by reading the product and version information upon successful connection from mysql client. You can also do “SHOW VARIABLES” and tell by noticing additional settings that are present in Percona Server.

It turns out you can do the same with MariaDB as well. In fact, I tested that over the weekend successfully. You need one extra step though. If you simply followed the steps above, here is what you will get while mysqld is trying to start:

Starting MySQL. ERROR! The server quit without updating PID file (/var/lib/mysql/localhost.localdomain.pid).

And here is the relavant info in error log:

120213 5:12:33 [ERROR] Error message file '/usr/share/mysql/english/errmsg.sys' had only 722 error messages, but it should contain at least 930 error messages.
Check that the above file is the right version for this program!
120213 5:12:33 [ERROR] Aborting

You see, Percona Server is more or less Oracle MySQL, plus Percona patches for InnoDB storage engine plugin, known as XtraDB. MariaDB’s change, on the other hand, is more widespread and substantial. It certainly has changes to the InnoDB storage engine plugin. After all, both MariaDB and Percona Server use the same XtraDB default storage engine. On top of that, MariaDB has additional changes to the MySQL server itself, query optimizer and replication come to mind. So it is not surprising that MariaDB’s errmsg.sys is different. I hope that compatibality can be maintained as things move forward, which is probably not too hard to do technically. In a perfect world, I wish to be able to combine both MariaDB and Percona’s strength, but I digress.

So before we can test drive MariaDB, not only do we need to swap mysqld, we also need to swap errmsg.sys. It’s always a good idea to keep a backup before it is overwritten. After starting, if you run “SHOW VARIABLES LIKE ‘optimizer_%’, you will notice that MariaDB has a lot more optimizer knobs than Percona Server or Oracle MySQL.

This actually gives me some ideas for benchmark testing. What will we see if we conduct the same benchmark tests on the same server, with only mysqld swapped for each set of benchmark tests?

PS. This reminds me that years back, my team wrote a VB6 application that controled lamination film production process. I wrote a utility called AppLauncher that checked the version of the .exe binary and did a replace if the production version is different from the one in the deployment folder. That binary swap bypassed all unnecessary installation package creation, testing, and distribution, provided that there was no changes to the supporting DLL files.

Usually people do this around New Year, I will do it in February. Actually, I was inspired to do this after reviewing all the talks for this year's MySQL Conference - what a snapshot into the state of where we are! It made me realize we've made important progress in the past year, worth taking a moment to celebrate it. So here we go...

Diversification

read more

Usually people do this around New Year, I will do it in February. Actually, I was inspired to do this after reviewing all the talks for this year's MySQL Conference - what a snapshot into the state of where we are! It made me realize we've made important progress in the past year, worth taking a moment to celebrate it. So here we go...

Diversification

In the past few years there was a lot of fear and doubt about MySQL due to Oracle taking over the ownership. But if you ask me, I was more worried for MySQL because of MySQL itself. I've often said that if MySQL had been a healthy open source project - like the other 3 components in the LAMP stack - then most of the NoSQL technologies we've seen come about would never have been started as their own projects, because it would have been more natural to build those needs on top of MySQL. You could have had a key-value store (HandlerSocket, Memcache API...), sharding (Spider) and clustering (Galera). You could have had a graph storage engine (OQGraph engine isn't quite it, I understand it is internally an InnoDB table still?). There could even have been MapReduce functionality, although I do think the Hadoop ecosystem targets problems that actually are better solved without MySQL.

read more

Last week I had the opportunity to attend the Surge 2011 conference in Baltimore, MD.  I thought it was a great conference, and I’m already looking forward to next year.  I’m sure there’s already a plethora of great blog posts on Surge, but here’s just some thoughts based on my experience.

In no particular order:

EC2 has changed the world, everybody hates EC2

I don’t think I heard a presentation where somebody didn’t use EC2 and the other assorted AWS products.  Amazon (as far as I know) was not represented at the conference, and it seemed awkward for them to not be there (to me, at least).  This conference was full of folks rigging up serious production architectures in EC2, and they all seemed to have their own private horror story about something that EC2 did to them.  

I’m really puzzled by why some of EC2s competitors aren’t just as popular, or why with all the talk of HA and redundancy, people seemed to think it a weird idea to run a production stack on EC2 and another provider, as opposed to running Hot/Hot (for the few actually doing so) on multiple EC2 availability zones.  

The good part about how transient EC2 is?  Everybody cares about service resiliency now.

Technology goes through fads, but good Engineering practices are true regardless

We’re all partial to the technologies we know and love (or know and hate!).  I think the biggest reason is inertia:  once we get familiar with a tool, it’s natural to see how to apply it in various ways and get comfortable with its implementation.  

The diversity of technology being employed by presenters at Surge was impressive.  I fully expected the Etsy presentation to talk about how they moved away from the big bad RDBMS (Postgres) and into YourFavoriteNoSQLSolutionGoesHere.  However, they migrated from Python and Postgres to the ultra trendy: PHP and MySQL!  What is this, 2003?

Seriously, Ross Snyder gave a great presentation and really highlighted the struggles Etsy went through with the traditional silos built between Dev and Ops.  The underlying technology played second fiddle to those company dynamics: the end result seemed less tied to those particular technologies than getting the relationships right.

Postgres is a lot more popular than I thought

I have nothing against Postgres, I’ve just never had opportunity to use it.  Nor have I ever heard anyone talk about it, much less say they actually used it.  I guess I’ve just been living in a MySQL-only bubble.  

Folks are a lot more serious about Operations work than in the past

I haven’t been to Velocity yet, so maybe I’m missing out.  But it’s refreshing to see so much focus on Operability.  No longer are Operations folks the ogres in the closet, or maybe it’s just the case that the ogres are getting better organized. :)  In any case, I’m all for it.

MongoDB’s Replica set and sharding configurations is what I always wanted for MySQL

I can’t speak for Mongo’s scalability, reliability, client feature sets, or whatever.  But I can say that the talk given by Matthias Stearn about scaling Mongo turned out to be more about High Availability and it was impressive.  

Cross-WAN replica sets with client-configurable quorum write requirements, oh la-la!  Master election priorities, replica auto-cloning, SSL inter-replica communication, and auto shard re-balancing!  Response-time based client -> server and server -> server connection management, so stuff stays local including quorum reads/writes (up to the minimum quorum requirement) and slave recloning from the nearest up-to-date replica.  Beautiful.

The replica sets in Mongo seem to operate not unlike MySQL with Tungsten Replicator/Enterprise (with at least a non-empty subset of these features).  It’s super refreshing to see this kind of stuff baked in to the core of the datastore and I really want to see how it progresses to more feature-rich RDBMSs. 

MySQL Connector/Net 6.4.4, a new version of the all-managed .NET driver for MySQL has been released.  This is an update to our latest GA release and is intended for full production deployment.

Version 6.4.4 is intended for use with versions of MySQL from 5.0 – 5.5

It is now available in source and binary form from here and mirror sites (note that not all mirror sites may be up to date at this point of time- if you can’t find this version on some mirror, please try again later or choose another download site.)

The release is also available for download on the My Oracle Support (MOS) and will be available from Oracle eDelivery.

This release includes several bug fixes including a fix to using Windows authentication.  Please review the change log and documentation for a review of what changed.

Enjoy and thanks for the support!

Update: I won't be in Moscow after all. I was denied visa on grounds that my passport is beginning to fall apart and there wasn't time to get new passport, invitation and visa. Maybe next year - I was excited to go.

October brings 2 very interesting conferences. I will be speaking first on Oct 3rd at HighLoad++ in Moscow and a few weeks later on Oct Oct 25 at Percona Live in London. I will give a talk called Choosing a MySQL Replication / High Availability Solution which is based on my thinking developed in my recent blog post The ultimate MySQL high availability solution and many benchmarks and functional tests I've done while evaluating these technologies.

At Percona Live I will also give a second talk Fixed in Drizzle: No more GOTCHA's. It looked like none of the Drizzle core team would be able to attend the conference and as I was going to be there I volunteered to cover a Drizzle topic at the same time. This is a talk Stewart Smith has given a few times at earlier conferences which I liked and proposed to Percona. As it turns out, also Stewart will be in London after all, so there will be 2 Drizzle talks, I will still give the one I'm committed to.

read more