Recently I was tasked with investigating slippage between master and slave in a standard replication setup.
The client was using Maatkit’s mk-table-checksum to check his slave data was indeed a fair copy of that of the master.
Ever wonder what is stored in InnoDB buffer pool at the moment ?
It is not so hard actually - we made a short patch for MySQL 5.0 which show innodb buffer pool content
A paginated display is one of the top optimization scenarios we see in the real world. Search results pages, leaderboards, and most-popular lists are good examples. You know the design pattern: display 20 results in some most-relevant order. Show a “next” and “previous” link. And usually, show how many items are in the whole list and how many pages of results there are.
Rendering such a display can consume more resources than the entire rest of the site!
The problem of MySQL Replication unable to catch up is quite common in MySQL world and in fact I already wrote about it. There are many aspects of managing mysql replication lag such as using proper hardware and configuring it properly. In this post I will just look at couple of query design mistakes which result in low hanging fruit troubleshooting MySQL Replication Lag
[read more...]
Whilst working with a client recently I noticed a large number of temporary tables being created on disk.
show global status like 'Created_tmp%'| Created_tmp_disk_tables | 91970 |
| Created_tmp_files | 19624 |
| Created_tmp_tables | 1617031 |
I’ll be speaking at HighLoad++ conference in Moscow,Russia taking place 6,7 Oct 2008. This conference was quite success with very interesting presentation last year and I’m hoping it would be even better this year.
I also will have a full dayMaster Class focused on Scaling MySQL w Sharding and Replication based on material in our book
Interesting enough this year the conference became two another one being about same topic and very similar name taking place 22-23 September. It also sounds great and it would be hard for me to pick one or another one if not my 10 year wedding anniversary this week which made it impossible for travel.
When optimizing queries for customers, the first thing I do with a slow query is figure out what it’s trying to do. You can’t fully optimize a query unless you know how to consider alternative ways to write it, and you can’t do that unless you know what the query “means.” I frequently run into a situation where I’m forced to stop and ask the developers what they were trying to do with COUNT(). This is database-agnostic, not related to MySQL.
Quite commonly I get a question similar to this - “My Innodb Buffer Pool is already 90% full, should I be thinking about upgrading memory already?”
This is a wrong way to put the question. Unless you have very small database (read as database which is less than innodb_buffer_pool_size) You will have all buffer pool busy sooner or later.
How to figure out if it is time for upgrade when ?
[read more...]
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
As you see query is not fast, mainly because information_schema.statistics is slow by itself, but in any case very helpful.
For some versions of the patch, where the table has only the INDEX_NAME and the ROWS_READ columns, you can use this query:
Recently Google published V2 release of patches, one of them user_statistics we use in our releases.
New features are quite interesting so we decided to port it to fresh releases of MySQL. Features includes:
Our port includes:
del.icio.us
digg