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

On Friday last week, after the intensive days of the conference, Ars Technica wrote and published a nice article about MariaDB including many of the messages we had been delivering during the conference, http://arstechnica.com/business/news/2012/04/mysql-founders-latest-mariadb-release-takes-enterprise-features-open-source.ars.

Last year, when it became clear that O’Reilly wasn’t going to arrange the MySQL user conference in the future, there was a lot of discussion on who should arrange it. In the end Percona was pretty fast informing everyone that they had booked the convention center in Santa Clara to arrange the conference this year. Now with the results to hand it’s easy to say that the conference was very well arranged. Great work Percona!

The MariaDB booth was located in the .Org section of the expo hall and we experienced a huge crowd, especially on the first day (Wednesday) of the conference. Our t-shirts were really popular and we could probably have handed out even double the amount of what we had with us. Unfortunately for those in attendance, we had to put some aside for our next upcoming event in Bellingham, WA, USA 28-29th of April. It’s the LinuxFest Northwest 2012, http://linuxfestnorthwest.org. We hope to see some of you there!

We released MariaDB 5.5.23 GA on Tuesday of the conference. Apparently people just loved this news and we’ve enjoyed double our usual download rates since then.

On the SkySQL MariaDB Solutions Day on Friday the 13th, the MySQL founders Monty and David started the day with a panel and the day continued with sessions on all kinds of MariaDB and MySQL related topics. Make sure you read SkySQL’s summary, http://www.skysql.com/blogs/jenwilbur/seal-you-next-year-successful-mysql-friday-13th-santa-clara.
SkySQL has also posted pictures of the event on https://www.facebook.com/skysql.

During the conference we had many interesting conversations with people and businesses that we haven’t had a chance to meet before who had migrated to MariaDB. I’m certain there will be even more of these discussions this year and next.

To stay up to date with MariaDB, add yourself to the MariaDB announce list, which informs mainly about new releases. Also add yourself to the MariaDB Facebook page to get even more MariaDB news. Sign up at http://mariadb.org.

PlanetMySQL Voting: Vote UP / Vote DOWN

The Percona Live MySQL Conference and Expo 2012 is over. Together with the SkySQL solutions day, it has kept me occupied for 4 full days, from early morning to late at night.

I have to say that I am pleased. The quality of the organization was very high, with a very good lineup of speakers and an excellent technical support.

As usual, I have learned a lot during this week, either directly, by attending talks, or indirectly, by meeting people who told me what was juicy at the talks that I had missed.And I have met new interesting people, and caught up with the people that I know already.

This conference was particularly intense also because I got myself involved in 5 talks, which was probably more than I should have. How did I end up with such a task? It's a long story.

It all started when the CfP opened. In the review committee, we all knew that Oracle was not eager to participate, but we hoped that it would change its mind and send someone in the end. So we planned ahead, and some of us proposed talks aimed at beginner and intermediate users, with topics that are usually best covered by the people who work at the MySQL team. I proposed Replication 101 and What's new in MySQL 5.5 and 5.6 replication, with the idea that I would hand them over to a couple of Oracle engineers, or have them as co-speakers. That, however, didn't happen. So I had to prepare and present these two talks, in addition to the one that I wanted to do on my own (Testing MySQL creatively in a sandbox).

That makes 3 talks. Then I got tasked with organizing the lightning Talks, which is not a big deal per se, but it adds to the global effort. 4 talks.

And finally, SkySQL organized another beautiful conference on Friday, and I got to present a fifth talk. I enjoyed every bit of them, but boy! the conference was intense!.

I have learned not only from the talks that I have attended, but also from the preparation of my own talks. The biggest source of surprises was my talk about MySQL 5.6 replication. I was expecting a mature release, but I found a collection of features that don't play very well together, and can sometimes lead to an unstable server. Since I was trying to get my demos working, rather than isolating the bugs, I didn't submit any reports, but I will come back to that version and do a more thorough analysis as soon as I catch up with my day-by-day work.

Speaking about demos, it's quite common for me to include a demo in a technical talk. First, because getting a demo done will make me better acquainted with the features that I am presenting, and also because a presentation with a demo conveys the idea of a mature and reliable product (or the idea that I, as the speaker, know what I am talking about). Either way, I know prepare a demo for every talk where I have sufficient time to show one, and sometimes even for a lightning talk. So it was surprising to hear comments that praised my talks because they contain demos. Is this practice so unusual? I should start taking count of how often this is done.

My most satisfactory demo (and the one that almost got me in trouble) happened at the last talk, on Friday, when I had to show features from three different Tungsten topologies, using three separate remote clusters. For these demos to be successful, I needed good internet connection, a solid confidence in the product and the strength of its tests, and to remember the sequence of operations for each demo. To my surprise, everything went so smoothly, that someone in the audience thought that I was running a simulation in my laptop, instead of interacting with servers that were 10,000 Km away. So much for my rehearsals! I must remember to add at least a tiny mistake in an otherwise perfect sequence of tasks, to make the audience aware that I am playing live.

The slides for my presentations are available at Slideshare.

PlanetMySQL Voting: Vote UP / Vote DOWN

We are pleased to announce the immediate availability of MariaDB 5.5.23. This stable (GA) release incorporates MariaDB 5.3.6 and MySQL 5.5.23, some performance improvements, and bug fixes.

• MariaDB 5.5.23 Release Notes
• MariaDB 5.5.23 Changelog
• MariaDB 5.5.23 Downloads

Please see the What is MariaDB 5.5 page for an overview of MariaDB 5.5.

Sources, binaries, and package downloads are available from our network of MariaDB mirrors. Debian and Ubuntu packages are available from our mirrored apt repositories. We have a sources.list generator for creating sources.list entries.

PlanetMySQL Voting: Vote UP / Vote DOWN

On March 21st I will be in Paris, to attend the OTN MySQL Developers Day.Oracle is organizing these events all over the world, and although the majority are in the US, some of them are touching the good old European continent.Previous events were an all-Oracle show. Recently, the MySQL Community team has been asking for cooperation from the community, and in such capacity I am also presenting at the event, on the topic of testing early releases of MySQL in a sandbox. Of course, this is one of my favorite topics, but it is quite appropriate in this period, when Oracle has released a whole lot of preview features in its MySQL Labs. Which is another favorite topic of mine, since I was the one who insisted for having the Labs when I was working in the community team. It's nice to see that the labs are still in place, and being put to good use.

Speaking of sandboxes, I was making some quick tests yesterday, and I installed 15 sandboxes at once (all different versions, from 5.0.91 to 5.6.5). Installing a single sandbox, depending on the version, takes from 5 to 19 seconds.Do you know how long it takes to install 15 sandboxes, completely, from tarball to working conditions? It takes 19 seconds. How's so? It's because I have been working at a large project where we are dealing with many replicated clusters spread across three continents. Administering these clusters is a problem in itself, and so we are using tools to do our work in parallel. At the same time, using a host with a fast 16 core CPU I can install many sandboxes at once. It's a real joy to see software behaving efficiently the way it should!It works so fast, in fact, that I found a race condition bug. If you install more than one sandbox at once, the MySQL bootstrap process may try to open the same temporary file from two different servers. That's because I did not indicate a dedicated temporary directory for the bootstrap (I was using one only for the installed sandbox). When this happens, you may find that instead of 15 sandboxes you have installed only 9 or 11. So I fixed the bug, by adding --tmpdir to mysql_install_db, and now you can install more than one sandbox in parallel.
PlanetMySQL Voting: Vote UP / Vote DOWN

On March 21st I will be in Paris, to attend the OTN MySQL Developers Day.Oracle is organizing these events all over the world, and although the majority are in the US, some of them are touching the good old European continent.Previous events were an all-Oracle show. Recently, the MySQL Community team has been asking for cooperation from the community, and in such capacity I am also presenting at the event, on the topic of testing early releases of MySQL in a sandbox. Of course, this is one of my favorite topics, but it is quite appropriate in this period, when Oracle has released a whole lot of preview features in its MySQL Labs. Which is another favorite topic of mine, since I was the one who insisted for having the Labs when I was working in the community team. It's nice to see that the labs are still in place, and being put to good use.

Speaking of sandboxes, I was making some quick tests yesterday, and I installed 15 sandboxes at once (all different versions, from 5.0.91 to 5.6.5). Installing a single sandbox, depending on the version, takes from 5 to 19 seconds.Do you know how long it takes to install 15 sandboxes, completely, from tarball to working conditions? It takes 19 seconds. How's so? It's because I have been working at a large project where we are dealing with many replicated clusters spread across three continents. Administering these clusters is a problem in itself, and so we are using tools to do our work in parallel. At the same time, using a host with a fast 16 core CPU I can install many sandboxes at once. It's a real joy to see software behaving efficiently the way it should!It works so fast, in fact, that I found a race condition bug. If you install more than one sandbox at once, the MySQL bootstrap process may try to open the same temporary file from two different servers. That's because I did not indicate a dedicated temporary directory for the bootstrap (I was using one only for the installed sandbox). When this happens, you may find that instead of 15 sandboxes you have installed only 9 or 11. So I fixed the bug, by adding --tmpdir to mysql_install_db, and now you can install more than one sandbox in parallel.
PlanetMySQL Voting: Vote UP / Vote DOWN

We are pleased to announce the immediate availability of MariaDB 5.5.20-alpha. MariaDB 5.5.20 is the first Alpha release in the 5.5 series. We hope to follow it up soon with a beta 5.5 release.

MariaDB 5.5.20-alpha is a merge of MariaDB 5.3 and MySQL 5.5 with some limited additional bug fixes. This is the first 5.5-based release, and we are releasing it now, intentionally without any extra features (and with it missing some planned features) to get it into the hands of any who might want to test it. Extra features planned for MariaDB 5.5 will be pushed into future releases.

As with any alpha release, MariaDB 5.5.20-alpha should not be used on production systems.

The Release Notes page has some notes on the release. There is also a Changelog available for those who are interested.

Sources, binaries and package downloads are available from our network of MariaDB mirrors. Debian and Ubuntu packages are available from our mirrored apt repositories. We have created a sources.list generator for creating sources.list entries.

About MariaDB 5.5

The MariaDB 5.5 series is the combination of MariaDB 5.3 and MySQL 5.5.

Please see the What is MariaDB 5.5 page for details.

PlanetMySQL Voting: Vote UP / Vote DOWN

I see that MySQL 5.5.21 has just been released. This sounds interesting. I’m mainly running 5.5.16 which has been broadly stable, but I have been caught by a few important issues which 5.5.21 fixes according to the change list:

• Bug #57480
• Bug #61104
• Bug #54035

These together with a few earlier fixes after 5.5.16 are certainly interesting to me, so I’m eager to try out 5.5.21 and see how it fairs.

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL, like a lot of other software, has many knobs you can tweak. Most of these knobs may affect behaviour, but more importantly most affect the memory usage of the server, so getting these settings right is very important.

Most of MySQL’s memory is really used just as a cache, in one form or another, information that otherwise is on disk. So ensuring you have as large a cache as possible is important. However, making these memory sizes too large will trigger the server to start swapping and possibly can cause it to crash or cause the kernel to kill the process when it runs out of memory.  So that’s something we want to avoid.

Certain settings affect memory allocation on a per connection/thread basis, being bounded by thread_cache_size and max_connections.  If you configure for the worst behaviour (max_connections) you may end up not actually using all the memory you have available, memory which normally could be used for other purposes.

Recently a server I managed was configured incorrectly with a large sort_buffer_size (4M to 256M) and larger read_buffer_size (4M to 20M).  The change in configuration on first glance looks quite innocent, and not noticing that these are per-connection settings this got rolled out. max_connections on this server was set to 1000, while normally there were ~40 connections of which only a few were active. The mysqld memory footprint on startup looked fine. In fact under normal usage it also worked fine. However spiky load suddenly changed this nice behaviour: as configured mysqld ramped up the thread count and hence memory usage, resulting in swapping and finally server death…

The fault of course was mine for not noticing, but this has been an issue with MySQL forever.  Most other RDBMSes manage memory slightly differently: you define how much memory you want to use (maximum), this is optinally locked into memory, and further requests for different buffers are taken from this global pool.  That is much safer, avoids unexpected swapping, or memory over-allocation, but does raise the question of what happens when a memory request can not be honoured.

Initially I would expect two different behaviours: either

1. kill a thread whose memory can not be allocated, or
2. wait a certain time to allocate that memory, after which it gets killed anyway.

Option (2) is probably saner, and possibly some sort of deadlock detection can kick if in all threads are waiting for memory, perhaps killing the younger thread, or thread which has done least work first. Possibly there are other better ways of doing this?

I can imagine that changing MySQL’s current behaviour to do something like this could be quite hard, especially as ideally the engines would also use the same memory management mechanisms, but I see this as being a good thing and would make MySQL more robust, especially under load, which is after all what counts.  Of course this will not happen in today’s 5.5 GA version, or tomorrow’s 5.6 version which is probably likely to appear some time this year. That’s a major change. It would be nice if Oracle look at this for 5.7 as a way of ensuring that when resource usage does come under pressure MySQL does not go heads up, but attempts to use the allocated resources as best as possible.

In the meantime what would help would be:

• better documentation so we can see clearly how all mysql memory is allocated. There are several web pages commenting ways to calculate this, but certainly no definitive guide.
• The InnoDB engine’s documentation talks about memory usage and most people think that the innodb_buffer_pool_size is the main setting. yet read further and there’s talk of an overhead of perhaps 1/8th. I have recently been playing with innodb_buffer_pool_instances settings > 1 (using values in the range of 20-40) and am inclined to think that this increases that overhead somewhat more, yet there’s no documentation on this and whether my guess is right or not. Please InnoDB developers improve your documentation, if only to prove me wrong.
• Ideally some tools to tell you if you server is possibly misconfigured. Coming from a Sybase environment I’d be tempted to suggest a stored procedure in the mysql database which can tell you total memory usage and how it is broken down as doing this with a single SELECT is going to be tricky.

Then once that is done consider adding some extra variable to enable total memory usage to be controlled. I made a feature request for this at http://bugs.mysql.com/?id=64108. If you think this feature might interest you please let Oracle know.

PlanetMySQL Voting: Vote UP / Vote DOWN

For those who saw my previous post about the crashing (regression) bug with SELECT COUNT(DISTINCT) on InnoDB with Primary Key (PK), you’ll be interested to know my test case does not crash in 5.5.18 (which was just released).

I’ve only tested my test case thus far, but it seems fine.

Unfortunately, the fix is not mentioned in the 5.5.18 changelogs though.

And there is no mention (yet, anyway) of a fix in the bug report I filed (though it was designated a ‘duplicate’, so it wouldn’t necessarily be updated).

I’m trying to get confirmation from the MySQL Dev Team on this (via the bug report), and will update this post if/when I hear anything.

I’ll also perform some of the other tests on my end to see if they all pass as well.

All in all, at least the initial results look very promising!

PlanetMySQL Voting: Vote UP / Vote DOWN

In 5.5, a crashing, regression bug exists if you use SELECT COUNT(DISTINCT) *and* one of the WHERE operands is in the Primary Key (or just a unique index).

This simple crash (if only one row is in the table) will crash mysqld.

Of course I’ve filed a bug report, but that has been nearly 3 months and no updates yet.

Here is the bug I filed (which you won’t be able to view):

http://bugs.mysql.com/bug.php?id=61842

Really, the only thing that happened to my bug report was that it was designated a duplicate of another bug (which we also cannot view):

http://bugs.mysql.com/bug.php?id=61101

Based on the id, and the submitted dates of bugs 61100 and 61102, this initial bug (61101) was filed on May 9, 2011. So, in fact, this bug has been present for over 5 months, and not one breath of an update to the public!

Therefore, I felt it necessary to warn others about this bug, (or possibly you’ll run across this if you’re searching on SELECT COUNT(DISTINCT) in the future).

All I can say is please watch out for it!

It is extremely easy to reproduce:

CREATE TABLE t (a int(1), b int(1), PRIMARY KEY (a,b)) ENGINE=InnoDB;
INSERT INTO t VALUES (1, 1);
SELECT COUNT(DISTINCT a) FROM t WHERE b = 1;

–> crash <--

For those interested, this was filed against 5.5.14. However, with each new release, I've continued testing, and this bug is present in 5.5.15, 5.5.16, and thus far in 5.5.17 (built from the latest bzr tree).

Hopefully we don't go too many more months before this is finally fixed.

And for reference (and those searching on the stack trace / error log messages), here is my full error log snippet from 5.5.16:

111017 10:54:47 [Note] C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections.
Version: ’5.5.16′  socket: ”  port: 3308  MySQL Community Server (GPL)
 len 128; hex f8aec9037d803805f017fc03189ddc030000000…
111017 10:55:12  InnoDB: Assertion failure in thread 5000 in file btr0pcur.c line 236
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
111017 10:55:12 – mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=26214400
read_buffer_size=65536
max_used_connections=1
max_threads=100
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 58325 K
bytes of memory
Hope that’s ok; if not, decrease some variables in the equation.

Thread pointer: 0x3c98428
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
00CE92EC    mysqld.exe!btr_pcur_restore_position_func()[btr0pcur.c:236]
00CA62FB    mysqld.exe!sel_restore_position_for_mysql()[row0sel.c:3081]
00CA6CEA    mysqld.exe!row_search_for_mysql()[row0sel.c:3820]
00C5FE20    mysqld.exe!ha_innobase::general_fetch()[ha_innodb.cc:5918]
00C5FEDD    mysqld.exe!ha_innobase::index_next()[ha_innodb.cc:5956]
00C20DDA    mysqld.exe!index_next_different()[opt_range.cc:11038]
00C249BC    mysqld.exe!QUICK_GROUP_MIN_MAX_SELECT::next_prefix()[opt_range.cc:11099]
00C26BE7    mysqld.exe!QUICK_GROUP_MIN_MAX_SELECT::get_next()[opt_range.cc:10824]
00B68D01    mysqld.exe!rr_quick()[records.cc:344]
00BC1B9A    mysqld.exe!sub_select()[sql_select.cc:11723]
00BD10A7    mysqld.exe!do_select()[sql_select.cc:11483]
00BD37BD    mysqld.exe!JOIN::exec()[sql_select.cc:2370]
00BD3A29    mysqld.exe!mysql_select()[sql_select.cc:2581]
00BD3D4B    mysqld.exe!handle_select()[sql_select.cc:297]
00ACD76E    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4511]
00ACF816    mysqld.exe!mysql_execute_command()[sql_parse.cc:2118]
00AD2D1F    mysqld.exe!mysql_parse()[sql_parse.cc:5548]
00AD3848    mysqld.exe!dispatch_command()[sql_parse.cc:1037]
00AD43BB    mysqld.exe!do_command()[sql_parse.cc:771]
00AF2DB6    mysqld.exe!do_handle_one_connection()[sql_connect.cc:789]
00AF2F44    mysqld.exe!handle_one_connection()[sql_connect.cc:708]
00C33DE4    mysqld.exe!pthread_start()[my_winthread.c:61]
00D9C6F3    mysqld.exe!_callthreadstartex()[threadex.c:348]
00D9C79B    mysqld.exe!_threadstartex()[threadex.c:326]
765F3823    kernel32.dll!BaseThreadInitThunk()
77CAA9BD    ntdll.dll!LdrInitializeThunk()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (03DC0F10): SELECT COUNT(DISTINCT a) FROM t WHERE b = 1
Connection ID (thread ID): 1
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
InnoDB: Thread 5980 stopped in file os0sync.c line 781
InnoDB: Thread 6820 stopped in file os0sync.c line 474
InnoDB: Thread 7532 stopped in file os0sync.c line 474

PlanetMySQL Voting: Vote UP / Vote DOWN