PlanetMysql.ru — информация о СУБД MySQL

LVM (Logical Volume Management) is a very important tool to have in the toolkit of a MySQL DBA. It allows you to create and extend logical volumes on the fly. This allows me to, say, add another disk and extend a partition effortlessly. The other very important feature is the ability to take snapshots, that you can then use for backups. All in all its a must have tool. Hence, this guide will allow you to understand various terminologies associated with LVM, together with setting up LVM volumes and in a later part will also show you how to extend...
PlanetMySQL Voting: Vote UP / Vote DOWN

So your not a MySQL DBA, but you have to perform like one. If you have a production environment that’s running now, what are the first things you do when it’s not running or reported as not running?

1. Are the MySQL processes running? (i.e. mysqld and mysqld_safe)
2. Can you connect locally via cli?
3. What’s in the MySQL error log?
4. What are current MySQL threads doing? Locked? long running? how many? idle sources?
5. Can you connect remotely via cli?
6. Verify free diskspace?
7. Verify system physical resources?
8. If this is a slave, is MySQL replication running? Is it up to date?
9. What is the current MySQL load, e.g. reads/writes/throughput/network/disk etc?
10. What is the current InnoDB state and load? (based on if your using InnoDB)

After you do this manually more then once you should be scripting these commands to be productive for future analysis and proactive monitoring?

Is a problem obvious? Does the output look different to what a normal environment looks like? (HINT: This list is not just for when there is a problem)

So moving forward?

1. Is disk/memory/cpu/network bottleneck an issue you can resolve?
2. Can you improving locking statements (if applicable)?
3. Can you identify, analyse and tune long running statements?
4. Do you know how to restart MySQL?
5. Do you know who to call when you have a non working environment?
6. When did your backup last run?
7. Does your last backup work?

In order to support any level of production MySQL environment you need to know the answers to these questions? If you don’t, then this is your homework checklist for MySQL DBA operations 101. There a number of resources where you can find the answers, and this help can be available online, however never assume the timeliness of responses, especially if your expecting if for FREE! Open source software can be free, open source support rarely is.

PlanetMySQL Voting: Vote UP / Vote DOWN

While working on a deployment we came across a nasty surprise. In hindsight it was avoidable, but it never crossed our minds it could happen. I'll share the experience so when you face a similar situation, you'll know what to expect.

Scenario

To deploy the changes, we used a pair of servers configured to replicate with each other (master-master replication). There are many articles that describe how to perform an ALTER TABLE with minimum or no downtime using MySQL replication. The simple explanation is:

1. Set up a passive master of the database you want to modify the schema. 
2. Run the schema updates on the passive master.
3. Let replication to catch up once the schema modifications are done.
4. Promote the passive master as the new active master.

The details to make this work will depend on each individual situation and are too extensive for the purpose of this article. A simple Google search will point you in the right direction.

The Plan

The binlog_format variable was set to MIXED. While production was still running on the active master, we stopped replication from the passive to the active master so we would still get all the DML statements on the passive master while running the alter tables. Once the schema modifications were over, we could switch the active and passive masters in production and let the new passive catch up with the table modifications once the replication thread was running again.

The ALTER TABLE statement we applied was similar to this one:

ALTER TABLE tt ADD COLUMN cx AFTER c1;

There were more columns after cx and c1 was one of the first columns. Going through all the ALTER TABLE statements takes almost 2 hour, so it was important to get the sequence of event right.

Reality Kicks In

It turns out that using AFTER / BEFORE or changing column types broke replication when it was writing to the binlog files in row based format, which meant that we couldn't switch masters as planned until we had replication going again. As a result we had to re-issue an ALTER TABLE to revert the changes and then repeat them without the AFTER / BEFORE.

The column type change was trickier and could've been a disaster, fortunately this happened on a small table (~400 rows which meant the ALTER TABLE took less than 0.3sec). In this case we reverted the modification on the passive master and run the proper ALTER TABLE on the active master. Should this have happened with a bigger table, there was no other alternative than either rollback the deployment or deal with the locked table while the modification happened.

Once this was done we were able to restart the slave threads, let it catch up and and everything was running as planned ... but with a 2hr delay.

Unfortunately, using STATEMENT replication wouldn't work in this case for reasons that would need another blog article to explain.

Happy Ending

After the fact, I went back to the manual and I found this article: Replication with Differing Table Definitions on Master and Slave. I guess we should review the documentation more often, the changes happened after 5.1.22. I shared this article with the development team, so next time we won't have surprises.
PlanetMySQL Voting: Vote UP / Vote DOWN

I just have to chime in about how happy I am with my new job. I now work for Blue Gecko, as of August 30th. My role is a Senior Database and Systems Administrator. Blue Gecko is based out of Seattle though I'll be working out of my home in New Hampshire, albeit with my frequent travels to Seattle for family reasons, this will work out quite well.

Already in the last week, I've engaged in several tasks, all of which have been very interesting problems to solve. Not only that, but I've spoken with several existing and potential customers and never realized I really enjoy consulting with and acquiring customers-- hearing what problems they need to solve and being able to ascertain quickly how to solve those problems, making the customer look forward to engaging with us.

Who is Blue Gecko?

Based out of Seattle, their website states (http://www.bluegecko.net/)

"We don't eat, sleep, or go on vacation. We live for three things:

* Smooth, uninterrupted database operation for your company
* Proactive monitoring for potential problems
* Rapid, expert response with no restrictions or delays

"

We are a remote DBA (Database Administrative) service. We provide a service that would require an organization to have to hire a bunch of DBAs and even sysadmins. We provide this for Oracle, MySQL, and recently PostgreSQL and SQL Server.

Just from my first week here, there are some top-notch people that I'm already enjoying working with.

Some of the tasks I've worked on:

* Restoring data that a developer accidentally truncated from tables using InnoDB Tools (http://code.google.com/p/innodb-tools/). I hadn't done this before, and it was fantastic to be able to restore that data in such a dire situation

* Optimizing a query and ultimately how the data is stored in a table for a query that used a file sort that would be extremely slow unless you forced use of the index that the order by was using. The teaser was that EXPLAIN would show a full-table scan if the force index was used. This was a good exercise in understanding the buffer pool as well as how InnoDB works with indexes.

* Crawling through a schema with a bunch of tables and finding many optimizations

* Discussing deployment of The Sphinx Search Engine for a client who needs search functionality

* Various Perl questions from one of my Co-workers. I was able to send the code that I wrote for my book to help them solve a problem. I felt great being able to help someone so soon after starting a new job

The thing that has really dawned on me is that I prefer working on components within the LAMP stack, especially MySQL, with an eye on where NoSQL fits in as well.

I just wanted to write about my realization that I've had over the last several days!
PlanetMySQL Voting: Vote UP / Vote DOWN

I just have to chime in about how happy I am with my new job. I now work for Blue Gecko, as of August 30th. My role is a Senior Database and Systems Administrator. Blue Gecko is based out of Seattle though I'll be working out of my home in New Hampshire, albeit with my frequent travels to Seattle for family reasons, this will work out quite well.

Already in the last week, I've engaged in several tasks, all of which have been very interesting problems to solve. Not only that, but I've spoken with several existing and potential customers and never realized I really enjoy consulting with and acquiring customers-- hearing what problems they need to solve and being able to ascertain quickly how to solve those problems, making the customer look forward to engaging with us.

Who is Blue Gecko?

Based out of Seattle, their website states (http://www.bluegecko.net/)

"We don't eat, sleep, or go on vacation. We live for three things:

* Smooth, uninterrupted database operation for your company
* Proactive monitoring for potential problems
* Rapid, expert response with no restrictions or delays

"

We are a remote DBA (Database Administrative) service. We provide a service that would require an organization to have to hire a bunch of DBAs and even sysadmins. We provide this for Oracle, MySQL, and recently PostgreSQL and SQL Server.

Just from my first week here, there are some top-notch people that I'm already enjoying working with.

Some of the tasks I've worked on:

* Restoring data that a developer accidentally truncated from tables using InnoDB Tools (http://code.google.com/p/innodb-tools/). I hadn't done this before, and it was fantastic to be able to restore that data in such a dire situation

* Optimizing a query and ultimately how the data is stored in a table for a query that used a file sort that would be extremely slow unless you forced use of the index that the order by was using. The teaser was that EXPLAIN would show a full-table scan if the force index was used. This was a good exercise in understanding the buffer pool as well as how InnoDB works with indexes.

* Crawling through a schema with a bunch of tables and finding many optimizations

* Discussing deployment of The Sphinx Search Engine for a client who needs search functionality

* Various Perl questions from one of my Co-workers. I was able to send the code that I wrote for my book to help them solve a problem. I felt great being able to help someone so soon after starting a new job

The thing that has really dawned on me is that I prefer working on components within the LAMP stack, especially MySQL, with an eye on where NoSQL fits in as well.

I just wanted to write about my realization that I've had over the last several days!
PlanetMySQL Voting: Vote UP / Vote DOWN

If you’re new to the MySQL DBA role, you’ll be excited to learn about the Maatkit toolset.  It provides a whole host of valuable functionality and fills many of the DBAs day-to-day needs.

DatabaseJournal – Wonders of Maatkit

PlanetMySQL Voting: Vote UP / Vote DOWN

When you are importing large amounts of data from other sources LOAD DATA is a common method of inserting data into a table.
It is one of the old commands implemented in MySQL. As such it is very fast, and it has been optimized for both MyISAM and InnoDB.
All is well when you are loading data into a standalone server. All is almost well when you are using replication. LOAD DATA used to be a problem in old versions of MYSQL, prior to 4.1. With recent versions of MySQL, it is replicated correctly, and sometimes efficiently.
The trouble starts when the data file is big. The definition of big and the amount of trouble that you can get depends on many factors. That's why users may not realize that this problem exists, even with fairly large files, and then being hit by this disaster when the file is only a little larger than the previous ones.
First, let me explain what happens when you replicate LOAD DATA.

1. The LOAD DATA query runs in the master.
2. When the query is finished, the master starts pumping data to the binary log.
3. The slave receives the binary log, and it will create a copy of the data file in the default temporary directory.
4. The slave executes the LOAD DATA query using the temporary file.
5. When the slave is done loading the data, the temporary file is deleted
6. The data from the relay log is deleted

At the end of the exercise, your data is only in the database table, both in the master and in the slaves. However, during the loading, each slave needs THREE TIMES THE STORAGE of the initial data file size, not counting the indexes.
If your data is 10 GB, then you will need 20 GB on the master (10 for the table, 10 for the binary log, and eventually 10 more for the indexes).
On the slave, you will need 30 GB: 10 for the table (+ indexes if requested), 10 for the relay logs, and 10 for the file in the temporary directory. The last part is the tricky one. The temporary directory is whatever is indicated in the TMPDIR system variable. If that directory is in a partition with less than 10 GB free, your replication will break, even if your data directory has terabytes of free space.
PlanetMySQL Voting: Vote UP / Vote DOWN

As everyone knows, I am a command line guy. I am very much comfortable with the shell prompt and the command line SQL client. I do most of my work that way, and I am very much productive.
However, there comes a time when even for a command line enthusiast a GUI can be helpful.
Here comes the latest MySQL Workbench 5.2.
There are two areas where I feel that WB can give me a hand:
The first is when looking at tables that contain BLOB columns. Sure I can deal with them at the command line, but this editor makes my life easier.

When a column contains a BLOB, you can open the field viewer.

At first glance, this is nothing more than what the command line could provide. I could get output in hexadecimal format quite easily in any client. But, looking more closely, there is a tab labeled "image" that is not as easy to come by at the command line prompt.

And there is Mike Hillyer, the main author of the Sakila database, who has stored his own image in the staff table for future generations. If you stick to the command line, you may easily miss this piece of self advertising.

The second area where I like having MySQL Workbench is when I need to change my configuration file with less than common options. Since no human (apart from Sheeri, perhaps) can remember all the options, I usually need to search the manual.

In WB, instead, I can edit the options file with the GUI, without need of remembering the exact names and spelling of the items I need.
Now, if I couple the above issues with the notion that MySQL Workbench is A Useful Tool to Centrally Manage Many MySQL Instances, I think that every command line enthusiast should give this tool a try.
Lastly, I should mention that Workbench 5.2 is becoming quite popular, as the downloads map shows.

PlanetMySQL Voting: Vote UP / Vote DOWN

While I was presenting my partitioning tutorial at the latest MySQL Conference, I announced a new feature that was, as far as I knew, still in the planning stage. Mattias Jonsson, one of the partitions developers, was in attendance, and corrected me, explaining that the feature was actually available in a prototype.

So, we can have a look at this improvement, which I am sure will make DBAs quite happy. The new feature is an instantaneous exchange between a partition and a table with the same structure. Using this feature, you can transfer the contents of one partition to one table, and vice versa. Since the transition is done only in the attribution of the data, there is no copy involved. The data stays where it is at the moment. What is in the table ends up in the partition and what's in the partition ends up in the table. Let's see an example.

With the data in figure 1, where we have a partitioned table t1 and an empty table t2 with the same structure, we can issue the following statement:

ALTER TABLE t1
EXCHANGE PARTITION p2
WITH TABLE t2

After the exchange, partition p2 is empty, and table t2 contains 4 records.
If we repeat the command, the contents will be swapped again, leaving table t2 empty and partition p2 with its original contents.

If you want to test on your own, you can get the code from Launchpad. Once you get the code, you can use cmake to build the server.

$ cmake-gui .
# add the options you need. For example, enable innodb 
# or else you will need to load it as a plugin.
$ make && ./scripts/make_binary_distribution

You can then use this script to test the new functionality. You may want to change Innodb with MyISAM to test it thoroughly. At the moment, it doesn't work with the archive engine (yet).

# ############################
# test_exchange_partitions.sql
# ############################
use test;
set default_storage_engine=innodb;
drop procedure if exists compare_tables;
delimiter //
create procedure compare_tables (wanted int)
reads sql data
begin
    set @part_table     := (select count(*) from t1);
    set @non_part_table := (select count(*) from t2);
    select @part_table, @non_part_table, 
        if(@non_part_table = wanted, "OK", "error") as expected;
end //
delimiter ;

drop table if exists t1, t2;
create table t1 (i int) # not null primary key)
partition by range (i) 
    (
partition p01 values less than  (100001), 
partition p02 values less than  (200001), 
partition p03 values less than  (300001), 
partition p04 values less than  (400001), 
partition p05 values less than  (500001), 
partition p06 values less than  (600001), 
partition p07 values less than  (700001), 
partition p08 values less than  (800001), 
partition p09 values less than  (900001), 
partition p10 values less than (1000001), 
partition p11 values less than (maxvalue));

create table t2 (i int ) ; # not null primary key);

select table_name, engine 
from information_schema.tables 
where table_schema='test' and table_type='base table';


select 'generating 1 million records. ...' as info;
# generates 1 million records
# see this article for details
# http://datacharmer.blogspot.com/2007/12/data-from-nothing-solution-to-pop-quiz.html
create or replace view v3 as select null union all select null union all select null;
create or replace view v10 as select null from v3 a, v3 b union all select null;
create or replace view v1000 as select null from v10 a, v10 b, v10 c;
set @n = 0;
insert into t1 select @n:=@n+1 from v1000 a,v1000 b;

select partition_name, table_rows from information_schema . partitions where table_name='t1' and table_schema='test';

call compare_tables(0);

alter table t1 exchange partition p04 with table t2; 
call compare_tables(100000);

select partition_name, table_rows from information_schema . partitions where table_name='t1' and table_schema='test';

alter table t1 exchange partition p04 with table t2; 
call compare_tables(0);

alter table t1 exchange partition p04 with table t2; 
call compare_tables(100000);

Here is a test run:

$ ~/sandboxes/msb_5_6_99/use -t test -vvv < test_exch_part.sql 
--------------
set default_storage_engine=innodb
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
drop procedure if exists compare_tables
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
create procedure compare_tables (wanted int)
reads sql data
begin
    set @part_table     := (select count(*) from t1);
    set @non_part_table := (select count(*) from t2);
    select @part_table, @non_part_table, 
        if(@non_part_table = wanted, "OK", "error") as expected;
end
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
drop table if exists t1, t2
--------------

Query OK, 0 rows affected (0.07 sec)

--------------
create table t1 (i int) 
partition by range (i) 
    (
partition p01 values less than  (100001), 
partition p02 values less than  (200001), 
partition p03 values less than  (300001), 
partition p04 values less than  (400001), 
partition p05 values less than  (500001), 
partition p06 values less than  (600001), 
partition p07 values less than  (700001), 
partition p08 values less than  (800001), 
partition p09 values less than  (900001), 
partition p10 values less than (1000001), 
partition p11 values less than (maxvalue))
--------------

Query OK, 0 rows affected (0.08 sec)

--------------
create table t2 (i int )
--------------

Query OK, 0 rows affected (0.14 sec)

--------------
select table_name, engine 
from information_schema.tables 
where table_schema='test' and table_type='base table'
--------------

+------------+--------+
| table_name | engine |
+------------+--------+
| t1         | InnoDB |
| t2         | InnoDB |
+------------+--------+
2 rows in set (0.01 sec)

--------------
select 'generating 1 million records. ...' as info
--------------

+-----------------------------------+
| info                              |
+-----------------------------------+
| generating 1 million records. ... |
+-----------------------------------+
1 row in set (0.00 sec)

--------------
create or replace view v3 as select null union all select null union all select null
--------------

Query OK, 0 rows affected (0.12 sec)

--------------
create or replace view v10 as select null from v3 a, v3 b union all select null
--------------

Query OK, 0 rows affected (0.14 sec)

--------------
create or replace view v1000 as select null from v10 a, v10 b, v10 c
--------------

Query OK, 0 rows affected (0.09 sec)

--------------
set @n = 0
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t1 select @n:=@n+1 from v1000 a,v1000 b
--------------

Query OK, 1000000 rows affected (10.01 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

--------------
select partition_name, table_rows from information_schema . partitions where table_name='t1' and table_schema='test'
--------------

+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p01            |     100623 |
| p02            |     100623 |
| p03            |     100623 |
| p04            |     100623 |
| p05            |     100623 |
| p06            |     100623 |
| p07            |     100623 |
| p08            |     100623 |
| p09            |     100623 |
| p10            |     100623 |
| p11            |          0 |
+----------------+------------+
11 rows in set (0.01 sec)

--------------
call compare_tables(0)
--------------

+-------------+-----------------+----------+
| @part_table | @non_part_table | expected |
+-------------+-----------------+----------+
|     1000000 |               0 | OK       |
+-------------+-----------------+----------+
1 row in set (0.56 sec)

Query OK, 0 rows affected (0.56 sec)

--------------
alter table t1 exchange partition p04 with table t2
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
call compare_tables(100000)
--------------

+-------------+-----------------+----------+
| @part_table | @non_part_table | expected |
+-------------+-----------------+----------+
|      900000 |          100000 | OK       |
+-------------+-----------------+----------+
1 row in set (0.54 sec)

Query OK, 0 rows affected (0.54 sec)

--------------
select partition_name, table_rows from information_schema . partitions where table_name='t1' and table_schema='test'
--------------

+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p01            |     100623 |
| p02            |     100623 |
| p03            |     100623 |
| p04            |          0 |
| p05            |     100623 |
| p06            |     100623 |
| p07            |     100623 |
| p08            |     100623 |
| p09            |     100623 |
| p10            |      91799 |
| p11            |          0 |
+----------------+------------+
11 rows in set (0.01 sec)

--------------
alter table t1 exchange partition p04 with table t2
--------------

Query OK, 0 rows affected (0.05 sec)

--------------
call compare_tables(0)
--------------

+-------------+-----------------+----------+
| @part_table | @non_part_table | expected |
+-------------+-----------------+----------+
|     1000000 |               0 | OK       |
+-------------+-----------------+----------+
1 row in set (0.56 sec)

Query OK, 0 rows affected (0.56 sec)

--------------
alter table t1 exchange partition p04 with table t2
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
call compare_tables(100000)
--------------

+-------------+-----------------+----------+
| @part_table | @non_part_table | expected |
+-------------+-----------------+----------+
|      900000 |          100000 | OK       |
+-------------+-----------------+----------+
1 row in set (0.56 sec)

Query OK, 0 rows affected (0.56 sec)

Bye

Notice that the value for "table_rows" is only approximate with InnoDB, while it is reliable for MyISAM. Anyway, when it says that a partition has 0 records, it's reliable for any engine. Here you see that, after the exchange, partition p04 is empty.
The exchange is repeated twice, to make sure that it works both ways.
Notice also that, if the table contains data that doesn't fit with the partition, the server throws an error, and the exchange does not happen.

mysql > insert into t2 values (2000000);
Query OK, 1 row affected (0.00 sec)

mysql > alter table t1 exchange partition p04 with table t2;
ERROR 1697 (HY000): Found row that does not match the partition

If you remove the offending row from the table, the exchange works as expected.
PlanetMySQL Voting: Vote UP / Vote DOWN

To whom it may concern,

in response to a query from André Simões (also known as ITXpander), I slapped together a MySQL script that outputs mysqldump commands for backing up individual partitions of the tables in the current schema. The script is maintained as a snippet at MySQL Forge.

How it works

The script works by querying the information_schema.PARTITIONS system view to generate an appropriate expression for mysqldump's --where option. The generated command also redirects the output to a file with this name pattern:

<schema>.<table>.<partition-name>.sql

For example, for this table (taken from the MySQL reference manual):

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

the script generates the following commands:

mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) < 1960" test members > test.members.p0.sql
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) >= 1960 and  YEAR(joined) < 1970" test members > test.members.p1.sql
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) >= 1970 and  YEAR(joined) < 1980" test members > test.members.p2.sql
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) >= 1980 and  YEAR(joined) < 1990" test members > test.members.p3.sql
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) >= 1990 and  YEAR(joined) < 18446744073709551615" test members > test.members.p4.sql

Tip: in order to obtain directly executable output from the mysql command line tool, run the script with the --skip-column-names (or -N) option.

Features

Currently, the script supports the following partitioning methods:

• HASH


• LIST


• RANGE

Limitations

The LINEAR HASH method is currently not supported, but I may implement that in the future.

Currently I do not have plans to implement the KEY and LINEAR KEY partitioning methods, but I may reconsider if and when I have more information about the storage-engine specific partitioning methods used by these methods.

Finally, I should point out that querying the information_schema.PARTITIONS table is dog-slow. This may not be too big of an issue, however it is pretty annoying. If anybody has some tips to increase performance, please let me know.

Acknowledgements

Thanks to André for posing the problem. I had a fun hour of procrastination to implement this, and it made me read part of the MySQL reference manual on partitioning.

I also would like to thank Giuseppe Maxia (the Datacharmer) for providing valuable feedback. If you're interested in either partitioning or the mysql command line, you should visit his tutorials at the MySQL conference, april 12-15, 2010.
PlanetMySQL Voting: Vote UP / Vote DOWN