You searched for innodb hash join - Page 2 of 3

How well do your tables fit in buffer pool

March 26, 2010 By Peter Zaitsev 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.

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)

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 );

+-------------+---------------------+---------+------------+-------------+--------+

+-------------+---------------------+---------+------------+-------------+--------+

| 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% – [...]

Filed Under: MySQL, Percona Software Tagged With: InnoDB, XtraDB

Guidance for MySQL Optimizer Developers

September 20, 2009 By Peter Zaitsev 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 [...]

Filed Under: Insight for Developers, MySQL Tagged With: Optimizer

High-Performance Click Analysis with MySQL

December 22, 2008 By Baron Schwartz 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 [...]

Filed Under: Insight for Developers Tagged With: Ideas, Optimizer, Replication, Tips, XtraDB

Using Multiple Key Caches for MyISAM Scalability

November 26, 2008 By Peter Zaitsev 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 [...]

Filed Under: Insight for DBAs Tagged With: Production

The new cool MySQL patch has landed! Check your queries performance!

October 31, 2007 By Maciej Dobrzanski 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 [...]

Filed Under: Percona Software Tagged With: InnoDB, Patches, Replication

Heikki Tuuri Innodb answers – Part I

October 26, 2007 By Peter Zaitsev 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 [...]

Filed Under: Insight for Developers Tagged With: Community, InnoDB

Using CHAR keys for joins, how much is the overhead ?

June 18, 2007 By Peter Zaitsev 18 Comments

I prefer to use Integers for joins whenever possible and today I worked with client which used character keys, in my opinion without a big need. I told them this is suboptimal but was challenged with rightful question about the difference. I did not know so I decided to benchmark. The results below are for [...]

Filed Under: Insight for Developers Tagged With: Bugs, InnoDB, MyISAM, Tips

Watch out for Marketing benchmarks

August 23, 2006 By Peter Zaitsev 7 Comments

Whenever I see benchmark results I try to understand if it is technical benchmark – made by people seeking the truth or it is done by Marketing department to wash your brains. Watch out. Whenever you treat marketing benchmarks as technical ones, you make make wrong decision. Take a look at MySQL 5.0 Benchmarks Whitepaper [...]

Filed Under: Benchmarks

SHOW INNODB STATUS walk through

July 17, 2006 By Peter Zaitsev 40 Comments

Many people asked me to publish a walk through SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS output and how to use this info to improve MySQL Performance. To start with basics SHOW INNODB STATUS is command which prints out a lot of internal Innodb performance counters, statistics, information about [...]

Filed Under: Insight for DBAs Tagged With: InnoDB

Why MySQL could be slow with large tables ?

June 9, 2006 By Peter Zaitsev 141 Comments

If you’ve been reading enough database related forums, mailing lists or blogs you probably heard complains about MySQL being unable to handle more than 1.000.000 (or select any other number) rows by some of the users. On other hand it is well known with customers like Google, Yahoo, LiveJournal,Technocarati MySQL has installations with many billions [...]

Filed Under: Insight for DBAs Tagged With: Production, Tips

How well do your tables fit in buffer pool

Guidance for MySQL Optimizer Developers

High-Performance Click Analysis with MySQL

Using Multiple Key Caches for MyISAM Scalability

The new cool MySQL patch has landed! Check your queries performance!

Heikki Tuuri Innodb answers – Part I

Using CHAR keys for joins, how much is the overhead ?

Watch out for Marketing benchmarks

SHOW INNODB STATUS walk through

Why MySQL could be slow with large tables ?

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA