Alex Stetsenko

PROCEDURE ANALYSE examines the result from a query and returns an analysis of the results that suggests optimal data types for each column that may help reduce table sizes. Simply append PROCEDURE ANALYSE to the end of a select statement:

SELECT f1, f2 FROM t PROCEDURE ANALYSE(20, 2000);

where

• 10 is is the maximum number of distinct values which is checked for columns;
• 2000 is the maximum amount of memory which can be allocated per column while trying to find all distinct values.

This MySQL feature can be helpful after importing new data or for checking your existing tables to verify whether columns datatype was designed providently.

When designing partitions keep in mind that it will be required to have the number of partitions and open file limit system variable in harmony.

Thus each partition creates an overhead of extra two files (#p.myd and #p.myi). I.e. partition by hash of month will produce 2 * 12 months = 24 extra files. Not too bad for the default MySQL settings. However if there is plan for much more partitions, the open file limit has to be tuned respectively. Otherwise the error “mysql out of resources when opening file errcode 24” may occur.

To change the number of file descriptors available to MySQL, you can set open-files=2048 (or to any other reasonable number) in the MySQL configuration file.

If table is not empty, there may be duplicated records. If so a regular alter query to create a new unique index will return an error like below.

ERROR 1062 (23000): Duplicate entry ‘132653-47’ for key 1

However keyword IGNORE allows to force a new unique index to drop duplicated rows.

ALTER IGNORE TABLE `t` ADD UNIQUE INDEX `i` (`f1`, `f2`);

In this case the output will be something like this:

Query OK, 507 rows affected (0.02 sec)
Records: 507 Duplicates: 0 Warnings: 0

So simply using IGNORE helps to save time on writing a custom script to clean up duplicates.

Although it might be a good time to start testing the third milestone of MySQL 5.5, I am sure many projects are still in transition between versions 5.0 and 5.1.

MySQL 5.0 to 5.1 upgrade is well documented and went pretty smoothly in our case. However our project was heavily relied on stored procedures and that gave us some troubles to deal with.

The first issue was just annoying warning “Creation context of stored routine .. is invalid” for every call of any stored routine (stored procedures and functions worked as expected).

It turned out that the upgrade to 5.1 had added four columns to the mysql.proc table:

• character_set_client,
• collation_connection,
• db_collation,
• body_utf8.

Those columns were created with a default value of NULL and that was generating the warning. But if a stored procedure/function is created in ‘native’ 5.1 environment, it has these values set to the system defaults and no warning is shown.

Manual altering the mysql.proc table and setting the value of ‘character_set_client’, ‘collation_connection’ and ‘db_collation’ to valid values prevented the error form appearing:

UPDATE proc SET
  character_set_client = 'utf8',
  collation_connection = 'utf8_general_ci',
  db_collation = 'latin1_swedish_ci';

This issue was reported as bug and marked as closed for version 5.1.21 in 2007. But it looked like it had made it back to version 5.1.4x somehow in 2010.

The second issue was more complicated and actually some stored procedures stopped working because of weird “Column count doesn’t match value count at row 1” error (which did not make much sense though).

Additional research showed that some insert queries did not work withing prepare\execute blocks due to incompatible changes in 5.1.25 to the way that the server handles prepared statements. The bug #21774 shed some light on this issue too.

For example the statement like below (within a stored procedure) may produce the error mentioned above:

DECLARE vNum INT;
 
CREATE TABLE t (f TINYINT);
 
SET vNum = 1;
SET @SQL = CONCAT('INSERT INTO t SELECT ', vNum);
 
PREPARE QUERY FROM @SQL;
EXECUTE QUERY;
DEALLOCATE PREPARE QUERY;

Simply changing vNum type from INT to TINYINT helped (so the variable type matches the column type in the table).

I hope this will save couple hours to someone unless it has been taken care of in newer MySQL releases.

There is a magic MySQL statement KILL which allows to terminate either connection or query associated with provided thread ID.

SHOW PROCESSLIST statement allows to see running threads (including thread ID).

Example 1:

KILL QUERY 24;

terminates the statement that the connection is currently executing under thread 24, but leaves the connection itself intact.

Example 2:

KILL 24;

or

KILL CONNECTION 24;

terminates the connection associated with the thread 24 (i.e. re-connect will be required).

Roland Bouman published his presentation for the MySQL User Conference and Expo 2010 on optimizing MySQL stored routines. His recommendations are based on test measurements using benchmark() function.

The summary is short but could be very useful.

It is a bit confusing that MySQL function supports return statement but stored procedure does not.

However there is LEAVE statement which can serve the same goal:

this statement is used to exit the flow control construct that has the given label. It can be used within BEGIN … END or loop constructs (LOOP, REPEAT, WHILE).

For example the stored procedure below returns the passed number only if it is non-zero:

DROP PROCEDURE IF EXISTS TestProc;
CREATE PROCEDURE TestProc
(
  vTestNum INT
)
TOP:BEGIN
 
IF vTestNum = 0 THEN
  LEAVE TOP;
END IF;
 
SELECT vTestNum;
 
END;

There are many restrictions on stored routines and triggers. However it turned out though a query is fallen under the restrictions it may not cause an error message but simply work a different way. So developers may not be aware that their queries do not work as expected.

For example, inserts cannot be delayed within stored procedures BUT INSERT DELAYED syntax is accepted. The statement is handled as a normal INSERT though.

Working on the task which involved high insert load, we debated either utilize delayed insert or regular one. Later it turned out that delayed insert was not even an option. Thus stay on guard and don’t let MySQL to confuse you. 🙂

Ronald Bradford has shared slides for his presentation at FOSDEM 2010. This presentation covers monitoring, reviewing and analyzing SQL, the art of indexes, improving SQL, storage engines and caching.

My favorite slide is #46:

The best performance improvement for an SQL statement is to eliminate it.

🙂

Dries Buytaert did some research and compiled a timeline of MySQL AB’s history.

1995

• MySQL AB founded by Michael Widenius (Monty), David Axmark and Allan Larsson in Sweden.

2000

• MySQL goes Open Source and releases software under the terms of the GPL. Revenues dropped 80% as a result, and it took a year to make up for it.

2001

• Mårten Mickos elected CEO at age 38. Mårten was the CEO of a number of Nordic companies before joining MySQL, and comes with a sales and marketing background.
• 2 million active installations.
• Raised series A with undisclosed amount from Scandinavian venture capitalists. Estimated to be around $1 to $2 million.

2002

• MySQL launched US headquarters in addition to Swedish headquarters.
• 3 million active users.
• Ended the year with $6.5 million in revenue with 1,000 paying customers.

2003

• Raised a $19.5 million series B from Benchmark Capital and Index Ventures.
• 4 million active installations and over 30,000 downloads per day.
• Ended the year with $12 million in revenue.

2004

• With the main revenue coming from the OEM dual-licensing model, MySQL decides to move more into the enterprise market and to focus more on recurring revenue from end users rather than one-time licensing fees from their OEM partners.
• Ended the year with $20 million in revenue.

2005

• MySQL launched the MySQL Network modeled after the RedHat Network. The MySQL Network is a subscription service targeted at end users that provides updates, alerts, notifications, and product-level support designed to make it easier for companies to manage hundreds of MySQL servers.
• MySQL 5 ships and includes many new features to go after enterprise users (e.g. stored procedures, triggers, views, cursors, distributed transactions, federated storage engines, etc.)
• Oracle buys Innobase, the 4-person Finnish company behind MySQL’s InnoDB storage backend.
• Ended the year with $34 million in revenue based on 3400 customers.

2006

• Mårten Mickos confirms that Oracle tried to buy MySQL. Oracle’ CEO Larry Ellison commented: “We’ve spoken to them, in fact we’ve spoken to almost everyone. Are we interested? It’s a tiny company. I think the revenues from MySQL are between $30 million and $40 million. Oracle’s revenue next year is $15 billion.”
• Oracle buys Sleepycat, the company that provides MySQL with the Berkeley DB transactional storage engine.
• Mårten Mickos announces that they are making MySQL ready for an IPO in 2008 on an projected $100 million in revenues.
• 8 million active installations.
• MySQL has 320 employees in 25 countries, 70 percent of whom work from home.
• Raised a $18 million Series C based on a rumored valuation north of $300 million.
• MySQL is estimated to have a 33% market share measured in install base and 0.2% market share measured in revenue (the database market was a $15 billion market in 2006).
• Ended the year with $50 million in revenue.

2007

• Ended the year with $75 million in revenue.

2008

• Sun Microsystems acquired MySQL AB for approximately $1 billion.
• Michael Widenius (Monty) and David Axmark, two of MySQL AB’s co-founders, begin to criticize Sun publicly and leave Sun shortly after.

2009

• Mårten Mickos leaves Sun and becomes entrepreneur-in-residence at Benchmark Capital. Sun has now lost the business and spiritual leaders that turned MySQL into a success.
• Sun Microsystems and Oracle announced that they have entered into a definitive agreement under which Oracle will acquire Sun common stock for $9.50 per share in cash. The transaction is valued at approximately $7.4 billion.