-
New MySQL Troubleshooting Book
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!
-
Filesorts, Secondary Indexes and the Importance of Covering Indexes
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 thisexplain 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=latin1You 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 |+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
-
MySQL 5.6 Partitons and Subpartititions
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.
-
InnoDB disabled if ib_logfile files corrupted
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
-
Linux Documentation Writer Wanted!
The Oracle Linux and Virtualization Documentation Team is seeking an experienced Technical Writerwith 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!
|
Newsfeeds