Featured
Percona Products
Percona Services
Use Cases
Peter ZaitsevI’m often asked how one can evaluate IO subsystem (Hard drive RAID or SAN) performance for MySQL needs so I’ve decided to write some simple steps you can take to get a good feeling about it, it is not perfect but usually can tell you quite a lot of what you should expect from the […]
Implementing MySQL database in 24/7 environments we typically hope for uniform component performance, or at least would like to be able to control it. Typically this is indeed the case, for example CPU will perform with same performance day and night (unless system management software decides to lower CPU frequency due to overheating). This is […]
I got first Sun/MySQL Newsletter Today which among other things lead to the site publishing among other things links to various stuff related to Sun and MySQL and among other things – These Benchmarks This may be great piece of Benchmarks for Sales and Marketing needs as they show sun stuff is so much cooler […]
I never liked how build in WordPress search works. it shows full documents rather than snippets it does not search comments and it does not have any query language so I always used Google search if I wanted to find something on MySQL Performance Blog. Today we have published new search functionality for our site […]
As some of you surely know already I’m moving back to USA, so does Vadim. From very beginning we had much more business from US than from rest of the world combined so this is a great step in company development having more comfortable time zones for our customers (without having to work nights) as […]
Probably most of MySQL users sometimes had a situation when they would do changes in MySQL config file without restarting the server (maybe matching SET GLOBAL command to do it in run time). This could be because it is misspelled or because the given version does not support such option, the server when would refuse […]
Preparing to move I’m selling stuff on GumTree which is UK based clone of Craigslist offering similar functionality but with Ads 🙂 Similarly to Craigslist GumTree is powered by MySQL but unlike craigslist it looks like they are shy in tuning their MySQL Full Text Search index configuration and setting proper ft_min_word_len value. Searching for […]
Sergey Petrunia brought to my attention the work Optimizer Team has done in MySQL 6.0 related to SubQuery Optimization. I am excited to see this information published honestly outlining the things which are fixed now and things which are still not handled well. According to the bug sampling done by Optimizer Team 68% of reported […]
Finding the largest tables on a MySQL instance is a no brainer in MySQL 5.0+ thanks to Information Schema, but I still wanted to post a little query I use for the purpose so I can easily find it later. Plus it is quite handy in a way it presents information:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SELECT CONCAT(table_schema, '.', table_name), CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows, CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size, ROUND(index_length / data_length, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10; +-------------------------------------+--------+--------+--------+------------+---------+ | concat(table_schema,'.',table_name) | rows | data | idx | total_size | idxfrac | +-------------------------------------+--------+--------+--------+------------+---------+ | art87.link_out87 | 37.25M | 14.83G | 14.17G | 29.00G | 0.96 | | art87.article87 | 12.67M | 15.83G | 4.79G | 20.62G | 0.30 | | art116.article116 | 10.49M | 12.52G | 3.65G | 16.18G | 0.29 | | art84.article84 | 10.10M | 10.11G | 3.59G | 13.70G | 0.35 | | art104.link_out104 | 23.66M | 6.63G | 6.55G | 13.18G | 0.99 | | art118.article118 | 7.06M | 10.49G | 2.68G | 13.17G | 0.26 | | art106.article106 | 9.86M | 10.19G | 2.76G | 12.95G | 0.27 | | art85.article85 | 6.20M | 9.82G | 2.51G | 12.33G | 0.26 | | art91.article91 | 8.66M | 9.17G | 2.66G | 11.83G | 0.29 | | art94.article94 | 5.21M | 10.10G | 1.69G | 11.79G | 0.17 | +-------------------------------------+--------+--------+--------+------------+---------+ 10 rows in set (2 min 29.19 sec) |
I do some […]
Guess what ? I also will be speaking at MySQL Users Conference 2008, which is always excited. According to session schedule I’ll have one talk about Innodb Scalability Limits and another one together with Andrew Aksenoff about Sphinx. Looking back at Proposals (+1) I’m surprised only one talk was chosen and also this particular one. […]
One performance gotcha with MEMORY tables you might know about comes from the fact it is the only MySQL storage engine which defaults to HASH index type by default, instead of BTREE which makes indexes unusable for prefix matches or range lookups. This is however not performance gotcha I’m going to write about. There is […]
As MySQL Manual Says Query Cache works with transactions with Innodb tables but it does not tell you how and with which restrictions. According to my tests it works but it is very restricted and one could expect it to work much better: The result set can be retrieved from query cache (for statements both […]
This weekend we’re hearing great news from Michael “Monty” Widenius – one of the Fathers of MySQL. Monty finally found a time to create his own blog with very descriptive name Monty Says. At the same time Monty finally announces Maria – the MyISAM successor storage engine he has been working for last few years. […]
As you might know even if you’re only using Innodb tables your replication is not completely crash safe – if Slave MySQL Server crashes/power goes down it is likely for relay logs to run out of sync (they are not synced to the disk) plus position on the master which slave remembers becomes stale. During […]
Kaj wrote me email yesterday asking if I was sleeping for a whole week or may be dead because I’m probably the only one of people blogging about MySQL who has not commented about announced Sun – MySQL Deal. In fact I was just on extremely busy travel schedule last week, so I’m just finding […]
When you’re storing text of significant size in the table it often makes sense to keep it compressed. Unfortunately MySQL does not provide compressed BLOB/TEXT columns (I would really love to have COMPRESSED attribute for the BLOB/TEXT columns which would make them transparently compressed) but you well can do it yourself by using COMPRESS/UNCOMPRESS functions […]
As I already wrote you can well go to Linux distribution vendors if you’re looking for recent MySQL version in a hassle free binary form. (On the time of this writing you could only get binaries for MySQL Community 5.0.45 from MySQL Download Pages which was released in July and so about half a year […]
In MySQL 4.1 and above the longest part of recovery after crash for Innodb tables could be UNDO stage – it was happening in foreground and was basically unbound – if you have large enough transaction which needed to be undone this could take long hours. REDO stage on other hand always could be regulated […]
MySQL Storage Engines implementing Multi Version Concurrency Control have several internal identifiers related to MVCC. I see a lot of people being confused what they are and why they are needed so I decided to take a time to explain it a bit. This is general explanation, it does not corresponds to Innodb in particular […]
So we’re back from the company meeting which took place last week in Hurghada, Egypt. It was a great opportunity to meet a lot of people for the first time in person to work together and have some fun.