… are automatically picked up. Nice, eh? Usage of this tool is very straightforward …use for my Percona Server 5.6.10 sandbox: description=MySQL driver=com.mysql.jdbc.Driver connectionSpec=jdbc:mysql…/tables based on names, tables without indexes, columns flagged ‘nullable’ and ‘must be unique’ …
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
…The buffer pool and adaptive hash index on the standby server will…-playback –mysql-host 127.0.0.1 –mysql-username playback –mysql-password PaSSwOrd –mysql-schema …need Percona Server in order to use innodb_fake_changes. Thread pool…so the difference between the IO usage is 2 orders of magnitude….
Post: Announcing Percona XtraBackup 2.1.1 GA
May 15, 2013 By Leave a Comment
… features for MySQL backup. The new 2.1.1 GA version offers improved performance, enterprise-grade security, and lower resource usage. This… can be used to specify the number of threads started by XtraBackup when rebuilding secondary indexes on innobackupex –apply-log –rebuild-indexes. This…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…index_usage \ –create-save-results-database After that finishes, the ‘index_usage‘ database contains several tables: mysql> show tables; +———————–+ | Tables_in_index_usage | +———————–+ | index_alternatives | | index_usage | | indexes…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… can one have optimized indexes and sorting in such a scenario, since inequality ends index usage. Actually in this case index on (A,B… lot more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or… IN-ranges in some cases for better index usage. Q: Is the b+ tree innodb index a single or double linked list at…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… ways MySQL can use indexing, getting wrong ideas on what query performance they should expect. There are 3 main ways how MySQL can use the indexes… describes how MySQL uses single index – there are more complex rules of how indexes will be used if you look at multiple indexes usage with “index merge” Using Index to…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
…’t get index statistics usage from those tables. pt-index-usage The previous example only works with Percona Server so with stock MySQL we should… understand. pt-index-usage reads the slow query log and execute every query with EXPLAIN to ask MySQL which indexes would it use. At the end of the process you’ll have an output with a list of not used indexes…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…: mysql> create view used_indexes as (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats ) UNION DISTINCT (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from slave_index_stats) ORDER BY TABLE_SCHEMA, TABLE_NAME; Now I can query the ‘all_known_index_usage‘ and see…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…_note key_len: 4 ref: const rows: 10259274 Extra: Using where; Using index 1 row in set (0.00 sec) I reported this… means you need more manual care and tuning now. MySQL is conservative about “Using index” -in most cases it will be the right… to see better algorithms, statistics computation and monitoring variables about index usage in the future, now that we have more complex optimization…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… I use. Plot Memory Usage First I would like to see MySQL memory consumption plotted. I use “VSZ” columns…used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index…
