A special extended edition of Tech Messages for 2010-04-15 through 2010-04-24:
A week or so ago at the MySQL conference, I visited one of the backup vendors in the Expo Hall. I started to chat with them about their MySQL backup product. One of the representatives told me that their backup product uses FLUSH TABLES WITH READ LOCK, which he admitted takes a global lock on the whole database server. However, he proudly told me that it only takes a lock for "a couple of milliseconds." This is a harmful misconception that many backup vendors seem to hold dear.
The truth is, this command can take a lock for an indeterminate amount of time. It might complete in milliseconds on a test system in a laboratory, but I have seen it take an extremely long time on production systems, measured in many minutes, or potentially even hours. And during this time, the server will get completely blocked (not just read-only!) To understand why, let's look at what this command actually does. There are several important parts of processing …
[Read more]
just managed to run the first "to-many" join inside the
data-nodes.
the query correspondce to
SELECT t1.*, t2.*
FROM T1 t1
LEFT OUTER JOIN T1 as t2 on t2.pk >= t1.b
- the code inside the data-nodes is general...but incomplete
(data-node later crashed)
- there is no ndbapi and no SQL,the program testing is a
hard-coded c-program sending messages using the ndb-cluster
wire-protocol
- the result-set is not managed (the c-program just hangs)
- hopefully converting this into usable code will be faster than
last years post as ndbapi is now quite complete, and this
is "only" an add-on.
for a bit more background look at my …
Latest code of MySQL Connector/Python on launchpad has support for multiple result sets when you execute a stored procedure. We also changed the way the actual result of the routine is returned to conform to PEP249.
Here is some example code: it creates a stored procedure which generates 2 result sets. You can get the result by calling next_resultset(), which is returning a MySQLCursorBuffered.
cur = cnx.cursor()
cur.execute("DROP PROCEDURE IF EXISTS multi")
proc = """
CREATE PROCEDURE multi(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)
BEGIN
SELECT 1,'a' as FooBar;
SELECT 2;
SET pProd := pFac1 * pFac2;
END""" …[Read more]
Sheeri has posted an awesome list of talks with links to videos and slides from the MySQL conference. She did a lot of the video recording herself. I don’t know how she could have posted them so fast — it’s a lot of work.
Related posts:
The O’Reilly MySQL Conference & Expo 2010 is over. I hope all of you had a good time. I have plenty of blog posts and thoughts lined up about this, but first, I’d like to point out something that has become a tradition, that was continued in 2010: the O’Reilly MySQL Conference Community Award Winners.
Tim O’Reilly was kind enough to hand out the awards this year. In case people were wondering, the awards were pewter wine goblets from Royal Selangor.
Selection of the award winners happened via voting from the alumni of winners, and was all done in a rather short period of time. Kudos to the entire team that voted. Now for …
[Read more]
I did two talks on replication and clustering at the recent
MySQL
Conference in Santa Clara. Thanks to all of you who
attended as well as the fine O'Reilly folks who organized
everything. Slides are posted on the talk descriptions at
the following URLs:
Conferences like the MySQL UC are fun because you get to see all your virtual pals …
[Read more]Sheeri has posted an awesome list of talks with links to videos and slides from the MySQL conference. She did a lot of the video recording herself. I don’t know how she could have posted them so fast – it’s a lot of work.
I will try to tell you how we reduced mutex contention on LOCK_open. Corrections are welcome. This code is complex and in flux from 5.0 to 5.5. This change is in the Facebook patch for MySQL 5.1.
Table handler instances are needed for each table used in a query. They are either fetched from a cache or opened at the start of a query and then either closed or returned to the cache at the end of the query. Some of the work done to open a table handler instance is serialized. This is done by locking the LOCK_open mutex for the duration of the call to the handler::open method. Sometimes a lot of work is done during the call to handler::open. Index cardinality statistics must be computed the first time a table is opened for InnoDB tables as it does not store statistics on disk. Cardinality statistics are computed by sampling 8 leaf blocks …
[Read more]This is the first part of an ongoing series about my experiences while writing the MySQL Admin Cookbook for Packt Publishing. All parts can be found under the mysql-admin-cookbook label.
In a few past posts I wrote about different aspects of my book writing project already. However those posts were not very contiguous, and I was asked a few times what my general experiences were and what caveats to look out for when taking on such a project for the first time. It might be of interest to some if I write down my experiences in a little more structured form.
This will be a series of several posts, because for one I do not think it would be feasible to …
[Read more]