Understanding how well your tables and indexes fit to buffer pool are often very helpful to understand why some queries are IO bound and others not – it may be because the tables and indexes they are accessing are not in cache, for example being washed away by other queries. MySQL Server does not provide [...]
Data mart or data warehouse?
July 15, 2010 By 13 Comments
This is part two in my six part series on business intelligence, with a focus on OLAP analysis. Part 1 – Intro to OLAP Identifying the differences between a data warehouse and a data mart. (this post) Introduction to MDX and the kind of SQL which a ROLAP tool must generate to answer those queries. [...]
How well do your tables fit in buffer pool
March 26, 2010 By 4 Comments
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | SELECT d.*, ROUND(100 * cnt * 16384 / ( data_length + index_length ), 2) fit FROM (SELECT schema_name, table_name, COUNT(*) cnt, SUM(dirty), SUM(hashed) FROM INNODB_BUFFER_POOL_PAGES_INDEX GROUP BY schema_name, table_name ORDER BY cnt DESC LIMIT 20) d JOIN TABLES ON ( TABLES.table_schema = d.schema_name AND TABLES.table_name = d.table_name ); +-------------+---------------------+---------+------------+-------------+--------+ | schema_name | table_name | cnt | sum(dirty) | sum(hashed) | fit | +-------------+---------------------+---------+------------+-------------+--------+ | db | table1 | 1699133 | 13296 | 385841 | 87.49 | | db | table2 | 1173272 | 17399 | 11099 | 98.42 | | db | table3 | 916641 | 7849 | 15316 | 94.77 | | db | table4 | 86999 | 1555 | 75554 | 87.42 | | db | table5 | 32701 | 7997 | 30082 | 91.61 | | db | table6 | 31990 | 4495 | 25681 | 102.97 | | db | table7 | 1 | 0 | 0 | 100.00 | +-------------+---------------------+---------+------------+-------------+--------+ 7 rows in set (26.45 sec) |
You can also see in one of the cases the value shown is a bit over 100% – [...]
Guidance for MySQL Optimizer Developers
September 20, 2009 By 4 Comments
I spend large portion of my life working on MySQL Performance Optimization and so MySQL Optimizer is quite important to me. For probably last 10 years I chased first Monty and later Igor with Optimizer complains and suggestions. Here are some general ideas which I think can help to make optimizer in MySQL, MariaDB or [...]
High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
We have a lot of customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, and many similar types of work. The first thing these have in common is that they’re generally some kind of loggable event. The next characteristic of a lot of these systems (real or planned) is [...]
Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
I have written before – MyISAM Does Not Scale, or it does quite well – two main things stopping you is table locks and global mutex on the KeyCache. Table Locks are not the issue for Read Only workload and write intensive workloads can be dealt with by using with many tables but Key Cache [...]
Unused indexes by single query
September 12, 2008 By 14 Comments
Usually unused indexes are devil, they waste diskspace, cache, they make INSERT / DELETE / UPDATE operations slower and what makes them worse – it is hard to find them. But now ( with userstatsV2.patch) you can find all unused indexes (since last restart of mysqld) by single query
1 2 3 4 5 6 | SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics INDXS ON (s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA AND s.TABLE_NAME=INDXS.TABLE_NAME AND s.INDEX_NAME=INDXS.INDEX_NAME) WHERE INDXS.TABLE_SCHEMA IS NULL; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | +--------------+---------------------------+-----------------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | +--------------+---------------------------+-----------------+ | art100 | article100 | ext_key | | art100 | article100 | site_id | | art100 | article100 | hash | | art100 | article100 | forum_id_2 | | art100 | article100 | published | | art100 | article100 | inserted | | art100 | article100 | site_id_2 | | art100 | author100 | PRIMARY | | art100 | author100 | site_id | ... +--------------+---------------------------+-----------------+ 1150 rows in set (1 min 44.23 sec) |
As you see query [...]
TPC-H Run on MySQL 5.1 and 6.0
April 10, 2008 By 16 Comments
We were doing MySQL Performance evaluation on TPC-H queries for the client and they kindly allowed us to publish results which are very interesting. This is obviously not audited TPC-H run, and it can’t be because we used MyISAM tables which are not ACID complaint. Plus we only measured Power to keep things simple. We [...]
The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
Microslow patch is used by many DBAs and developers to accurately time their queries and to catch those which run less than a second as they can also be a performance killer for a busy application. Recently I have started the development of an updated version of the patch. The basic idea is the same [...]
Heikki Tuuri Innodb answers – Part I
October 26, 2007 By 19 Comments
Its almost a month since I promised Heikki Tuuri to answer Innodb Questions. Heikki is a busy man so I got answers to only some of the questions but as people still poking me about this I decided to publish the answers I have so far. Plus we may get some interesting follow up questions [...]
