This week we present using Puppet to manage MySQL. Ear Candy is netcat and At the Movies is a video about the Master High Availability toolkit by Yoshinori Matsunobu.

Feedback
Daniël van Eeden gave us lots of great feedback this week:
Episode 93, which included iotop and ionice in ear candy
CFQ scheduler for Linux
noop scheduler for Linux
deadline scheduler for Linux

read more

When I first started using MySQL, I was happy to see that MySQL had the familiar SYSDATE() function that I'd been using for years in Oracle. I wrote a bunch of code using SYSDATE() in MySQL, and then one day I noticed that my slave database sometimes had higher timestamp values than the master database for the same rows. Upon closer investigation, it turned out that SYSDATE() was the problem. As stated in the MySQL Reference Manual:

In addition, the SET TIMESTAMP statement affects the value returned by NOW() but not by SYSDATE(). This means that timestamp settings in the binary log have no effect on invocations of SYSDATE().

Essentially, this means that if you use SYSDATE() in a DML statement that is replicated, the slave gets the timestamp of when the slave executed the DML, not when the master executed the …

When I first started using MySQL, I was happy to see that MySQL had the familiar SYSDATE() function that I'd been using for years in Oracle. I wrote a bunch of code using SYSDATE() in MySQL, and then one day I noticed that my slave database sometimes had higher timestamp values than the master database for the same rows. Upon closer investigation, it turned out that SYSDATE() was the problem. As stated in the MySQL Reference Manual:

In addition, the SET TIMESTAMP statement affects the value returned by NOW() but not by SYSDATE(). This means that timestamp settings in the binary log have no effect on invocations of SYSDATE().

Essentially, this means that if you use SYSDATE() in a DML statement that is replicated, the slave gets the timestamp of when the slave executed the DML, not when the master executed the …

The Brief
The Client’s mystery shopping programme consists of visits to each of the client’s ten locations, grouped into five different areas. Each location receives a single visit each month, but on occasion a visit may not take place.
On the completion of each visit, a questionnaire is completed by the mystery shopper that is used to rate the service they received during the visit. The completed questionnaire is assigned a score, which is calculated based on the number of points achieved over the number of points available, normally expressed as a percentage.
Points Achieved x 100 Points Available
= % Score
PHP Development Test
The Client has requested that an end of year report is generated to summarise the data collected over the previous year. The report should be easy to understand and show as much useful information as possible.
Test Requirements
A PHP application should be …

MySQL 5.5.25a has been released with the fix for the bug http://bugs.mysql.com/bug.php?id=65745. This bug – introduced in 5.5.25 – could ultimately have the result that a single UPDATE statement made all disk space on the system unavailable. The InnoDB tablespace(s) grow(s)  even though data and indexes do not require more disk space.

This is of course an ugly bug – but ‘s… happens‘ they say. So it is not the purpose of this to ridicule or expose anyone. After all it was fixed  as soon as possible after the bug was reported. But after the fix has been released it is still unclear what exact conditions will trigger the effect.

Kolbe Kegel from SkySQL who reported the bug report, provided this CREATE TABLE statement (the simplest he could find to reproduce the issue)

…

The database team has been pretty busy, since half of it (me!) has been traveling. We will get to that later, but first, I have an important news item to share with you: Bassel Khartabil, an open source developer and a Creative Commons volunteer is being detained in Syria, with no explanation why. His family, including his fiancee, have gotten no reason why he has been detained for over four months. Please read more and sign a petition to free Bassel so he can come home to his family and continue his important volunteer contributions to the open source movement.

In happier news, I mentioned travel. Where I spoke and my slides are listed here:

• At the 1st Latin American Conference on MySQL, NoSQL and the Cloud I co-taught a tutorial on Monitoring MySQL ( …

We are pleased to announce that MySQL Enterprise Monitor 2.3.11 is now available for download on the My Oracle Support (MOS) web site. It will also be available via the Oracle Software Delivery Cloud in approximately 1-2 weeks. This is a maintenance release that contains several new features and fixes a number of bugs. You can find more information on the contents of this release in the changelog:

http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-news-2-3-11.html

You will find binaries for the new release on My Oracle Support:

https://support.oracle.com

Choose the "Patches & Updates" tab, and then use the "Product or Family (Advanced Search)" feature.

And from the Oracle Software Delivery Cloud (in about 1-2 …

Read the original article at A CTO Must Never Do This…

A couple years back I was contacted to look at a very strange problem.

The firm ran flash sales. An email goes out at noon, the website traffic explodes for a couple of hours, then settles back down to a trickle.

Of course you might imagine where this is going. During that peak, the MySQL database was brought to its knees. I was asked to do analysis during this peak load, and identify and fix problems. Make it go faster, please!

First day on the job I’m working with a team of outsourced DBAs. I was also working with a sort of swat team chatting on SKYPE, while monitoring the systems closely.

…

MySQL server intensively uses dynamic memory allocation so a good choice of memory allocator is quite important for the proper utilization of CPU/RAM resources. Efficient memory allocator should help to improve scalability, increase throughput and keep memory footprint under the control. In this post I’m going to check impact of several memory allocators on the performance/scalability of MySQL server in the read-only workloads.

For my testing i chose following allocators: lockless, jemalloc-2.2.5, jemalloc-3.0, tcmalloc(gperftools-2.0), glibc-2.12.1(new malloc)(CentOS 6.2), glibc-2.13(old malloc), glibc-2.13(new malloc), glibc-2.15(new malloc).

Let me clarify a bit about malloc …

In my last post, I described a specific problem with prepared statements into which PERFORMANCE_SCHEMA can give visibility.  That made me wonder whether PERFORMANCE_SCHEMA can also be used to identify other areas where prepared statements run into problems.  The most significant problem tends to be leakage of prepared statements.  This can inflate memory usage, both on the server and application side, and it’s not uncommon to find applications which fail to close prepared statements.

So the question is, what can PERFORMANCE_SCHEMA tell us about how connections close (or more importantly, fail to close) prepared statements?

At the most basic level, one can check the number of PREPARE statements executed compared to DEALLOCATE PREPARE, and you can do that using global status variables.  You’re shooting for general equality between (Com_prepare_sql + Com_stmt_prepare) and (Com_stmt_close + …