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

Mark Riddoch of SkySQL.

This was a talk about the future in general. What people would like. Etc. Not about something that exists yet, hence the sparse notes.

Trace statements are good for the “why”. You move on to the debugger, but what is a useful SQL debugger? Profilers addresses the “when”.

SQL developer tools: manually run queries (traditional route for development, effective way to test SQL statements, some indication of performance), server logs (alerts developers of serious issues like the slow query log – identify poorly written queries, requires server access, not effective in a cloud environment (?)), external monitoring (network sniffing of connection packets, Ethereal dissectors – allow individual connections to be traced, no server access, privileged network access, complex to interpret, possibility of packet loss), intrusive tools (insert “proxy” between client & server to intercept all traffic, imposes delay & requires setup modification).

Client-side tools – client modification or hook via connectors (query logging, profiling). The Java connector has profiling. Should there be a connector slow query log? So there’s no requirement for server access and its per client rather than per server.

Plans: roll out connector query logs for Java, C, and scripting languages built on Connector/C. SQL Parser for report generation/fuzzy matching. Create post-processing tools. Query log comparisons – detect fluctuation in execution time, plan flip alerting, replay log.

Related posts:

1. Open source tools to run a small-medium sized business
2. Leaving Summer of Code applications to the last minute?
3. Google Native Client & Chrome about:flags

PlanetMySQL Voting: Vote UP / Vote DOWN

Ralf Gebhardt of SkySQL.

Cluster: shared nothing architecture (no single point of failure), synchronous replication between nodes, ACID transactions, row level locking. In-memory storage (some data can be stored on disk, but indexes must be in-memory). Checkpointing to disk for durability. It supports two types of indexes – ordered T-trees, unique hash indexes. Online operations like adding node groups, software upgrades, table alterations. Quick standard architecture diagram displayed about MySQL Cluster.

Network partitioning protocol is designed to avoid a split brain scenario. Is there at least one node from each node group? If not then this part cannot continue – graceful shutdown. Are all nodes present from any node group? If so, then this is the only viable cluster – continue to operate. Ask the arbitrator – the arbitrator which parts will continue if no arbitrator is available the cluster shuts down.

Durability – in order for a node to recover fast some data is stored locally. The REDO log is synchronized by global checkpoints (GCP). The DataMemory is synchronized by local checkpoints (LCP).

I agree with Ralf — almost impossible to talk about NDB in 25 minutes. Its very deep, you’d need at least three hours to grasp it well.

Related posts:

1. Gong-A-Thong at LugRadio Live USA
2. Morning sessions at MySQL MiniConf
3. MySQL at Google

PlanetMySQL Voting: Vote UP / Vote DOWN

Giuseppe Maxia of Continuent.

MySQL replication is single threaded. Multi-master replication is complex with MySQL. Circular replication works but is very fragile. Once you’ve achieved the feat, how do you avoid conflicts? The lack of global transaction ID today also means you may have slaves that may not be synchronized fully. Finally, some people like to replicate to PostgreSQL, Oracle and MongoDB.

This is where Tungsten Replicator comes into place. Opensource. 100% GPLv2. You can do easy failover (no need to synchronize the slaves manually when a master dies), have multiple masters, multiple sources to a single slave, conflict prevention, parallel replication, and replicate to Oracle/PostgreSQL database (heterogeneous replication – Oracle->MySQL is not opensource, but MySQL->Oracle is).

Parallel replication: ability to replicate with multiple thread at once. Sharded by database (all big sites already use this naturally). Good choice for slave lag problems. Bad choice for single database projects. In their tests to measure slave catch up time between standard MySQL & Tungsten slave with 1hr of sysbench and some 130GB of data, MySQL replication takes 4.5h to catchup, and Tungsten takes less than 1 hour to catchup. This comparison was not made with MySQL 5.6 (the same parallel replication feature at this moment in 5.6 is quite buggy). No need to install Tungsten on the master (Tungsten needs to be on slave). Replication can revert to native salve with 2 commands, but the failover is an issue — so install Tungsten on both!

Conflict prevention. When you have multiple masters you are tempted to do many things that you shouldn’t. Tungsten provides the multi-master topology. Tungsten can help you avoid conflicts. Decide you want to shard your information by database – Tungsten can enforce such rules. Define the rules, applied to either the master or the slave and you can then state to make replication fail or drop silently or drop with a warning.

You can create clusters very quickly with Tungsten. You can install from a centralized point to many servers. You need Java, Ruby, SSH access and a MySQL user with all privileges (used for replication).

Related posts:

1. MySQL HA reloaded by Ivan Zoratti
2. Ticketmaster thrives on MySQL Replication
3. Federation at Flickr: A tour of the Flickr Architecture

PlanetMySQL Voting: Vote UP / Vote DOWN

First talk at FOSDEM MySQL Devroom by Oli Sennhauser, of FromDual. Quick notes/liveblog of the talk, plus links from a quick search.

New Release Model: starts with at least in beta quality, milestone releases are RC quality (every 3-6 months), between milestones new features are allowed, GA releases every 12-18 months, no more than 2 releases in active support. There is also MySQL Labs, in where features can make it into a release model, but not necessarily.

Oli’s guess: MySQL 5.6 GA in April 2012 (Collaborate) or June 2012. As a consequence, MySQL 5.0 and MySQL 5.1 will be EOL probably by April this year.

New improvements in partitioning: explicit partition selection, exchanging partitions (good for ETL jobs).

New improvements in InnoDB: InnoDB INFORMATION_SCHEMA has got some new entries. Buffer (INNODB_BUFFER), Fulltext (INNODB_FT), Metrics (INNODB_METRICS), Data Dictionary (INNODB_SYS). The InnoDB Monitor is now obsolete. innodb_purge_threads can be set > 1. Kernel mutex split which should lead to improved concurrency. Persistent optimizer statistics (since InnoDB used to do random dives, so when you do a mysqldump, things can be different) – you can do SET GLOBAL innodb_analyze_is_persistent = 1; (read more: InnoDB Persistent Statistics at last, InnoDB Persistent Statistics Save the Day).

New improvements in optimizer: ORDER by on non-indexed columns, Multi Range Read (MRR), Index Condition Pushdown (ICP), query execution plan for DML statements, Batched Key Access (BKA). There is also optimizer tracing.

There is now some new instrumentations in Performance Schema (introduced in MySQL 5.5). There is also improved replication features, but that will be covered in a later talk.

Fractional seconds (microseconds) is introduced in MySQL 5.6. GET DIAGNOSTICS for stored procedures. Pluggable authentication (socket). memcached/InnoDB still in labs, hasn’t made it to a milestone release. Some 400+ bugs have been fixed. Lots of cleanup’s (old unused variables, commands removed).

Pay attention to MySQL 5.6 incompatible changes when you plan to upgrade.

Probably some good resources: What’s New in MySQL 5.6, MySQL 5.6 (Early Access Features) – InnoDB & Replication.

Related posts:

1. MariaDB/MySQL users in Paris & Brussels
2. Ticketmaster thrives on MySQL Replication
3. Some MySQL-related links

PlanetMySQL Voting: Vote UP / Vote DOWN

First talk at FOSDEM MySQL Devroom by Oli Sennhauser, of FromDual. Quick notes/liveblog of the talk, plus links from a quick search.

New Release Model: starts with at least in beta quality, milestone releases are RC quality (every 3-6 months), between milestones new features are allowed, GA releases every 12-18 months, no more than 2 releases in active support. There is also MySQL Labs, in where features can make it into a release model, but not necessarily.

Oli’s guess: MySQL 5.6 GA in April 2012 (Collaborate) or June 2012. As a consequence, MySQL 5.0 and MySQL 5.1 will be EOL probably by April this year.

New improvements in partitioning: explicit partition selection, exchanging partitions (good for ETL jobs).

New improvements in InnoDB: InnoDB INFORMATION_SCHEMA has got some new entries. Buffer (INNODB_BUFFER), Fulltext (INNODB_FT), Metrics (INNODB_METRICS), Data Dictionary (INNODB_SYS). The InnoDB Monitor is now obsolete. innodb_purge_threads can be set > 1. Kernel mutex split which should lead to improved concurrency. Persistent optimizer statistics (since InnoDB used to do random dives, so when you do a mysqldump, things can be different) – you can do SET GLOBAL innodb_analyze_is_persistent = 1; (read more: InnoDB Persistent Statistics at last, InnoDB Persistent Statistics Save the Day).

New improvements in optimizer: ORDER by on non-indexed columns, Multi Range Read (MRR), Index Condition Pushdown (ICP), query execution plan for DML statements, Batched Key Access (BKA). There is also optimizer tracing.

There is now some new instrumentations in Performance Schema (introduced in MySQL 5.5). There is also improved replication features, but that will be covered in a later talk.

Fractional seconds (microseconds) is introduced in MySQL 5.6. GET DIAGNOSTICS for stored procedures. Pluggable authentication (socket). memcached/InnoDB still in labs, hasn’t made it to a milestone release. Some 400+ bugs have been fixed. Lots of cleanup’s (old unused variables, commands removed).

Pay attention to MySQL 5.6 incompatible changes when you plan to upgrade.

Probably some good resources: What’s New in MySQL 5.6, MySQL 5.6 (Early Access Features) – InnoDB & Replication.

Related posts:

1. MariaDB/MySQL users in Paris & Brussels
2. Ticketmaster thrives on MySQL Replication
3. Some MySQL-related links

PlanetMySQL Voting: Vote UP / Vote DOWN

I’m about to head to Paris to present at the February meetup of the MySQL User Group in Paris, France. It happens 1st February from 6-8pm at the Patricks Irish Pub. Its free to attend, and I understand that SkySQL keeps this event afloat.

I’m also heading to my first FOSDEM right afterwards and will definitely hang out at the MySQL & Friends Devroom. There is an amazing lineup of speakers, with all talks being about 25-30 minutes, it looks like it is going to be a lot of fun. To boot, Michael “Monty” Widenius will also be there, so expect lots of Salmiakkikossu.

If you want to keep track of where Monty Program folk are going to be to talk about MariaDB, make sure you’re subscribed to our news page, which also includes important release information. Pretty much every conference that we plan to attend (and have attended) is at the conference page.

I am looking forward to meeting & learning from many MariaDB/MySQL users!

Related posts:

1. Using MariaDB in production?
2. OpenSUSE users have a choice of database now!
3. Plugins & Storage Engines Summit for MySQL/MariaDB

PlanetMySQL Voting: Vote UP / Vote DOWN

As many of you know, the schedule had been set for FOSDEM.

Giuseppe had posted the full scehdule here:

http://datacharmer.blogspot.com/2012/01/mysql-and-friends-schedule-at-fosdem.html

However, I refreshed the FOSDEM list (provided at fosdem.org) and noticed some changes to the schedule late last week.

http://fosdem.org/2012/schedule/track/mysql_and_friends_devroom

Specifically, I noticed new talks by Oli Sennhauser ("New Features in MySQL 5.6"), Alexey Yurchenko ("Galera 2.0"), Steve Hardy ("Optimizing your innodb buffer pool usage"), Liz van Dijk ("Virtualized Databases"), and Stéphane Combaudon ("Practical indexing guidelines").

(And all of the previous folks (Ralf Gebhardt, Stéphane Varoqui, Ivan Zoratti, Sergey Petrunya, Vladimir Fedorkov, Giuseppe Maxia, Mark Riddoch, Kenny Gryp, and Frédéric Descamps) still have talks too.)

So an already very impressive lineup has been improved even more! Kudos! :)

PlanetMySQL Voting: Vote UP / Vote DOWN

As many of you know, the schedule had been set for FOSDEM.

Giuseppe had posted the full scehdule here:

http://datacharmer.blogspot.com/2012/01/mysql-and-friends-schedule-at-fosdem.html

However, I refreshed the FOSDEM list (provided at fosdem.org) and noticed some changes to the schedule late last week.

http://fosdem.org/2012/schedule/track/mysql_and_friends_devroom

Specifically, I noticed new talks by Oli Sennhauser ("New Features in MySQL 5.6"), Alexey Yurchenko ("Galera 2.0"), Steve Hardy ("Optimizing your innodb buffer pool usage"), Liz van Dijk ("Virtualized Databases"), and Stéphane Combaudon ("Practical indexing guidelines").

(And all of the previous folks (Ralf Gebhardt, Stéphane Varoqui, Ivan Zoratti, Sergey Petrunya, Vladimir Fedorkov, Giuseppe Maxia, Mark Riddoch, Kenny Gryp, and Frédéric Descamps) still have talks too.)

So an already very impressive lineup has been improved even more! Kudos! :)

PlanetMySQL Voting: Vote UP / Vote DOWN

The MySQL DevRoom at FOSDEM is ready. The schedule has been voted. Thanks to all who have participated. Now, let's make sure that the event is successful. The schedule is juicy, and not only because I have three talks in it!

Sunday 2012-02-05
Event	Speaker	Room	When
All you need to know about migrations and you never dared to ask	Ralf Gebhardt	H.1309	09:05-09:30
Sphinx User stories	Stéphane Varoqui	H.1309	09:35-10:00
MySQL HA reloaded - old tricks and cool new tools to guarantee high availability to your MySQL Servers	Ivan Zoratti	H.1309	10:00-10:25
MariaDB 5.3's query optimizer: taking the dolphin to where he's never been before	Sergey Petrunya	H.1309	10:30-10:55
How to offload MySQL server with Sphinx	Vladimir Fedorkov	H.1309	11:00-11:25
** Build simple and complex replication clusters with Tungsten Replicator	Giuseppe Maxia	H.1309	11:30-11:55
Cluster internals	Ralf Gebhardt	H.1309	12:00-12:25
Optimising SQL applications by using client side tools	Mark Riddoch	H.1309	12:30-12:55
** MySQL Replication 101	Giuseppe Maxia	H.1309	13:00-13:25
Choosing Hardware for MySQL	Kenny Gryp	H.1309	13:30-13:55
Replication features of 2011: what they were, how to get and how to use them	Sergey Petrunya	H.1309	14:00-14:25
** MySQL creatively in a sandbox	Giuseppe Maxia	H.1309	14:30-14:55
Case Study: La Poste - Real Time, High Volume Data Warehousing Using MySQL & InfiniDB	Stéphane Varoqui	H.1309	15:00-15:25
Sphinx performance top secret	Vladimir Fedorkov	H.1309	15:30-15:55
Managing MySQL with Percona Toolkit	Frédéric Descamps	H.1309	16:00-16:25
Data Warehousing with MySQL	Ivan Zoratti	H.1309	16:30-16:55

UPDATE The schedule has changed. Speakers with more than one talk have been asked to give up one. Now I have two talks instead of three.

PlanetMySQL Voting: Vote UP / Vote DOWN

There is a room dedicated to MySQL at FOSDEM 2012. (Thanks to @lefred for organizing).The CfP has received 37 submissions, but there will be time slots only for 12 to 15 talks. So now it's up to the community. If you want to attend a particular talk, you should vote for it.Like in previous years, the selection of the talks is public. You can see the list of the proposals, with the instructions, which I repeat here.You can vote either publicly, using Twitter, or privately, by sending an email. Each talk proposal will be referred by the number immediately after the title in this page. This number indicates the order in which the proposals were received. In public, you should send a tweet to @opensqlcamp, indicating a maximum of 12 talks that you would like to see, in the order you like them. e.g. "@opensqlcamp #FOSDEM2012 1,2,3,4,5,6,7,8,9,10,11,12 http://bit.ly/mysql_fosdem_2012" (adding the link will help others to find the page.In private, by email at mysqlfriends AT gmail DOT com, using the same method used for Twitter. Maximum 12 talks, in the order of your preference.In both cases, votes for your preferences will result in 1 point for each talk. In case of equal voting, we will assign 12 points to the first in the list, 11 to the second, and so on. We'll do the tally, and choose the most popular ones.Anonymous votes either by Twitter or email won't be counted. If you want your vote to count, make sure your twitter account has a recognized name (or known nick) on it. If your email address doesn't spell your name, please sign the message with your real one.DEADLINE: Your votes must be entered by January 8th, 2011.

PlanetMySQL Voting: Vote UP / Vote DOWN