by Aurimas Mikalauskas | Nov 9, 2010 | Insight for DBAs, MySQL
Before I start a story about the data recovery case I worked on yesterday, here’s a quick tip – having a database backup does not mean you can restore from it. Always verify your backup can be used to restore the database! If not automatically, do this...
by Peter Zaitsev | Sep 2, 2010 | Insight for DBAs, MySQL
How long it may take MySQL with Innodb tables to shut down ? It can be quite a while. In default configuration innodb_fast_shutdown=ON the main job Innodb has to do to complete shutdown is flushing dirty buffers. The number of dirty buffers in the buffer pool varies...
by Baron Schwartz | Jul 31, 2010 | Insight for DBAs, MySQL
A couple of weeks ago one of my colleagues and I worked on a data corruption case that reminded me that sometimes people make unsafe assumptions without knowing it. This one involved SAN snapshotting that was unsafe. In a nutshell, the client used SAN block-level...
by Baron Schwartz | Jul 16, 2010 | MySQL, Percona Software
I recently did a quick analysis of the distribution of writes to InnoDB’s log files. On a high-traffic commodity MySQL server running Percona XtraDB for a gaming workload (mostly inserts to the “moves” table), I used strace to gather statistics about...
by Justin Swanhart | Jul 12, 2010 | Insight for Developers, MySQL
This is the first of a series of posts about business intelligence tools, particularly OLAP (or online analytical processing) tools using MySQL and other free open source software. OLAP tools are a part of the larger topic of business intelligence, a topic that has...
by Aleksandr Kuzminsky | Jul 1, 2010 | Insight for DBAs, MySQL
For a long time long types like BLOB, TEXT were not supported by Percona InnoDB Recovery Tool. The reason consists in a special way InnoDB stores BLOBs. An InnoDB table is stored in a clustered index called PRIMARY. It must exist even if a user hasn’t defined...
by Peter Zaitsev | Jun 10, 2010 | Insight for DBAs, MySQL
I just wrote a large post on reasons for innodb main tablespace excessive growth and I thought it would make sense to explain briefly of why it is so frequently you have purge not being the problem at all and when out of no where you can see purge thread being unable...
by Peter Zaitsev | Jun 10, 2010 | Insight for DBAs, MySQL
So you’re running MySQL With innodb_file_per_table option but your ibdata1 file which holds main (or system) tablespace have grown dramatically from its starting 10MB size. What could be the reason of this growth and what you can do about it ? There are few...
by Peter Zaitsev | Jun 8, 2010 | Insight for DBAs, MySQL
Quite frequently I see people confused what table locks reported by SHOW INNODB STATUS really mean. Check this out for example: ---TRANSACTION 0 4872, ACTIVE 32 sec, process no 7142, OS thread id 1141287232 2 lock struct(s), heap size 368 MySQL thread id 8, query id...
by Ryan Lowe | May 24, 2010 | Benchmarks, Insight for DBAs, MySQL
InnoDB has an oft-unused parameter innodb_concurrency_tickets that seems widely misunderstood. From the docs: “The number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable. A thread is placed in a queue when it...
by Peter Zaitsev | May 22, 2010 | Insight for DBAs, MySQL
One schema optimization we often do is extending index when there are queries which can use more key part. Typically this is safe operation, unless index length increases dramatically queries which can use index can also use prefix of the new index are they ? It turns...
by Peter Zaitsev | May 14, 2010 | Insight for DBAs, MySQL
Upgrading from MySQL 5.0 to MySQL 5.1 or Percona Server 5.1 you may run into issues with mysql_upgrade – it will identify some tables to be upgraded and will attempt to run REPAIR TABLE for them. This will fail with “The storage engine for the table...
by Peter Zaitsev | May 6, 2010 | Insight for DBAs, MySQL
The amount of memory Innodb will require for its data dictionary depends on amount of tables you have as well as number of fields and indexes. Innodb allocates this memory once table is accessed and keeps until server is shut down. In XtraDB we have an option to...
by Vadim Tkachenko | Apr 27, 2010 | Hardware and Storage, MySQL
As I mentioned in my talk An Overview of Flash Storage for Databases I see in the near and middle term future a lot of interest for using Flash storage in Level 2 caching level. The price-capacity trade-off makes Flash as the very good fit for a cache layer. Actually...
by Vadim Tkachenko | Apr 26, 2010 | MySQL, Percona Software
I did some drawing exercise and put XtraDB / InnoDB internals in Visio diagram: The XtraDB differences and main parameters are marked out. PDF version is there https://www.percona.com/docs/wiki/percona-xtradb:internals:start.
by Vadim Tkachenko | Apr 21, 2010 | Benchmarks, MySQL
MySQL-5.5.4 ® is the great release with performance improvements, let’s see how it performs in tpcc-like workload. The full details are on Wiki page https://www.percona.com/docs/wiki/benchmark:mysql:554-tpcc:start I took MySQL-5.5.4 with InnoDB-1.1,...
by Peter Zaitsev | Mar 26, 2010 | MySQL, Percona Software
In XtraDB we have the table INNODB_BUFFER_POOL_PAGES_INDEX which shows which pages belong to which indexes in which tables. Using thing information and standard TABLES table we can see how well different tables fit in buffer pool. SELECT d.*, ROUND(100 * cnt * 16384 /...
by Vadim Tkachenko | Mar 23, 2010 | MySQL
As soon as we get couple FusionIO cards, there is question how to join them in single space for database. FusionIO does not provide any mirroring/stripping solutions and totally relies on OS tools there. So for Linux we have software RAID and LVM, I tried to followup...
by Vadim Tkachenko | Mar 20, 2010 | MySQL, Percona Software
In Released and new coming features I did not mentioned two additional INFORMATION_SCHEMA tables available in XtraDB: It is INNODB_TABLE_STATS INNODB_INDEX_STATS These table show statistics about InnoDB tables ( taken from InnoDB data dictionary). INNODB_TABLE_STATS...
by Vadim Tkachenko | Feb 28, 2010 | Benchmarks, MySQL
I recently was asked what maximal amount transactions per second we can get using MySQL and XtraDB / InnoDB storage engine if we have high-end server. Good questions, though not easy to answer, as it depends on: – durability setting (...
