… Permanent tables it is easy. We can look at information_schema to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where… is where your MySQL Support contract can be handy. Conclusion Understanding where MySQL can allocate memory can help us to find the cause…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table… In-memory workload Now let’s see how effective are the join optimizations when the… indirectly impact BKA performance. I did not find much of a performance improvement from using…
Post: Introducing new type of benchmark
February 25, 2012 By 16 Comments
…; NewOrder transactions Per Minute in tpcc-mysql). However, like Mark Callaghan mentioned in …TABLE, running in separate session, affect a response time of queries that handle user load ? The same for mysqldump, how…am looking for your comments, do you find it useful? Follow @VadimTk !function(d…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…_disk_tmp_tables, I’ll paste in mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table_size | 268435456 | | tmp_table_size…, including checking for default users in the mysql.* tables, running mk-duplicate-key-checker to find redundant indexes, and so on. As with…, and the desired modifications to the query or the table. We explain how to interpret what we’re showing, and why the…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
… a backup or the backup restore process doesn’t work. How can I recover deleted rows? We’re going to follow… get the table row format from the Information Schema: mysql (information_schema) > SELECT ROW_FORMAT from TABLES WHERE TABLE_SCHEMA=’employees’ AND TABLE_NAME=’salaries… Server has some extra tables in INFORMATION_SCHEMA that can help us to find the different indexes and types. mysql (information_schema) > select…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
…and you will not find it anywhere but in production. The pmp-check-mysql-deadlocks plugin can…pt-table-checksum tool from Percona Toolkit can generate table checksums, and the pmp-check-pt-table-checksum… has been restarted recently. It is surprising how often a server crashes, perhaps even cycling …
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 7 Comments
…’t much written about using the Closure Table design, I periodically get questions about how to solve certain problems. Here’s one… table ‘TreePaths’ for update in FROM clause.” We can’t DELETE and SELECT from the same table in a single query in MySQL. But we can use MySQL‘s multi-table DELETE syntax, to find any path that…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
…the manual is only partially correct in how to properly build the server to use … what kind of goodies does Helgrind find on MySQL server. Let’s take a …table_state) (sql_show.cc:6238) ==9090== by 0x6A5E0C: JOIN::exec() (sql_select.cc:1863) ==9090== by 0x6A7D72: mysql_select(THD*, Item***, TABLE…
Post: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
… enough, spin-offs from this work included being able to find out and then test that the ENUM limit of 65… of how the Storage Engine API is called. But back to what the above strace shows. In MySQL 5.5 you can find… the TABLES table, but didn’t look closely) An example of how this is used is that in the DROP TABLE code path, MySQL uses…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…perform some quality control, to verify consistency. How can we do that? Quality Control …relationships in any complex database. We can find all foreign keys by querying the INFORMATION_…control: mysql> CREATE TABLE test.MY_KEY_COLUMN_USAGE LIKE INFORMATION_SCHEMA.KEY_COLUMN_USAGE; mysql> INSERT…
