MySQL Blogs

PlanetMySQL Voting: Vote UP / Vote DOWN

An annual tradition of the upcoming MySQL user conference is the awards ceremony. Last year we introduced the opportunity for everyone in the community to nominate candidates and this was a big success. Now is the time to start nominating deserving winners for the awards for 2012, in the 3 categories named below.

The winners will be selected by a community panel (see below) and winners will be announced on Wednesday, April 11th at the Santa Clara Convention Center, as part of the evening Community Reception.

How:

Please send in your suggestions for deserving winners
to: mysql.awards@gmail.com
no later than: 23:59 Sunday March 27th (Pacific time)

read more

PlanetMySQL Voting: Vote UP / Vote DOWN

Database blogging is more than about announcing that your last night’s backup was successful. While that’s extremely important to keep track of, the database world across Oracle, MySQL, and SQL Server technologies has grown way beyond. This Log Buffer Edition also extends beyond and more in this Log Buffer #258. Oracle: Laimis has produced a [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

I was searching around and was pleasantly surprised when I ran across this new MySQL Troubleshooting book by Sveta Smirnova:

“MySQL Troubleshooting – What To Do When Queries Don’t Work”

http://shop.oreilly.com/product/0636920021964.do

Having worked with Sveta in Support for years, I’m certain this book is chock-full of great troubleshooting techniques and advices (and you can get a good idea from the “Table of Contents” listed on the above page).

I’m always happy to see new MySQL-related books.

Congratulations, Sveta!

PlanetMySQL Voting: Vote UP / Vote DOWN

Here's a question that was driving me crazy: Why do these two explain plans look different?

explain select a from test order by b;
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | test | index | NULL | b | 5 | NULL | 3263769 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+

and this

explain select a,c from test order by b;
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 3263769 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+

The second query does a filesort, but the only change is adding another column to the SELECT clause!For reference, here is the table structure. As you can see, there's a primary key with auto increment, and a secondary key on b.

CREATE TABLE `test` (
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` varchar(32) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=3278870 DEFAULT CHARSET=latin1

You don't get the same behavior if you're ordering by the primary key value. So it seems there's a big difference when sorting if you use a secondary index.

 The explanation seems to be that (with innodb tables) if you order by a secondary index, and you need to read row data, then mysql has to go back to read the clustered index anyway, so it just ignores the secondary index and does a filesort.

Wow, that makes optimization of sorts much more difficult! What's the solution? I've heard that Multi-Range-Read in MySQL 5.6 will fix this, but I haven't tested that myself yet.For now, a query like this highlights the importance of covering indexes.

alter table test add index (b,c);

explain select a,c from test order by b;
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | test | index | NULL | b_2 | 40 | NULL | 3263685 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+

PlanetMySQL Voting: Vote UP / Vote DOWN

At yesterdays MySQL Tech Tour in Dallas, a gentleman asked how to not only partition data but also store the indexes on other disks as a way of reducing I/O contention on drives.That struck a chord with me and I was soon looking up the information in the MySQL manual. I remembered there was a way to do this with MySQL 5.6 but I am sure the small screen of my cell phone and the detail level of the documentation did not convery the information as well as I had wished.

The example I tried to show was from the subpartition section of the MySQL manual. If you read 12.1.17 for the CREATE TABLE syntax, you will see how to add DATA DIRECTORY and INDEX DIRECTORY definitions to a partition. I guess the example I remembered got stuck in my brain cache (such as it is) because 5.6 allows subpartitioning. This allows you to store a column in a partition by a RANGE and the subpartition by a HASH or a KEY.

CREATE TABLE t1 (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (2009) ( SUBPARTITION s0 DATA DIRECTORY = '/disk/a' INDEX DIRECTORY = '/disk/idx1', SUBPARTITION s1 DATA DIRECTORY = '/disk/b' INDEX DIRECTORY = '/disk/idx2' ), PARTITION p1 VALUES LESS THAN MAXVALUE ( SUBPARTITION s2 DATA DIRECTORY = '/disk/c' INDEX DIRECTORY = '/disk/idx3', SUBPARTITION s3 DATA DIRECTORY = '/disk/d' INDEX DIRECTORY = '/disk/idx4' ) );

A few others at the show asked about subpartitions and I promissed to blog about this.

And by the way, EXPLAIN PARTITION shows you how the optimizer wants to execute the query down to the subpartitions needed.

An example of EXPLAIN PARTITION on a subpartition

Make sure you create the directories for the DATA and INDEX entries and set the owner to mysql.

PlanetMySQL Voting: Vote UP / Vote DOWN

I recently came across a dev VM running MySQL 5.0.77 (an old release, 28 January 2009) that didn’t have InnoDB available. skip-innodb wasn’t set, SHOW VARIABLES LIKE '%innodb%' looked as expected, but with one exception: the value of have-innodb was DISABLED.

I confirmed this with SHOW ENGINES:

(root@localhost) [(none)]> show engines; +------------+----------+----------------------------------------------------------------+ | Engine | Support | Comment | +------------+----------+----------------------------------------------------------------+ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys | ...

(and chuckled to myself over the comment about MyISAM’s performance)

/var/log/mysqld.log yielded the answer:

120127 17:17:51 mysqld started 120127 17:17:51 [Warning] /usr/libexec/mysqld: ignoring option '--engine-condition-pushdown' due to invalid value 'InnoDB' InnoDB: Error: log file ./ib_logfile0 is of different size 0 20971520 bytes InnoDB: than specified in the .cnf file 0 104857600 bytes! 120127 17:17:51 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.77-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution

innodb_file_per_table was not set on this VM, and when the InnoDB log files had become corrupted and were not recognised, the engine couldn’t start. These ib_logfile files are the crash recovery logs:

The unsung heroes of InnoDB are the logfiles. They are what makes InnoDB automatic crash recovery possible.

Database administrators of other DBMS may be familiar with the concept of a “redo” log. When data is changed, affected data pages are changed in the innodb_buffer_pool. Then, the change is written to the redo log, which in MySQL is the InnoDB logfile (ib_logfile0 and ib_logfile1). The pages are marked as “dirty”, and eventually get flushed and written to disk.

If MySQL crashes, there may be data that is changed that has not been written to disk. Those data pages were marked as “dirty” in the innodb_buffer_pool, but after a MySQL crash the innodb_buffer_pool no longer exists. However, they were written to the redo log. On crash recovery, MySQL can read the redo log (InnoDB log files) and apply any changes that were not written to disk.

The solution is to move the logs and allow InnoDB to recreate them. Don’t delete them – you may need them if your server has crashed or in case of data loss.

/etc/init.d/mysql stop mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak /etc/init.d/mysql start

With the files absent, InnoDB recreates them and the engine is loaded successfully.

Of note: when this problem occurs, MySQL 5.0.77 erroneously returns this:

(root@some_host) [some_db]> SHOW ENGINE INNODB STATUS; ERROR 1235 (42000): Cannot call SHOW INNODB STATUS because skip-innodb is defined

PlanetMySQL Voting: Vote UP / Vote DOWN

The Oracle Linux and Virtualization Documentation Team is seeking an experienced Technical Writer
with a focus on writing documentation for the Oracle Linux product. (The MySQL Documentation Team is part of that group as well.)

Applicants should be located in either Ireland, the UK, Sweden, Norway, Denmark, or Finland (click on the links for a detailed job description).

We're a vastly distributed team, with writers in Australia, North America, and Europe. Our infrastructure is based on DocBook XML, and we're not just writing docs, but also maintain the whole processing and publication work chain.

Key competencies you should have include:

• 3 or more years previous experience in writing software documentation (please provide URLs of your writings I can look at!)
  
• Experience with writing documentation for system level software and operating systems
• Strong knowledge of the Linux operating system
• Strong knowledge of XML, DocBook XML, and XSL style sheets (and motivation to help maintain and expand our tools and infrastructure)
  
• Ability to administer own workstation and test environment
• Good experience with distributed working environments and versioning systems such as SVN

If this sounds like something for you, follow the links above and send in your application!

PlanetMySQL Voting: Vote UP / Vote DOWN

Similar to my other catch-all pages on Server Variables, Changelogs, and Documentation, I’ve added a new ‘Downloads’ section.

Basically, it’s just a single source containing links to all of your favorite MySQLs and related products, such as MySQL, MariaDB, XtraDB, Xtrabackup, MySQL Proxy, Connectors and more:

http://www.chriscalender.com/?page_id=831

Hope this helps.

 

PlanetMySQL Voting: Vote UP / Vote DOWN

I'm pleased to announce the release of Spider storage engine version 2.28(beta) and Vertical Partitioning storage engine version 0.17(beta).
Spider is a Storage Engine for database sharding.
http://spiderformysql.com/
Vertical Partitioning is a Storage Engine for vertical partitioning for a table.
http://launchpad.net/vpformysql

Please use the following for downloading binary file.
http://spiderformysql.com/download_spider.html

The main changes in this version are following.
Spider
- Support parallel searching. ("spider_bgs_mode > 0")
The table using the Spider bundled MySQL and table partitioning performs parallel search of each partition. However, parallel search is not performed in the case which does not make all the partitions applicable to search like sequential search with limit.
- Add server parameter "spider_use_default_database" and "spider_remote_default_database".
These parameter is added for using some replication parameters like "binlog-do-db" on data node. "spider_use_default_database=0" is same of previous versions.
- Add table parameter "access_balance".
This parameter is the weight of load balancing for Spider's redundancy feature.
- Add INFORMATION SCHEMA "spider_alloc_mem".
This INFORMATION SCHEMA plugin adds "information_schema.spider_alloc_mem" table for showing Spider's using memory status. "alloc_mem_count" and "free_mem_count" columns in "information_schema.spider_alloc_mem" table express the count of increasing and decreasing memory, so they do not necessarily become the same.
- Add value of 3 to "quick_mode"(table parameter) and "spider_quick_mode"(server parameter).
"quick_mode=3" is the mode of using temporary table for result set from data node. This mode is useful for searching a huge table.

Spider's management table was changed from previous version and add new plugin, please execute "install_spider.sql" for upgrading.

Vertical Partitioning
- Add UDF paramter "suppress_autoinc".
- Add table parameter "allow_bulk_autoinc" and "allow_different_column_type".
- Add server parameter "vp_allow_bulk_autoinc".

handlersocket
Becomes version 1.1.
- Add server parameter "handlersocket_slow_log", "handlersocket_long_exec_time" and "handlersocket_close_table_interval".
"handlersocket_slow_log" is logging handlersocket request with slow (spend over "handlersocket_long_exec_time" micro second) response into slow log with "slow_query_log=on".
"handlersocket_close_table_interval" is interval of closing table internally for releasing meta data lock for executing like "alter table". You can use this parameter "handlersocket_close_table_interval=0" (doesn't close) for normally and only change this parameter for executing like "alter table".
These parameters are global parameter and you can change them from SQL interface.

Please see "99_change_logs.txt" in the download documents for checking other changes.

Thanks to Takafumi, Yukihiro, Jung, Adrian, leechangyeol, liuyanhong, Hisazumi, Hideyuki, Keisuke for bug reporting.
Thanks to Akira for your advice.

Enjoy!
PlanetMySQL Voting: Vote UP / Vote DOWN

In a previous post, I had explained how to use MySQL's optimizer tracing, a new feature which appeared in MySQL 5.6.3.

As a developer, it feels really good to see others adopt my work and make something useful out of it! My colleague Dimitri Kravtchuk, who is one of our top Benchmarking experts, has written a blog post where he shows how the optimizer tracing has helped him to figure out why, under load, once in a while and randomly, a query performed badly. His investigation technique may be reusable by other people, so I encourage you to read more about it, here.
PlanetMySQL Voting: Vote UP / Vote DOWN

We are excited to announce that we’ve been named as one of ten finalists selected for the startup showcase at the O’Reilly Strata “Making Data Work” Conference at the end of this month in Santa Clara, California. The startup showcase will be held on February 29th, starting at 6:30 pm.

The conference offers a great overview of the big data space, with tracks on Data Science, Business and Industry, Visualization and Interfaces, Hadoop Applied, Hadoop Tech, Policy and Privacy, and Domain Data. With all of the “NoSQL” buzz and sessions at the show (Hadoop gets two tracks!), we are glad to be able to attend as a representative of the “NewSQL” community. We’ll be showing just how much MySQL, with the right storage engine, can scale to take on Big Data while giving up none of the power of ACID, familiar SQL interfaces, rich indexes, high insertion rates, and flexible schema.

If you will be there, please stop by to say hello! And please vote for us too (what can we say, it’s an election year all around).

PlanetMySQL Voting: Vote UP / Vote DOWN

PlanetMySQL Voting: Vote UP / Vote DOWN

PlanetMySQL Voting: Vote UP / Vote DOWN

After the recent update to pt-table-checksum, I’ve seen a few FAQs about it.

Q: is it still multi-threaded/parallel? A: No, that was a pile of bugs and complexity. If you need to run the tool in parallel to take advantage of powerful hardware, you can run several instances, say, one per database.

Q: what chunk size should I use? A: None, let the tool adjust itself dynamically.

Q: what if it skips a table or chunk because it’s oversized? A: this should be rare unless you have tables without any indexes; if you want to do the table in one chunk, run the tool again and specify to checksum only that table, with an appropriately large chunk size. This is one of the rare cases where you will need to specify a chunk size.

Q: what commandline options should I use after upgrading? A: It has sensible defaults for everything, and is designed to run without any options at all in most cases. If you’re upgrading from version 1, a few options are simply not available anymore, and most others should be removed unless you know you need them. In particular, remove the –chunk-size option and let it dynamically adapt to your server’s workload.

Hopefully that helps :)

Further Reading:

• MySQL Table Checksum 1.1.5 released
• Introducing MySQL Table Checksum
• MySQL Table Checksum 1.1.6 released
• MySQL Table Checksum 1.1.0 released
• How fast is MySQL Table Checksum?

PlanetMySQL Voting: Vote UP / Vote DOWN

Some administrative tasks can be simplified by using common_schema/QueryScript. I'm collecting a bunch of these for documentation. Here's one for example:

The DBA/developer has the task of retaining only top 3 most populated countries per continent. That is, she has to DELETE 4th, 5th, 6th, ... most populated counties in each continent.

Is it possible to work out with a single query? Yes. But the query is not pretty. In fact, it is quite complicated, and either involves unintuitive subqueries, or unintuitive hacks. A normal DBA would not want to write, neither maintain this kind of query, unless top-notch-geek, which is fine.

Since this is a one time job, we just need to get it done. And common_schema/QueryScript provide with the intuitive solution: if we read our demand aloud, we realize we want to delete 4th, 5th, 6th, ... populated countries for each continent.

I present a solution made available by QueryScript, and discuss the ways in which the code overcomes limitations, or simplifies complexity:

var $num_countries_to_delete; foreach($continent, $num_countries: SELECT continent, COUNT(*) FROM world.Country GROUP BY continent) { if ($num_countries > 3) { set $num_countries_to_delete := $num_countries - 3; DELETE FROM world.Country WHERE Continent = $continent ORDER BY Population ASC LIMIT :$num_countries_to_delete; } } Discussion

The first thing that should be apparent from the above is that this is a programmatic solution. Queries are declarative, which is why complex ones sometimes look incomprehensible. The above is more straightforward.

The next thing to realize, which is a disclosure issue of some sorts, is that the above code is fine for a one time, or maintenance execution; but you wouldn't want to be normally issuing this type of code against your database 10,000 times a second.

Now let's break down the code to fragments:

Discussion: variables

The $num_countries_to_delete is a script variable. It is local. It is reset to NULL upon declaration and destroyed when its visibility ends. But the real power comes later, when it is expanded. This is discussed last.

Discussion: iteration

How would you iterate the continents using a stored routine? I personally think the syntax for server side cursors is overwhelmingly verbose. Declare a cursor, declare a continue handler, declare variables to grab values, open the cursor, start a loop, iteratively fetch the cursor (assign row values onto variables), oh, check up on the continue handler (programmatically exit the loop if it fails), close the cursor.

The foreach() loop statement was developed to simplify all the above. Hey: just name your query, and the list of variables which should be assigned to, and do your thing in the following statement.

Discussion: conditional branching

The standard SQL CASE statement, and the additional IF() statement are fine, and I use them a lot. But they are fine for SELECT queries, and only allow you to get data. At best, you may invoke a function based on some condition, which can actually modify data.

With QueryScript it's as with your normal programming language: you can DELETE if some condition holds true, INSERT or SELECT or ALTER or whatever if false.

In the above code there isn't too much news. The same can be done with stored routines. However the if statement can also accept a query as a condition. One can ask: if (DELETE FROM ... WHERE...). The condition holds true only is the operation was successful (rows actually DELETEd, or INSERTed, or UPDATEed). This makes for a very tight integration between script and SQL.

Discussion: variables and variable expansion

Script variables behave just as normal MySQL user defined variables (in fact, current internal implementation of script variables is by user defined variables). Which means the set statement works for them just as normal.

And here is where things become not-normal:

Say we want to delete all but the 3 most populated countries in Europe. Wouldn't we like to issue a DELETE FROM Country WHERE Continent = 'Europe' ORDER BY Population DESC LIMIT 3, 999999999? (The 9999999999 to resemble "infinite", in poor man's solution)

But MySQL's DELETE does not accept both limit & offset in the LIMIT clause. Just the limit part. Which is why we're working the other way round: we find out the number of records we wish to purge and delete bottom up. But wait, here's another problem:

In MySQL, the LIMIT clause must accept a constant. You can just DELETE FROM .... LIMIT @x. This makes for a syntax error. Bummer!

If we don't know ahead the number of records we wish to purge, how can we work both dynamically and correctly?

Enter variable expansion. In the statement:

DELETE FROM world.Country WHERE Continent = $continent ORDER BY Population ASC LIMIT :$num_countries_to_delete;

The $num_countries_to_delete variable is expanded, via ":". The :$num_countries_to_delete token is replaced in-place with the value contained by $num_countries_to_delete. MySQL never gets a variable in the LIMIT clause: by the time the query reaches MySQL, theres a constant in place, and none is the wiser. But as far as we're concerned, we get a dynamic way of producing values to the LIMIT clause.

LIMIT is not the only clause which expects constants. How about KILL? How about DDLs, such as CREATE TABLE? With variable expansion you can dynamically inject values onto such clauses, statements and commands, and get your self a dynamic script.

Conclusion

This small code sample exposes much of QueryScript's power. Throughout the months of development, I happened to use QueryScript code over and over on production, to realize how it can sometimes simplify very complex tasks into a mere 2-liner code. A code that any of my fellow programmers can understand, as well, without having to be SQL experts. And such which is executed within the server; no need for external languages, connectors, dependencies, packages etc.

PlanetMySQL Voting: Vote UP / Vote DOWN

It might surprise you to hear this, but I had no idea whether my talks would be accepted. The committee decided on that, and neither I nor anyone else at Percona is on the committee. In any case, I’ll be giving some tutorials again this year, and two of my talks have been accepted: Measuring Scalability and Performance With TCP and Diagnosing intermittent performance problems.

This seems like an appropriate place to mention a few words about the conference organization. The number of people involved is staggering (100+). The logistics — the number of tasks, vendors, contracts, and so on — blows the mind. The upfront cost is literally unmentionable. It’s an exponentially bigger deal in every way than any of the conferences we’ve done before. There is no way to explain it to anyone who isn’t involved. I don’t even comprehend it myself.

Despite this, we are working hard to ensure that the traditions we’ve known and loved for years are continued. It turns out that they all have a very high cost in real dollars. We are constantly faced with hard decisions that always involve “how are we going to pay for this?” I wish that, for example, it could be simple and lightweight to organize BOFs. But there are unions and contracts and room minimums and overtime pay and bundled quotes and restrictions every which way. Nothing is simple, nothing is easy, nothing is cheap — regardless of how simple, easy, and cheap it should be.

And yet, there will be BOFs and dot-org booths and Drizzle Day and all the rest. And the ticket price is a lot less than it was last year. If you appreciate this, you can help by getting attendees to come. Please promote the conference to everyone you know, in every way you know. Use Twitter, LinkedIn, Facebook, everything. Blog about it. Mention it in-person. Ask your boss to send you and your colleagues, or send your team if you’re the boss. Send email to your meetups and user groups and mailing lists, and ask them to promote it to their networks too. Enough said.

Further Reading:

• There will be an O’Reilly MySQL Conference in April 2010
• I’ll be speaking at the O’Reilly MySQL Conference 2010
• Speaking at EdUI Conference 2009
• The MySQL Conference will be very good this year
• Postgres folks, consider the 2011 MySQL conference

PlanetMySQL Voting: Vote UP / Vote DOWN

Percona is glad to announce the release of Percona Server 5.5.20-24.1 on February 9th, 2012 (Downloads are available here and from the Percona Software Repositories).

Based on MySQL 5.5.20, including all the bug fixes in it, Percona Server 5.5.20-24.1 is now the current stable release in the 5.5 series. All of Percona ‘s software is open-source and free, all the details of the release can be found in the 5.5.20-24.1 milestone at Launchpad.

Full release notes available here: http://www.percona.com/doc/percona-server/5.5/release-notes/Percona-Server-5.5.20-24.1.html.
PlanetMySQL Voting: Vote UP / Vote DOWN

Good news to all of you who are going or were thinking of going to the Percona Live MySQL Conference and Expo. Yesterday two great addon events were announced, both happening on Friday April 13th, right after the main conference:

Drizzle Day 2012

read more

PlanetMySQL Voting: Vote UP / Vote DOWN

High availability is about more than making sure that apps can get to your data even if there is a failure. How about when you are upgrading your database schema? What if you need to add memory to a database server or reconfigure/restart MySQL? If your apps want to read data from a MySQL slave, how can you be sure they are not reading stale data without re-coding your apps? What if your main
PlanetMySQL Voting: Vote UP / Vote DOWN