Skip to content

# Category: MySQL

MySQL related posts

## Reducing locks by narrowing primary key

MySQL Indexing InnoDB Performance May 4, 2010May 4, 2010

In a period of two weeks, I had two cases with the exact same symptoms.

Database users were experiencing low responsiveness. DBAs were seeing locks occurring on seemingly normal tables. In particular, looking at Innotop, it seemed that INSERTs were causing the locks.

In both cases, tables were InnoDB. In both cases, there was a PRIMARY KEY on the combination of all 5 columns. And in both cases, there was no clear explanation as for why the PRIMARY KEY was chosen as such.

#### Choosing a proper PRIMARY KEY

Especially with InnoDB, which uses clustered index structure, the PRIMARY KEY is of particular importance. Besides the fact that a bloated PRIMARY KEY bloats the entire clustered index and secondary keys (see: The depth of an index: primer), it is also a source for locks. It’s true that any UNIQUE KEY can serve as a PRIMARY KEY. But not all such keys are good candidates. Continue reading » “Reducing locks by narrowing primary key”

## Database schema under version control

Development MySQL April 22, 2010

How many organization use version control for development? Probably almost every single one.

How many store the database schema under version control? Alas, not as many.

Coupling one’s application with table schema is essential. Organization who actively support multiple versions of the product understand that well. Smaller organizations not always have this realization.

How is it done?

#### Ideally

Ideally one would have:

- The schema, generated by hand
- Essential data (INSERT INTO statements for those lookup tables without which you cannot have an application)
- Sample data: sufficient real-life data on which to act. This would include customers data, logs, etc.

If you can work this way, then creating a staging environment consists of re-creating the entire schema from out schema code, and filling in the essential & sample data.

The thing with this method is that one does not (usually?) apply it on one’s live system. Say we were to add a column. On our live servers we would issue an ALTER TABLE t ADD COLUMN.

But this means we’re using different methods on our staging server and on our production server.

#### Incremental

Another kind of solution would be to hold:

- The static schema, as before
- Essential data, as before
- Sample data, as before
- A migration script, which is the concatenation of all ALTERs, CREATEs etc. as of the static schema.

Once in a while one can do a “reset”, and update the static schema with the existing design.

#### As you go along

This solution simply means “we apply the changes on staging; test + version them; then apply on production”.

A side effect of this solution is that the database generates the schema, but the schema does not generate the database as in previous cases. This makes for an uglier solution, where you first apply the changes to the database, and then, based on what the database report, enter data into the version control.

How to do that? Easiest would be to use mysqldump –routines –no-data. Some further parsing should be done to strip out the AUTO_INCREMENT values, which tend to change, as well as the surrounding variables settings (strip out the character set settings etc.).

#### Summary

However you do it, make sure you have some kind of version control on your schema. It pays off just as with doing version control for your code. You get to compare, understand the incremental changes, understand the change in design, etc.

## Tales of the Trade #3: MySQL vs. NoSQL

MySQL Humor NoSQL April 19, 2010April 21, 2010

Apparently the message from the comic below was not well conceived.

Following the strip I’ve added some spoiler notes.

This is not about bashing NoSQL. Apparently some people feel this way.

This is about:

- Trying to judge/analyze NoSQL as if it were a single entity, thereby asking “should I use MySQL or NoSQL”?
- The fact Wikipedia defines NoSQL as a “movement” (who’s the leader?)
- The fact that people are still arguing on how “MySQL” should be pronounced.

## Poll: what (minor) versions of MySQL should be supported by an open source MySQL related project?

MySQL Open Source Opinions April 2, 2010April 9, 2010

I would like to get the community’s opinion about supporting older (minor) versions of MySQL in open source projects.

If I were to develop some open source project, and a bug report came which only applied to MySQL 5.0.51 (but more recent versions worked fine), would I need to fix the code so as to support this older version?

How about supporting 5.0.22 (released almost 4 years ago, with almost 70 revisions since)? Would you expect an open source project to support this MySQL version because, say, this is the default version in your yum repository?

I would like to concentrate on the currently stable MySQL versions: 5.0 and 5.1. Versions 4.x are out of the question for me, and 5.5 is not yet GA.

Sure, it would be great to support everything. But also time and effort consuming. So, I would greatly appreciate your feedback!
Continue reading » “Poll: what (minor) versions of MySQL should be supported by an open source MySQL related project?”

## mycheckpoint (Rev. 118): alerts, email notifications and more

MySQL Monitoring mycheckpoint python March 25, 2010May 21, 2010

Revision 118 of mycheckpoint has been released. New and updated in this revision:

- Conditional alerts
- Email notifications
- Revised HTML reports, including 24/7 reports.
- Updated documentation

With this new revision mycheckpoint turns into a monitoring solution for MySQL. One can now:

- Store measure metrics
- Query for raw, aggregated or digested metrics
- Generate charts for selected metrics
- View HTML reports for selecetd metrics
- Define alerts conditions, query for pending alerts
- Be notified via email on raised or resolved alerts.

#### Conditional alerts

mycheckpoint is SQL oriented. As such, it allows for creation of alert conditions, which are nothing more than SQL conditions.

Continue reading » “mycheckpoint (Rev. 118): alerts, email notifications and more”

## oak-hook-general-log: streaming general log

MySQL logs openark kit python scripts March 21, 2010March 21, 2010

I’m seeking input on a new openark kit utility I’ve started to implement.

The tool, oak-hook-general-log, will hook up to a MySQL (>= 5.1) server, and stream the general log into standard output. It looks like this:

>     bash$ python src/oak/oak-hook-general-log.py --socket=/tmp/mysql.sock --user=root
>     2010-03-21 10:18:42     root[root] @ localhost []       79      1       Query   SELECT COUNT(*) FROM City
>     2010-03-21 10:18:48     root[root] @ localhost []       79      1       Query   DELETE FROM City WHERE id=1000
>     2010-03-21 10:18:54     root[root] @ localhost []       79      1       Query   SHOW PROCESSLIST
>     2010-03-21 10:19:06     root[root] @ localhost []       79      1       Quit
>     2010-03-21 10:19:07     root[root] @ localhost []       93      1       Connect root@localhost on
>     2010-03-21 10:19:07     root[root] @ localhost []       93      1       Query   select @@version_comment limit 1
>     2010-03-21 10:22:33     root[root] @ localhost []       93      1       Query   SELECT City.Name, Country.Name FROM Country JOIN City ON Country.Capit
>     2010-03-21 10:22:58     root[root] @ localhost []       93      1       Quit

Since output is written to stdout, one can further:

>     bash$ python src/oak/oak-hook-general-log.py --socket=/tmp/mysql.sock --user=root | grep Connect
>     bash$ python src/oak/oak-hook-general-log.py --socket=/tmp/mysql.sock --user=root | grep webuser@webhost

What the tool does is to enable table logs, and periodically rotate the mysql.general_log table, read and dump its content.

Continue reading » “oak-hook-general-log: streaming general log”

## But I DO want MySQL to say “ERROR”!

MySQL Configuration Data Types sql_mode Syntax March 12, 2010March 12, 2010

MySQL is known for its willingness to accept invalid queries, data values. It can silently commit your transaction, truncate your data.

- Using GROUP_CONCAT with a small group_concat_max_len setting? Your result will be silently truncated (make sure to check the warnings though).
- Calling CREATE TEMPORARY TABLE? You get silent commit.
- Issuing a ROLLBACK on non-transactional involved engines? Have a warning; no error.
- Using LOCK IN SHARE MODE on non transactional tables? Not a problem. Nothing reported.
- Adding a FOREIGN KEY on a MyISAM table? Good for you; no action actually taken.
- Inserting 300 to a TINYINT column in a relaxed sql_mode? Give me 255, I’ll silently drop the remaining 45. I owe you.

#### Warnings and errors

It would be nice to: Continue reading » “But I DO want MySQL to say “ERROR”!”

## Proper SQL table alias use conventions

MySQL Java Opinions python SQL Syntax March 11, 2010March 11, 2010

After seeing quite some SQL statements over the years, something is bugging me: there is no consistent convention as for how to write an SQL query.

I’m going to leave formatting, upper/lower-case issues aside, and discuss a small part of the SQL syntax: table aliases. Looking at three different queries, I will describe what I find to be problematic table alias use.

Using the sakila database, take a look at the following queries: Continue reading » “Proper SQL table alias use conventions”

## mk-schema-change? Check out ideas from oak-online-alter-table

MySQL openark kit Schema scripts March 10, 2010

In response to Mark Callaghan’s post mk-schema-change.

I apologize for not commenting on the post itself, I do not hold a Facebook account. Anyway this is a long write, so it may as well deserve a post of its own.

Some of the work Mark is describing already exists under openark kit‘s oak-online-alter-table. Allow me to explain what I have gained there, and how the issue can be further pursued. There is relevance to Mark’s suggestion.

oak-online-alter-table uses a combination of locks, chunks and triggers to achieve an almost non-blocking ALTER TABLE effect. I had a very short opportunity to speak with Mark on last year’s conference, in between bites. Mark stated that anything involving triggers was irrelevant in his case.

The triggers are a pain, but I believe a few other insights from oak-online-alter-table can be of interest. Continue reading » “mk-schema-change? Check out ideas from oak-online-alter-table”

## Things to monitor on MySQL, the user’s perspective

MySQL Execution plan Monitoring mycheckpoint March 10, 2010

Working on mycheckpoint, I have the intention of adding custom monitoring. That is, letting the user define things to monitor. I have my own thoughts, I would be grateful to get more input!

#### What would the user want to monitor?

Monitoring for the number of SELECT statements per second, InnoDB locks, slave replication lag etc. is very important, and monitoring utilities provide with this information. But what does that tell the end user? Not much.

The experienced DBA may gain a lot. The user would be more interested in completely other kind of information. In between, some information is relevant to both.

Say we were managing an on-line store. We want to monitor the health of the database. But the health of the database is inseparable from the health of the application. I mean, having little to no disk usage is fine, unless… something is wrong with the application, which leads to no new purchases.

And so a user would be interested in monitoring the number of purchases per hour, or the time passed since last successful purchase. This kind of data can only be generated by a user’s specific query. Looking at the charts, the user would then feel safer and confident in the wellness of his store app.

Continue reading » “Things to monitor on MySQL, the user’s perspective”