Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 61 to 70 of 134 10 Older Entries

Displaying posts with tag: tips (reset)

A workaround for the performance problems of TEMPTABLE views
+0 Vote Up -0Vote Down

MySQL supports two different algorithms for views: the MERGE algorithm and the TEMPTABLE algorithm. These two algorithms differ greatly. A view which uses the MERGE algorithm can merge filter conditions into the view query itself. This has significant performance advantages over TEMPTABLE views. A view which uses the TEMPTABLE algorithm will have to compute the entire contents of the view before any filter is applied. Computing the entire contents requires a temporary table and many more rows may be accessed than otherwise would had the filter been merged into the view query.

As long as a view avoids aggregation, …

  [Read more...]
MySQL Network Connections
+0 Vote Up -0Vote Down


If your MySQL server has hundreds of clients (applications) and tens of thousands of queries per second,  MySQL default network settings may NOT be for you.  Network performance is not often a significant factor in the performance of MySQL.  That said, there are things to consider.

If you are building new applications …

  [Read more...]
Debugging problems with row based replication
+3 Vote Up -3Vote Down

MySQL 5.1 introduces row based binary logging.  In fact, the default binary logging format in GA versions of MySQL 5.1 is 'MIXED' STATEMENT*;   The binlog_format  variable can still be changed per sessions which means it is possible that some of your binary log entries will be written in a row-based fashion instead of the actual statement which changed data, even when the global setting on the master is to write binary logs in statement mode.   The row-based format does offer advantages particularly if triggers or stored procedures are used, or if non deterministic functions like RAND() are used in DML statements.

…  [Read more...]
Simple Backup Restore Trick
+2 Vote Up -1Vote Down

I don't usually post these simple tricks, but it came to my attention today and it's very simple and have seen issues when trying to get around it. This one tries to solve the question: How do I restore my production backup to a different schema? It looks obvious, but I haven't seen many people thinking about it.

Most of the time backups using mysqldump will include the following line:

USE `schema`;
This is OK when you're trying to either (re)build a slave or restore a production database. But what about restoring it to a test server in a different schema?

The actual trick
Using …

  [Read more...]
MySQL master/slave support merged into Linux-HA
+0 Vote Up -0Vote Down

(Re-posted from Florian’s blog.)

MySQL replication support for the Pacemaker cluster manager (the stuff that we explained in this webinar) has made it into the Linux-HA resource agents default branch. If you are interested in testing — and …

  [Read more...]
Developer Tips using MySQL
+0 Vote Up -0Vote Down

I get ask, by application developers,  “how do you optimize MySQL”.  I do lots of things that don’t really relate to a developer. I analyze the percent of queries are being pulled from cache for instance.  What a developer can do to optimize the SQL they develop is a different questions.   So here is a quick list of things applications developers should know about MySQL.

Explain will analyze your query.

This example shows the possible indexes (keys) that could be used and the index that was selected.  2,262 rows where …

  [Read more...]
fadvise – may be not what you expect
+3 Vote Up -0Vote Down

I often hear suggestion to use fadvise system call to avoid caching in OS cache.
We recently made patch for tar, which supposes to create archive without polluting OS cache, as like in case with backup, you do not really expect any benefits from caching.

However working on the patch, I noticed, that fadvise with FADV_DONTNEED, does not really do what I expected (I used this call as it is often suggested for this purpose). In fact it does not prevent caching, it only releases already cached data.

And if we do man fadvise, it says exactly:

  [Read more...]
Too many connections? No problem!
+8 Vote Up -0Vote Down

Did that ever happen to you in production?


  1. [percona@sandbox msb_5_0_87]$ ./use
  2. ERROR 1040 (00000): Too many connections

Just happened to one of our customers. Want to know what we did?

For demo purposes I'll use sandbox here (so the ./use is actually executing mysql cli). Oh and mind it is not a general-purpose best-practice, but rather a break-and-enter hack when the server is flooded. So, when this happens in production, the problem is - how do you quickly regain access to mysql server to see what are all …

  [Read more...]
When should you store serialized objects in the database?
+4 Vote Up -0Vote Down

A while back Friendfeed posted a blog post explaining how they changed from storing data in MySQL columns to serializing data and just storing it inside TEXT/BLOB columns. It seems that since then, the technique has gotten more popular with Ruby gems now around to do this for you automatically.

So when is it a good idea to use this technique?

If the application really is schema-less and has a lot of optional parameters that do not appear in every …

  [Read more...]
Enabling IPv6 Support in nginx
+0 Vote Up -0Vote Down

This is going to be a really short post, but for someone it could save an hour of life.

So, you’ve nothing to do and you’ve decided to play around with IPv6 or maybe you’re happened to be an administrator of a web service that needs to support IPv6 connectivity and you need to make your nginx server work nicely with this protocol.

First thing you need to do is to enable IPv6 in nginx by recompiling it with --with-ipv6 configure option and reinstalling it. If you use some pre-built package, check if your nginx already has …

  [Read more...]
10 Newer Entries Showing entries 61 to 70 of 134 10 Older Entries

Planet MySQL © 1995, 2015, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.