The web is going the way of utf8.  Drizzle has chosen it as the default character set, most back-ends to websites use it to store text data, and those who are still using latin1 have begun to migrate their databases to utf8.  Googling for "mysql convert charset to utf8" results in a plethora of sites, each with a slightly different approach, and each broken in some respect.  I'll outline those approaches here and show why they don't work, and then present a script that can generically be used to convert a database (or set of tables) to a target character set and collation.

Approach #1:

PLAIN TEXT SQL:

1. ALTER TABLE `t1` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Take the following table as an example why this approach will not work:

…

Are you running MySQL on Debian or Ubuntu with InnoDB? You might want to disable /etc/mysql/debian-start. When you run /etc/init.d/mysql start it runs this script, which runs mysqlcheck, which can destroy performance.

It can happen on a server with MyISAM tables, if there are enough tables, but it is far worse on InnoDB. There are a few reasons why this happens -- access to open an InnoDB table is serialized by a mutex, for one thing, and the mysqlcheck script opens all tables. One at a time.

It's pretty easy to get into a "perfect storm" scenario. For example, I'm working with one client right now who has a hosted multi-tenanting application that keeps each customer in its own database. So they have a lot of databases and a lot of tables. And they're running on Amazon EC2 with 8G of RAM and EBS storage, which is slower than typical directly-attached server-grade RAID storage. Since they have a lot of tables, InnoDB uses …

How smart is the MySQL optimizer? If it sees an expression repeated many times, does it realize they're all the same and not calculate the result for each of them?

I had a specific case where I needed to find out for sure, so I made a little benchmark. The query looks something like this:

PLAIN TEXT SQL:

1. SELECT sql_no_cache
2.    pow(sum(rental_id), 1),
3.    pow(sum(rental_id), 2),
4.    pow(sum(rental_id), 3),
5.    pow(sum(rental_id), 4),
6.    pow(sum(rental_id), 5),
7.    pow(sum(rental_id), 6),
8.    pow(sum(rental_id), 7),
9.    pow(sum(rental_id), 8),
10.    pow(sum(rental_id), 10),
11.   …

It’s been a few months since we‘ve started actively using ActiveMQ queue server in our project. For some time we had pretty weird problems with it and even started thinking about switching to something else or even writing our own queue server which would comply with our requirements. The most annoying problem was the following: some time after activemq restart everything worked really well and then activemq started lagging, queue started growing and all producer processes were stalling on push() operations. We rewrote our producers from Ruby to JRuby, then to Java and still – after some time everything was in a bad shape until we restarted the queue server.

So, long story short, after a lots of docs and source code reading we’ve found really interesting thing. There is a “feature” added in the recent ActiveMQ release …

These days I'm working with a customer who has an application based entirely on stored routines on MySQL side. Even though I haven't worked much with stored procedures, I though it's going to be a piece of cake. In the end - it was, but there's a catch.

My initial idea was - I'll just analyze queries in the slow query log generated by our mysql build running with long_query_time=0, get the slowest ones and work on them. It wasn't really all the way I expected..

For a showcase I have created a function "whatstheweatherlike". Let's call it and see what shows up in the slow query log:

PLAIN TEXT SQL:

1. mysql> SELECT whatstheweatherlike(5);
2. +----------------------------------------------------------+
3. | …

Vadim wrote some time ago about how to find unused indexes with single query.

I was working on the system today and found hundreds of unused indexes on dozens of tables so just dropping indexes manually did not look fun. So I extended Vadim's query to generate ALTER TABLE statements automatically. I also made it to look only at tables which were accessed:

PLAIN TEXT SQL:

1. mysql> SELECT concat('alter table ',d.table_schema,'.',d.table_name,' drop index ',group_concat(index_name separator ',drop index '),';') stmt FROM (SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics s LEFT JOIN information_schema.index_statistics iz ON (s.TABLE_SCHEMA = iz.TABLE_SCHEMA AND …

We have a lot of customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, and many similar types of work.  The first thing these have in common is that they're generally some kind of loggable event.

The next characteristic of a lot of these systems (real or planned) is the desire for "real-time" analysis.  Our customers often want their systems to provide the freshest data to their own clients, with no delays.

Finally, the analysis is usually multi-dimensional.  The typical user wants to be able to generate summaries and reports in many different ways on demand, often to support the functionality of the application as well as to provide reports to their clients.  Clicks by day, by customer, top ads by clicks, top ads by click-through ratio, and so on for dozens of different types of slicing and dicing.

And as a result, one of the most common …

There are all sorts of different interfaces to memcached, but you don't need any of them to make requests from the command line, because its protocol is so simple. Try this, assuming it's running on the usual port on the local machine:

PLAIN TEXT CODE:

1. echo stats | nc 127.0.0.1 11211
2. STAT pid 22020
3. STAT uptime 3689364
4. STAT time 1227753109
5. STAT version 1.2.5
6. STAT pointer_size 64
7. STAT rusage_user 4543.071348
8. STAT rusage_system 8568.293421
9. STAT curr_items 139897
10. STAT total_items 51710845
11. STAT bytes 360147055
12. STAT curr_connections 40
13. STAT total_connections 66762
14. STAT connection_structures 327
15. STAT cmd_get 319992973
16. STAT cmd_set …

Occasionally there is a need to see what queries reach MySQL. The database provides several ways to share that information with you. One is called general log activated with --log (or --general-log in MySQL 5.1+) start-up parameter. The log writes any query being executed by MySQL to a file with limited amount of additional information. The other is slow log enabled by --log-slow-queries parameter (MySQL 5.1 requires also --slow-query-log), which was designed to store poorly performing queries that run at least 2 seconds. Percona actually extended the slow log to, among others, include any query regardless of the execution time.

The problem is that for both you need to prepare earlier either by enabling the logging before starting the database instance or, even more work, by applying the patch and rebuilding the entire database from sources.

I know that many …

Everyone does backups. Usually it’s some nightly batch job that just dumps all MySQL tables into a text file or ordinarily copies the binary files from the data directory to a safe location. Obviously both ways involve much more complex operations than it would seem by my last sentence, but it is not important right now. Either way the data is out and ready to save someone’s life (or job at least). Unfortunately taking backup does not come free of any cost. On the contrary, it’s more like doing very heavy queries against each table in the database when mysqldump is used or reading a lot of data when copying physical files, so the price may actually be rather high. And the more effectively the server resources are utilized, the more that becomes a problem.

What happens when you try to get all the data?

The most obvious answer is that it needs to be read, through I/O requests, from a storage that it resides on. The storage is …