April 23, 2014

Percona Server on the Nexus 7: Your own MySQL Database Server on an Android Tablet

Following Roel’s post, Percona Server on the Raspberry Pi: Your own MySQL Database Server , I thought what other crazy gadget can I run Percona Server on? And having an Asus Nexus 7 Android tablet I decided to give it a try. Anything below contains a risk that you break your tablet if you do […]

How well does your table fits in innodb buffer pool ?

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 […]

Thinking about running OPTIMIZE on your Innodb Table ? Stop!

Innodb/XtraDB tables do benefit from being reorganized often. You can get data physically laid out in primary key order as well as get better feel for primary key and index pages and so using less space, it is just OPTIMIZE TABLE might not be best way to do it. If you’re running Innodb Plugin on […]

How well do your tables fit in buffer pool

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.

You can also see in one of the cases the value shown is a bit over 100% – […]

SHOW OPEN TABLES – what is in your table cache

One command, which few people realize exists is SHOW OPEN TABLES – it allows you to examine what tables do you have open right now:

Can you Trust CHECK TABLE ?

Take a look at this:

The sort order is obviously wrong while CHECK TABLE is not reporting any error

Should you name indexes while doing ALTER TABLE ?

MySQL Server does not require you to specify name of the index if you’re running ALTER TABLE statement – it is optional. Though what might be good practical reasons to specify the key name or omit ? Things what you should be looking at is how MySQL names indexes automatically as well as what maintaining […]

Researching your MySQL table sizes

I posted a simple INFORMATION_SCHEMA query to find largest tables last month and it got a good response. Today I needed little modifications to that query to look into few more aspects of data sizes so here it goes:

Database security: Why should you review yours?

Ah database security… the black sheep of topics and something you would really rather not have to deal with right? I mean surely all the fanfare and paranoia is reserved for the neck beards with tinfoil hats whom live in their own D.I.Y Faraday cage … that must be it … it just has to […]

Many-table joins in MySQL 5.6

I recently worked on an uncommon slow query: less than 100 rows were read and returned, the whole dataset was fitting in memory but the query took several seconds to run. Long story short: the query was a join involving 21 tables, running on MySQL 5.1. But by default MySQL 5.1 is not good at […]