Showing entries 31 to 40 of 141
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: tips (reset)
Was a query served from MySQL Query Cache?

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 accounted in Com_select. The conclusion must be that if a query runs, but it …

[Read more]
How to find MySQL configuration file?

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 could simply check system process list using ps:

server ~ …
[Read more]
How to work with a long process list in MySQL

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 queries spanning over multiple lines. Finding relevant information there usually isn’t …

[Read more]
MySQL Backup and Restore

Making a compressed backup

mysqldump -u root -p database_name | bzip2 > output.sql.bz2
Restoring the compressed backup

shell> bunzip2 < output.sql.bz2 | mysql -u root -p
Copy database from one server to another

mysqldump db-name | ssh mysql -h db-name

mysqldump -u username -p'password' db-name | ssh mysql -u username -p'password -h db-name
Copy only table foo to database bar

mysqldump db-name foo | ssh mysql bar

mysqldump -u user -p'password' db-name foo | ssh mysql -u user -p'password' db-name foo

Add a Prefix to a fields value

Here's how to update your fields value by adding a prefix on it. You will have to use some functions in your WHERE statement to filter only those records that you want to be updated.

UPDATE `database`.`table`
SET field_value = CONCAT('PREFIX', field_value)
WHERE field_value = var;

Setting up XFS on Hardware RAID — the simple edition

There are about a gazillion FAQs and HOWTOs out there that talk about XFS configuration, RAID IO alignment, and mount point options.  I wanted to try to put some of that information together in a condensed and simplified format that will work for the majority of use cases.  This is not meant to cover every single tuning option, but rather to cover the important bases in a simple and easy to understand way.

Let’s say you have a server with standard hardware RAID setup running conventional HDDs.

RAID setup

For the sake of simplicity you create one single RAID logical volume that covers all your available drives.  This is the easiest setup to configure and maintain and is the best choice for operability in the majority of normal configurations.  Are there ways to squeeze more performance out of a server by dividing the logical volumes: perhaps, but it requires a lot of fiddling and custom tuning to …

[Read more]
Never say "there is no way"

Reading a recent MySQL book, I saw an example of SHOW CREATE TABLE that comes with backticks (`) around the table and column names, and a comment:
Unfortunately, there is no way to remove this from generated syntax with this command.(Emphasis mine).
Here's how it goes:

mysql> show create table mytest\G
*************************** 1. row ***************************
Table: mytest
Create Table: CREATE TABLE `mytest` (
`id` int(11) NOT NULL,
`description` varchar(50) DEFAULT NULL,
1 row in set (0.00 sec)

Of course, there is a way!

mysql> pager tr -d '`'
PAGER set to 'tr -d '`''
mysql> show create table mytest\G
*************************** 1. row ***************************
Table: mytest
Create Table: CREATE TABLE …
[Read more]
TIL: Lookout For DEFINER

The Issue
I haven't blogged in a while an I have a long TODO list of things to publish: The repository for the SNMP Agent, video and slides of my OSCON talk and a quick overview of MHA master-master support. In the meantime, here's a little fact that I didn't know from MySQL CREATE VIEW documentation:

Although it is possible to create a view with a nonexistent DEFINER account, an error occurs when the view is referenced if the SQL SECURITY value is DEFINER but the definer account does not exist.How can this be possible?
The ProblemFor a number of reasons we don't have the same user accounts on the master than we have on the slaves (ie: developers shouldn't be querying the master). Our configuration files include the following line:


So …

[Read more]
Fatal timeout !

There are several parameters to set a timeout on MySQL :

But I would like to focus on wait_timeout (or interactive_timeout depending on how you connect)

This timeout allows MySQL to automatically close a connection in case of non-activity during the time defined by this parameter (default value is 28000 seconds).

The problem I wish to explain here may happen when this timeout is set to a low value (about 10 to 30 seconds).

Indeed, in this case, this timeout may have serious consequences, look at that :
[The wait_timeout parameter is set to 10 seconds in this case]

You are connected through the standard MySQL client and you need to run a delete query (bypass autocommit) :

  • mysql> start transaction;
  • [ You …
[Read more]
Aligning IO on a hard disk RAID – the Theory

Now that flash storage is becoming more popular, IO alignment question keeps popping up more often than it used to when all we had were rotating hard disk drives. I think the reason is very simple – when systems only had one bearing hard disk drive (HDD) as in RAID1 or one disk drive at all, you couldn’t really have misaligned IO because HDDs operate in 512-byte sectors and that’s also the smallest amount of disk IO that systems can do. NAND flash on the other hand can have a page size of 512-bytes, 2kbytes or 4kbytes (and often you don’t know what size it is really) so the IO alignment question becomes more relevant.

It was and still is, however, relevant with HDD RAID storage – technology we have been using for many years – when there’s striping like in RAID0, 5, 6 or any variation of them (5+0, 1+0, 1+0+0 etc.). While IO inside the RAID is perfectly aligned to disk sectors (again due to the fact operations are done in …

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