Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 19

Displaying posts with tag: Tips & Tricks (reset)

You upgraded to > 5.1 but did you turn on performance_schema?
+0 Vote Up -0Vote Down

If you’ve upgraded from MySQL 5.1 to a newer version (5.5?  5.6? 5.7?)  Mark Leith shows you why you should turn on performance_schemaRoland Bouman gave us Common_Schema to help improve the SQL user experience.   Mark now lets us look inward at the IO latency to improve the system.

Mark has developed a great set of views and procedures to work with performance_schema that can give you things like:

  • A list all statements who’s average runtime, in microseconds, are the top 5% slowest
  [Read more...]
Tip: how to save 10 minutes a day?
+1 Vote Up -0Vote Down

For any person actively working with MySQL databases on the command line level, logging in, logging out for a few moments, and then logging in again, all repeated many times, not only eventually becomes annoying, especially with passwords that aren’t easy to type, but it also can take a lot of time over the course of a day. For a long time I’ve been relaying on something that allows me to avoid most of this effort while working. The solution is of course not to log out unless you actually want to.

Linux, BSD and Solaris all allow suspending a running task and resuming its execution at a later time. This can be used to temporarily exit MySQL client without having it to leave the database.

When inside MySQL, you can press Ctrl+Z to suspend the client program and return to the system shell. You will be able to

  [Read more...]
How to find MySQL binary logs, error logs, temporary files?
+1 Vote Up -0Vote Down

Have you ever spent a lot of time trying to locate where MySQL keeps some file? Here is a quick way to find all this information in one place.

The obvious way is through examining database options in my.cnf or looking at the output of SHOW GLOBAL VARIABLES. But not every path may be explicitly set in the configuration, in such case MySQL may assume some default, while other options may be set using relative paths.

A different approach is listing all files that a running database instance keeps open and searching for the required information there. I find that method by far the fastest whenever I need to learn any of such details.

garfield ~ # lsof -nc mysqld | grep -vE '(\.so(\..*)?$|\.frm|\.MY?|\.ibd|ib_logfile|ibdata|TCP)'
mysqld  30257
  [Read more...]
Extracting one table from mysqldump or phpMyAdmin backup
+1 Vote Up -0Vote Down

Using mysqldump is a quick way to do backups, although usually limited to only smaller databases – perhaps up to a few gigabytes large. It is still a fairly popular solution as majority of databases aren’t even that big. Also phpMyAdmin provides a variant of mysqldump format through its Export function. Everything works well for plain dump and restore, but in certain situations it is necessary to restore only a single table. With all data being in a single text file, it may not be a trivial task. Here is how I deal with the problem.

Rather than editing out parts of the file that I don’t need, I wrote simple one-liners that do that for me. These are not perfect as for example they can’t deal with multiple tables by the same name existing in several different schemas, but that so far

  [Read more...]
How to check if MySQL has been swapped out?
+2 Vote Up -0Vote Down

How to check if any MySQL memory has been swapped out? This post explains it.

Check if system is currently using any swap:

server ~ # free -m
             total       used       free     shared    buffers     cached
Mem:          3954       2198       1755          0        190       1040
-/+ buffers/cache:        968       2985
Swap:         3906          0       3906

In the above example swap is not in use, so no further checks would be necessary.

However if free command would report some usage, how to check whether MySQL memory was swapped out or not?

It is not possible to determine that using standard tools such as ps or top. They will report various memory related information per each process, but no clear indication whether something is in RAM or in swap space. But it is

  [Read more...]
How to selectively kill queries in MySQL?
+1 Vote Up -0Vote Down

For as long as it is only about a few of them, it is as simple as looking at the SHOW PROCESSLIST output for thread identifiers to kill. They can be found in the first column called Id. These values can be passed to KILL thread_id command in MySQL. The problems appear with more complex scenarios. What if one needs to terminate all queries running longer than ten seconds? Doing copy&paste repetitively could take a lot of time with twenty or so candidate threads. This can be done much more efficiently.


Earlier today we showed “Anohter way to work with MySQL process list”. That post presents how PROCESSLIST table can be used to retrieve any

  [Read more...]
Anohter way to work with MySQL process list
+1 Vote Up -0Vote Down

In an earlier post titled “How to work with a long process list in MySQL”, we showed a neat way to work with the process list by using various shell tools. But some of that can also be done using pure SQL.

Since version 5.0 a lot of MySQL meta and runtime information can be accessed by reading from predefined views in a database called INFORMATION_SCHEMA. The information which can be found there includes lists of threads, tables, user created views, triggers, stored procedures and many, many other things. The newer the MySQL version, the more items can found in there.

This post is about the process list, so it will focus on just one of the tables called PROCESSLIST. Its structure is virtually

  [Read more...]
Was a query served from MySQL Query Cache?
+1 Vote Up -0Vote Down

The MySQL query cache is a special buffer, where database stores the text of a SELECT statement together with the corresponding result that was sent to the client. For as long as no table that a statement refers to changes in any way, including the contents, the cached result can be re-used to answer any identical sub-sequent SELECT statements. But how to tell whether a query was executed or returned from the cache?

There are at least three ways to check it.

Method 1

MySQL exposes a number of runtime statistics that are accessible with SHOW STATUS statement. Among the long list of various counters, one is called Com_select which shows how many times a SELECT statement was executed. However if a SELECT is served from the query cache, it does not actually execute, so it is not

  [Read more...]
How to find MySQL configuration file?
+2 Vote Up -0Vote Down

A customer called me today asking for help with locating the configuration file used by one of their production MySQL instances. From the description I was given it appeared that their server had at least six different copies of my.cnf file in different locations on disk. And all were similar enough that each could actually be the one. All superfluous files were the result of a bit negligent system administration. So what turned to be the quickest and the least destructive way to find the correct one?

Initially suspecting the server was simply running more than just one MySQL instance, I logged in to take a deeper look. But I found only one mysqld process and, indeed, several configuration files.

All of them seemed good candidates:


In many cases you

  [Read more...]
How to work with a long process list in MySQL
+0 Vote Up -0Vote Down

I am generally a big fan of command line tools. This also applies to MySQL client software such as mysql or mysqladmin. To those spoiled by graphical interfaces, working in text mode may seem crude or even difficult. But the truth is that once you get used to these tools, you will be able to accomplish many things a lot faster than with any GUI client. Of course, using text terminal, which is the environment for any command line tool, has its drawbacks and limitations. For example on a relatively busy MySQL server, every so often when you run SHOW [FULL] PROCESSLIST, which lists client threads connected to a database, you can receive an output that will be many screens long. Sometimes it might be due to the high number of established connections – each takes at least one line on the screen, or sometimes due to some longish

  [Read more...]
The Full Monty – Scientfic Linux 6.1, drbd, PaceMaker, MySQL, Tunsten Replication and more
+0 Vote Up -0Vote Down

PART 1 – This will be a multi part post.


After years of supporting MySQL, for many different companies, I’ve seen this story played out again and again.
The company:

  • chooses a Database Management System (MySQL)
  • installs the DBMS on a computer with other processes
  • writes many programs to access the data (Without concern on how the queries are written.)
  • moves DBMS to a computer of its own and writes more programs
  • buy bigger computer to run the DBMS and writes more programs
  • tires of DBMS response times and outages caused by developers working on production systems and hires a Database Administrator to fix the mess

This is a step by step description of  how I

  [Read more...]
What is this MySQL file used for?
+7 Vote Up -0Vote Down

MySQL keeps many different files, some contain real data, some contain meta data. Witch ones are important? Witch can your throw away?

This is my attempt to create a quick reference of all the files used by MySQL, whats in them, what can you do if they are missing, what can you do with them.

When I was working for Dell doing Linux support my first words to a customer where “DO YOU HAVE COMPLETE AND VERIFIED BACKUP?” Make one now before you think about doing anything I suggest here.

You should always try to manage your data through a MySQL client.  If things have gone very bad this may not be possible. MySQL may not start. If your file system get corrupt you may have missing files. Sometimes people create other files in the MySQL directory (BAD).  This should help you understand what is safe to remove.

Before you try to work with one of

  [Read more...]
It’s about Time.
+6 Vote Up -0Vote Down


This post started with a simple question: “Does the function NOW() get executed every time a row is examined?” According to the manual,  “Functions that return the current date or time each are evaluated only once per query …. multiple references to a function such as NOW() … produce the same result. …. (However,) as of MySQL 5.0.12, SYSDATE() returns the time (the row is) executes. “

  • CURDATE() returns the current date.
  • CURTIME() returns the current time.
  • UTC_DATE() returns the current UTC date.
  • UTC_TIME() returns the current UTC time.
  • NOW() return the current date and time.
  • UTC_TIMESTAMP() returns the current UTC date and time.
  • SYSDATE() returns the
  [Read more...]
MySQL GIS – Part 1
+3 Vote Up -1Vote Down

In my business (weather) we use lots map based (Geo) information.  Almost every table has latitude and longitude. Working with this kind of data can be exciting and frustrating.  This should give you a quick start into GIS with MySQL.

“A geographic information system (GIS), or geographical information system, is any system that captures, stores, analyzes, manages, and presents data that are linked to location. In the simplest terms, GIS is the merging of cartography, statistical analysis, and database technology. GIS systems are used in cartography, remote sensing, land surveying,

  [Read more...]
Does Size or Type Matter?
+4 Vote Up -0Vote Down

MySQL seems to be happy to convert types for you. Developers are rushed to complete their project and if the function works they just move on. But what is the costs of mixing your types? Does it matter if your are running across a million rows or more? Lets find out.

Here is what the programmers see.

mysql> select 1+1;
| 1+1 |
|   2 |
1 row in set (0.00 sec)

mysql> select "1"+"1";
| "1"+"1" |
|       2 |
1 row in set (0.00 sec)


What if we do a thousand simple loops?  How long does the looping itself take?

The BENCHMARK() function executes the expression

  [Read more...]
Federated Tables
+0 Vote Up -0Vote Down

Your searching for how to create a join across two databases on two different servers and it can’t be done directly.   select  d1.a, d2.b from db1@server1 join db2@server2 where db1.c = db2.c; does not work.

You learn about federated databases.  The federated storage engine allows accesses data in tables of remote databases.  Now how do you make it work?

1) Check if the federated storage engine is supported.  Federation is OFF by default!

mysql> show engines;
| Engine     | Support | Comment                                                        |
| InnoDB     | YES    
  [Read more...]
MyTOP Upated
+1 Vote Up -0Vote Down

MyTOP is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL.

UPDATE – I just fond Jeremy did update MyTOP in 2009 and released it on GitHub.  He fixed the 64x and 5.x bugs. He also incremented the version number to 1.7.  So, I’m bumping my number to 1.8.

Jeremy D. Zawodny <Jeremy@Zawodny.com> wrote the original in 2000 and has continued to update it until 2007. The 1.6 version works on MySQL up to version 4.x.

For weeks now and I’m been working on bringing it up to date.  When I started using

  [Read more...]
New AutoMySQLBackup Script
+0 Vote Up -0Vote Down

MySQL Backup Script has been around for a long time.  I have used it on and off for years but now I’ve needed to make some  improvements.   This script is based on VER. 2.6 – http://sourceforge.net/projects/automysqlbackup/ Copyright (c) 2002-2003 wipe_out@lycos.co.uk.
I have added my own Copyright (c) 2010 mark@grennan.com – http://www.mysqlfanboy.com/Files/automysqlbackup.sh. But as the code says:  This program is distributed in the hope that it will be useful,  but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more details.

My improvements include:

# VER 2.6 Beta 5 – MTG – (2010-04-18)

  [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 selected and then sorted (Using file sorts) and one record was returned (limit 1).

mysql> explain SELECT 5/9*(temp_F-32) as t, 5/9*(dewpt_F-32) as td, speed_mps as spd, dir
 > where stn='KLDM' and
  [Read more...]
Showing entries 1 to 19

Planet MySQL © 1995, 2014, 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.