Showing entries 91 to 100 of 141
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: tips (reset)
How to generate per-database traffic statistics using mk-query-digest

We often encounter customers who have partitioned their applications among a number of databases within the same instance of MySQL (think application service providers who have a separate database per customer organization ... or wordpress-mu type of apps). For example, take the following single MySQL instance with multiple (identical) databases:

| Database |
| db1      |
| db2      |
| db3      |
| db4      |
| mysql    |

Separating the data in this manner is a great setup for being able to scale by simply migrating a subset of the databases to a different physical host when the existing host begins to get overloaded. But MySQL doesn't allow us to examine statistics on a per-database basis.

Enter Maatkit.

There is an often-ignored gem in Maatkit's mk-query-digest, and that is the --group-by argument. This can …

[Read more]
Which adaptive should we use?

As you may know, InnoDB has 2 limits for unflushed modified blocks in the buffer pool. The one is from physical size of the buffer pool. And the another one is oldness of the block which is from the capacity of transaction log files.

In the case of heavy updating workload, the modified ages of the many blocks are clustered. And to reduce the maximum of the modified ages InnoDB needs to flush many of the blocks in a short time, if these are not flushed at all. Then the flushing storm affect the performance seriously.

We suggested the "adaptive_checkpoint" option of constant flushing to avoid such a flushing storm. And finally, the newest InnoDB Plugin 1.0.4 has the new similar option "adaptive_flushing" as native.

Let's check the adaptive flushing options at this post.


< adaptive_checkpoint=reflex (older method)>

[Read more]
3 ways MySQL uses indexes

I often see people confuse different ways MySQL can use indexing, getting wrong ideas on what query performance they should expect. There are 3 main ways how MySQL can use the indexes for query execution, which are not mutually exclusive, in fact some queries will use indexes for all 3 purposes listed here.

Using index to find rows The main purpose of the index is to find rows quickly - without scanning whole data set. This is most typical reason index gets added on the first place. Most popular index type in MySQL - BTREE can speed up equality and prefix range matches. So if you have index on (A,B) This index can be used to lookup rows for WHERE clauses like A=5 ; A BETWEEN 5 AND 10 ; A=5 AND B BETWEEN 5 AND 10 it however will NOT be able to help lookup rows for B BETWEEN 5 AND 10 predicate because it is not index prefix. …

[Read more]
XtraDB: The Top 10 enhancements

Note: This post is part 2 of 4 on building our training workshop.

Last week I talked about why you don't want to shard. This week I'm following up with the top 10 enhancements that XtraDB has over the built-in InnoDB included in MySQL 5.0 and 5.1.  Building this list was not really a scientific process - It's always difficult to say which feature is better than another, because a lot of it depends on the individual workload.  My ranking method was to pick the features that have the highest impact and are most applicable to all workloads first:

  1. CPU scalability fixes - XtraDB improves performance on systems with multi-cpus (see docs …
[Read more]
Why you don’t want to shard.

Note: This blog post is part 1 of 4 on building our training workshop.

The Percona training workshop will not cover sharding. If you follow our blog, you'll notice we don't talk much about the subject; in some cases it makes sense, but in many we've seen that it causes architectures to be prematurely complicated.

So let me state it: You don't want to shard.

Optimize everything else first, and then if performance still isn't good enough, it's time to take a very bitter medicine. The reason you need to shard basically comes down to one of …

[Read more]
Copying InnoDB tables between servers

The feature I announced some time ago is now available in our latest releases of XtraBackup 0.8.1 and XtraDB-6.

Now I am going to show how to use it (the video will be also available on
Let's take tpcc schema and running standard MySQL ® 5.0.83, and assume we want to copy order_line table to different server. Note I am going to do it online, no needs to lock or shutdown server.

To export table you need XtraBackup, and you can …

[Read more]
Recovery after DROP [ TABLE | DATABASE ]

In your recovery practice we often face the problem when data lost by execution DROP TABLE or DROP DATABASE statement. In this case even our InnoDB Data Recovery tool can't help, as table / directory with files was deleted (if you have innodb-file-per-table). And the same for MyISAM, all .MYD / .MYI / .frm - files are deleted in this case.

So first step after DROP is to restore files, and for ext3 file system there are two utilities which can help of you are fast (and lucky) enough.
First one is ext3grep, with some instruction on this page
And also there is …

[Read more]
Is DRBD the right choice for me?

It seems pretty common to find customers install DRBD for the wrong reasons. There are many pros/cons to compare DRBD to replication, but I've managed to cut down my spiel I give to customers to these two points:

  • DRBD's aim (assuming replication mode C) is to provide 100% consistency, and then as much uptime as possible.
  • MySQL Replication (with a manager such as MMM) aims to have 100% availability, at the potential loss of some data surrounding a failure.

So if you are installing DRBD with the aim of purely "availability", and are not worried about losing that last write on the crash to your master database that (hopefully) happens only once every few years, you may be using the wrong technology.

While the prized "1 minute failover" is possible in DRBD, it doesn't really explain the full picture. The typical crash …

[Read more]
Just do the math!

One of the most typical reasons for performance and scalability problems I encounter is simply failing to do the math. And these are typically bad one because it often leads to implementing architectures which are not up for job they are intended to solve.

Let me start with example to make it clear. Lets say you're doing some reports from your apache log files - how many distinct visitors hit the page and stuff like that. You picked full logs because they are great in flexibility - you can run any adhoc queries and drill down as much as you like. Initially traffic was small and young and with 10000 page views a day you few days of history the queries there instant which gave you a confidence this approach will work.

As the time passes and you get 1.000.000 events per day and looking to do reporting for up to the whole year worth of data you find things not working any more with response times for individual queries taking half an …

[Read more]
Replace or Rename: that is the question.

Forget tedious hours of searching links to the renamed object and replacing them.

To save your efforts, dbForge Studio for MySQL automates renaming of tables, columns of tables, views, aliases, stored routines, local variables, triggers, events, UDFs, and users and even offers a convenient preview of the changes before applying them.

To rename an object in the script, right-click it and select the Rename option from the menu. The Rename dialog box opens where you can enter a new name and view all references to the renamed object in the expressions, strings, and even comments.

All you have to do is to select required references and press OK!

The refactoring functionality is indispensable while working with database projects. You can export a database to a project, rename …

[Read more]
Showing entries 91 to 100 of 141
« 10 Newer Entries | 10 Older Entries »