Archive

Archive for the ‘Admin’ Category

MySQL Connect 2013 Slides are available on [Plus]

October 28th, 2013 No comments

Hi,

You can find below the slides of the MySQL Connect 2013, enjoy!
(and much more in the slides page)

Categories: Admin

Why I do what I do?

May 20th, 2013 2 comments

I was sincerely affected by this last MySQL post and this other very long post from Jeremy Cole.
Yes, these two guys are MySQL rock stars and they are really impressives, their involvement in the MySQL community is utter!

I don’t want to write a long long speech about my simple life…
I just want to clarify why I do what I do.

Many people have asked or wondered without asking why I do what I do
(Jeremy Cole - 2013)

A few years ago Ashley Unitt asked me what I was most proud of, and now, I can make a complete answer. I’m very proud to take part of a community, MySQL has transformed my job into a passion and an incredible desire to share this passion, as honestly as possible.

What does it mean in real life?

If I can help in decision making or take part of a discussion, without any bad motivations, independently, I do it!

If the opportunity arises to have a good time over a glass of rosé and discussing open source with Stéphane Varoqui, I do it!

If I have suddenly an idea and a friend with me for several nights and weekends to achieve it, oh yes, I do it!

I do it because it’s terribly exciting and fun, even if I’ve just did it for myself… on a whim.

Yes, I’m very proud when we made MYXPLAIN.net with Max, I’m proud to work for an amazing company, I’m proud to advise someone who challenge me from the other side of the world, I’m proud to read an email from Rick James in my inbox…

I’m proud to be honest!

Categories: Admin

An incomplete list of what your developers would like to know before migrating to MySQL 5.5

April 21st, 2013 No comments

A few years ago, I asked to check with me in the long (very long) change history of MySQL 5.5 documentation what are the changes in relation to the SQL syntax.
Chris Calender helped me to retrieve a list of the main changes, thanks again Chris.

Today, I would like to share this list with you.
It is simply a curated transcript of what you might find in the documentation but I’m sure it can help some of you.
 

INTO clause in nested SELECT statements

 
Previously, the parser accepted an INTO clause in nested SELECT statements, which is invalid because such statements must return their results to the outer context. As of MySQL 5.5.3, this syntax is no longer permitted and statements that use it must be changed.
 

Table aliases in DELETE statements

 
In MySQL 5.5.3, several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases.

In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for the USING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table.

As of MySQL 5.5.3, alias declarations outside table_references are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the table_references part.

Incorrect:

  • DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2;
  • DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2;

Correct:

  • DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2;
  • DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2;

Previously, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is db1, the following statement does not work because the unqualified alias reference a2 is interpreted as having a database of db1:

  • DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id;

To correctly match an alias that refers to a table outside the default database, you must explicitly qualify the reference with the name of the proper database:

  • DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id;

As of MySQL 5.5.3, alias resolution does not require qualification and alias references should not be qualified with the database name. Qualified names are interpreted as referring to tables, not aliases.

Statements containing alias constructs that are no longer permitted must be rewritten.
 

New reserved words

 
There are several new reserved words in 5.5 what were not reserved in 5.1.
So if you are using any of these as table names, etc., then you’ll need to surround them with backticks (`)

  •  GENERAL
  • IGNORE_SERVER_IDS
  • MASTER_HEARTBEAT_PERIOD
  • MAXVALUE
  • RESIGNAL
  • SIGNAL
  • SLOW

 

FLUSH TABLES and LOCK TABLES

 
As of MySQL 5.5.3, due to work done for Bug #989, FLUSH TABLES is not permitted when there is an active LOCK TABLES … READ.
To provide a workaround for this restriction, FLUSH TABLES has a new variant, FLUSH TABLES tbl_list WITH READ LOCK, that enables tables to be flushed and locked in a single operation.
As a result of this change, applications that previously used this statement sequence to lock and flush tables will fail:

  • LOCK TABLES tbl_list READ;
  • FLUSH TABLES tbl_list;

Such applications should now use this statement instead:

  •  FLUSH TABLES tbl_list WITH READ LOCK;

 

Fast truncation

 
As of MySQL 5.5.7, InnoDB always uses the fast truncation technique, equivalent to DROP TABLE and CREATE TABLE.
It no longer performs a row-by-row delete for tables with parent-child foreign key relationships.
TRUNCATE TABLE returns an error for such tables.
Modify your SQL to issue DELETE FROM table_name for such tables instead.
 

TIMESTAMP display width

 
In very old versions of MySQL (prior to 4.1), the TIMESTAMP data type supported a display width, which was silently ignored beginning with MySQL 4.1.
This is deprecated in MySQL 5.1, and removed altogether in MySQL 5.5.

The display width is no longer supporter for TIMESTAMP data type in MySQL 5.5
 

Aliases and wildcards

 
Aliases for wildcards (as in SELECT t.* AS 'alias' FROM t) are no longer accepted and result in an error.
Previously, such aliases were ignored silently. (Bug #27249)
 

Scientific notation

 
In addition to these official parts of the MySQL release notes, Sheeri write a post about floats, doubles ans scientific notation between MySQL 5.1 and MySQL 5.5 :

http://blog.mozilla.org/it/2013/01/17/mysql-5-1-vs-mysql-5-5-floats-doubles-and-scientific-notation/

 

This list is incomplete because I’m sure you have your own tips to share.

Source : http://dev.mysql.com/doc/relnotes/mysql/5.5/en/

Categories: Admin, Live !, Tips

The strange commit behavior and the invisible Xid_log_event

April 15th, 2013 No comments

Did you see this when you are migrating from your lovely MySQL 5.1 to MySQL 5.5?
Oh, sorry, you remain attached to your pretty 4.1. Yes, I know, MyISAM has become so important in your life…

Ok, seriously, I would like to share this little observation I made recently when switching to MySQL 5.5 on one slave.
You can see below two graphs for the transactional activity, there is exactly the same volume of update, delete and insert queries :

MySQL 5.1

Screenshot 2013-04-15 at 06.34.23 PM

MySQL 5.5Screenshot 2013-04-15 at 06.32.42 PM

But the gray area represents the number of commit per second.
I find that I have much more commit with MySQL 5.5, why?
The first question I asked to myself was whether MySQL was telling the truth…

And I had three options to know the truth :

  • The com_commit counter : I compared the number of begin and commit during ten minutes
    • MySQL 5.1 : I got much more begin than commit
    • MySQL 5.5 : I got exactly the same result between begin and commit
  • The relay logs : In a sample of logs, I compared the number of begin and commit 
    • MySQL 5.1 : I got exactly the same result between begin and commit
    • MySQL 5.5 : I got exactly the same result between begin and commit
  • The genaral log : I compared the number of begin and commit during ten minutes
    • MySQL 5.1 : I got exactly the same result between begin and commit
    • MySQL 5.5 : I got exactly the same result between begin and commit

Hum, ok, the com_commit counter want to play with me!

Let’s go in the general log, I had two kinds of commit output :

Query COMMIT
Query COMMIT /* implicit, from Xid_log_event */

So, I called a friend : Hey Google, what is this (f…) Xid_log_event?

http://ftp.nchu.edu.tw/MySQL/sources/doxygen/mysql-5.1/classXid__log__event.html 
http://bugs.mysql.com/bug.php?id=54605

Hum, it smells replication…

Yes, you guessed it, the commit statements from the replication stream are not recorded in the com_commit counter in 5.1.
But the begin statements are recorded in the com_begin counter…

Look at your own slave servers and compare!

 

Categories: Admin

An incomplete guide to linux system configuration for MySQL

April 11th, 2013 3 comments

As a former Oracle DBA, I know how the system and the database are linked.
The first one who told me that the installation of Oracle has never been a problem is a liar!
Yes, your database and your system are the best of friends, you must respect that.

I’d make a list of linux system settings to configure a MySQL databases server and share my sources with you.
In return, I would like you to share your sources with the community by publishing your tips in the comments.

swappiness

 

  • This parameter allows to specify how the kernel must manage the memory swap
  • Default value : 60 (Range 0 to 100)
  • Value to set : 0 (it will swap only to avoid an out of memory condition)
  • How to set a new non-persistent value :  sysctl -w vm.swappiness=0
  • How to store a new persistent value : add vm.swappiness=0 in the /etc/sysctl.conf file

 

I/O Scheduler

 

  • The I/O scheduler manages and optimizes priorities of I/O requests
  • Default value : device driver dependent
  • Range of values : noop / deadline / anticipatory / cfq
  • Value to set : noop
  • How to set a new persistent value : echo noop >/sys/block/[DEVICE]/queue/scheduler

 

Mounting options

 

  • These options are set during the filesystem mount process
  • Default value : rw
  • Value to set ext4 : rw,nosuid,noatime,data=ordered
  • Value to set xfs : nobarrier
  • How to store a new persistent value : add these options in the /etc/fstab file

 

I/O queue size

 

  • This queue stores incoming I/O requests
  • Default value : 128 (No range)
  • Value to set : Hey Dude, you have to test, no magic value for this parameter
  • How to set a new persistent value :  echo 100000 > /sys/block/[DEVICE]/queue/nr_requests

 

Dirty ratio

 

  • These settings are used to tune the VM subsystem about dirty data
  • Default value : 10 and 20
  • Value to set : Again, you should test. You can try 5 and 60 to begin
  • How to set a new non-persistent value :
    • echo 5 > /proc/sys/vm/dirty_background_ratio
    • echo 60 > /proc/sys/vm/dirty_ratio
  • How to store a new persistent value : Add these parameters to the /etc/sysctl.conf file
    • vm.dirty_background_ratio = 5
    • vm.dirty_ratio = 60

NUMA Architecture

 

Note that all of these tips focus on optimizing I/O.
The first three tips can be considered as really interesting.
I recommend you to read the sources before any change.

Remember to test every changes before to put them in production.

Now, let us know how you setup your system for MySQL.

Sources :
http://en.wikipedia.org/wiki/Swappiness
http://yoshinorimatsunobu.blogspot.fr/2009/04/linux-io-scheduler-queue-size-and.html
http://rackerhacker.com/2008/08/07/reduce-disk-io-for-small-reads-using-memory
http://assets.en.oreilly.com/1/event/21/Mastering%20the%20Art%20of%20Indexing%20Presentation.pdf
http://cdn.oreillystatic.com/en/assets/1/event/56/Linux%20and%20H_W%20optimizations%20for%20MySQL%20Presentation.pdf
http://www.mysqlperformanceblog.com/2013/01/03/is-there-a-room-for-more-mysql-io-optimization/
http://www.percona.com/files/presentations/UC2011-MySQL-and-SSD-Usage-Patterns.pdf
http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/
http://blog.jcole.us/2012/04/16/a-brief-update-on-numa-and-mysql/

Categories: Admin, Live !

Does MariaDB kill the magic query?!

February 27th, 2013 5 comments

I’m sure that many of you use the magic query from Shlomi.
This query retrieves the difference between two status values with a single query.
I’m really fan of this kind of query and about this query in particular. This query is very useful to retrieve an information without to have to store a middle result.

Now, I tried this query on a MariaDB 5.5 server and I had a surprise :

Magic_query

The result was a bit strange, with negative or null values.

I’ve just had these server for few hours in my hands and I had no more time to analyze the situation.
I assume that the subquery optimisations in MariaDB can be involve here but it’s just an assumption.

Does anyone encountered the same issue? (with MariaDB or other distro)

Categories: Admin

FOSDEM 2013 Slides are available on [Plus]

February 26th, 2013 No comments

I was at FOSDEM for the first time this year and it was very exciting.
I was very glad to see Percona, MariaDB and Oracle together.
If you’ve never been at FOSDEM, I recommend you to join us next year, this is truly a magical event!

You can find some of the slides on [Plus], enjoy! :

If you would like to add your slides, post a comment with the link.
And remember to visit this page for more slides about MySQL : http://www.mysqlplus.net/mysql-docs/

Categories: Admin

Don’t play with innodb_io_capacity! (with standard HDDs)

January 7th, 2013 13 comments

A beautiful picture is sometimes better than words :

With standard HDDs (here in RAID 10), the innodb_io_capacity variable may have non expected effects.

You can see on this picture the result of a test on one of my server with only the replication thread activated. The value of the innodb_io_capacity variable was the single modification during the period.

So, don’t touch this parameter without a strong reason…
(Of course 30000 was a bit snooty)

[MAJ 2013-01-08] : I know 30k is a too high value for innodb_io_capacity, I used this value to make the graphe really eloquent.
Read this excellent post from Chris for more details : http://www.chriscalender.com/?p=201

Categories: Admin

And a happy new year!

January 6th, 2013 No comments

I wish you all my best wishes for this new year.
I hope 2013 will be as exciting as 2012 for the MySQL community.

Thank you for spending time on MySQL[Plus] in 2012, I hope to have more and more readers in 2013.

If you would like to read (or read back) my posts published in 2012, here is the summary :

And all the most useful slides of 2012 are also on [Plus] :  http://www.mysqlplus.net/mysql-docs/

Don’t forget to follow me on twitter for the latest news about the MySQL ecosystem :


And if you didn’t discover MyXplain before, please, take a look : http://myxplain.net

Happy new year again on [Plus]

Categories: Admin

MySQL 101 : Numeric type attributes and maximum value

November 20th, 2012 4 comments

I would like to make a quick MySQL 101 about numeric types in MySQL and especially explain the difference between the numeric type attributes and the maximum value that a numeric type can store.

Someone asked me yesterday why when he inserts a value in a table, the value 127 appears instead of the value 160 that he’s just inserted.
Ok, let me explain, consider this table :

What happens if you want to insert this row in the table :

sql > insert into product values (160, 554, "iPad mini");
Query OK, 1 row affected, 1 warning (0.01 sec)
sql > show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'id' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

sql > select * from product;
+-----+----------+-----------+
| id  | quantity | label     |
+-----+----------+-----------+
| 127 | 554      | iPad mini |
+-----+----------+-----------+
1 row in set (0.00 sec)

The result of the insert command suggest that a warning was detected during the insert process.
The warning message indicates that the value we wanted to insert in the id column was too high. There isn’t any mention of the quantity column.
And when I wanted to display the contents of the table, the value 127 appears instead of the value 160 previously inserted.

  • Value 160 inserted in a tinyint(8) column => 127 was stored in the table
  • Value 554 inserted in a int(200) column => 554 was stored in the table

The two values we wanted to insert for the id and the quantity columns were more important than the value of the type attribute.
But why there wasn’t any error for the quantity column ?

Simply because that attribute is not used to determine the maximum value that a numeric type can store.
The numeric type attribute may be used only by applications, not by MySQL.
The width of this attribute is present in the metadata returned with result sets of the query.

In our example, there was a warning for the id column and the data was truncated because the maximum value that a signed tinyint can store is 127.
Take a look at this table that shows the maximum values for each numeric types :
(Source : Official MySQL documentation)

You can see that the maximum value for a signed int is much larger than the value that we wanted to insert in the quantity column.

This question comes up frequently, hope this post can help.

Categories: Admin