Your MySQL server is under heavy load or refuses any new connections because MySQL runs out of available threads. Ever wondered why?

For me the easiest way to get a first and very helpful real-time insight in what is happening in your database server is to use the show processlist statement:

mysql> show processlist;
+--------+---------+-----------+-------------+----------------+------+--------------------+------------------------------------------------------------------+
| Id     | User    | Host      | db          | Command        | Time | State              | Info                                                             |
+--------+---------+-----------+-------------+----------------+------+--------------------+------------------------------------------------------------------+
| 426144 | DELAYED | localhost | statistics  | Delayed insert |  170 | Waiting for INSERT | …

I spend large portion of my life working on MySQL Performance Optimization and so MySQL Optimizer is quite important to me. For probably last 10 years I chased first Monty and later Igor with Optimizer complains and suggestions. Here are some general ideas which I think can help to make optimizer in MySQL, MariaDB or Drizzle better.

Make it Plugable Every Major optimizer change causes a lot of pain for some of the users. Even if you improve performance 99 our of 100 queries there are going to be countless users complaining about the change. Due to this problem Optimizer Team was more conservative than I think they could have been. The solution is simple - make optimizer pluggable and make it possible to stick to old optimizer behavior with new MySQL Version.

Make Cost Model Adjustable MySQL Optimizer looks at query plan in terms of disk IOs/Seeks in same way for all data sets. In practice some …

I'm pleased to announce the release of Vertical Partitioning storage engine version 0.3.
http://launchpad.net/vpformysql

The main changes in this version are following.
- Add table parameter "choose_table_mode_for_lock".
- Add server parameter "vp_choose_table_mode_for_lock".
  These parameters are used for avoiding deadlock by fixing lock order to table list order.

Please see "99_change_logs.txt" in the download documents for checking other changes.

Enjoy!

Your MySQL server is under heavy load or refuses any new connections because MySQL runs out of available threads. Ever wondered why?

For me the easiest way to get a first and very helpful real-time insight in what is happening in your database server is to use the show processlist statement:

mysql> show processlist;
+--------+---------+-----------+-------------+----------------+------+--------------------+------------------------------------------------------------------+
| Id     | User    | Host      | db          | Command        | Time | State              | Info                                                             |
+--------+---------+-----------+-------------+----------------+------+--------------------+------------------------------------------------------------------+
| 426144 | DELAYED | localhost | statistics  | Delayed insert |  170 | Waiting for INSERT | …

Your MySQL server is under heavy load or refuses any new connections because MySQL runs out of available threads. Ever wondered why?

For me the easiest way to get a first and very helpful real-time insight in what is happening in your database server is to use the show processlist statement:

mysql> show processlist;
+--------+---------+-----------+-------------+----------------+------+--------------------+------------------------------------------------------------------+
| Id     | User    | Host      | db          | Command        | Time | State              | Info                                                             |
+--------+---------+-----------+-------------+----------------+------+--------------------+------------------------------------------------------------------+
| 426144 | DELAYED | localhost | statistics  | Delayed insert |  170 | Waiting for INSERT | …

Oracle Open World 2009 is just few weeks away and I firmed up my presentation schedule now. I will present three “normal” presentations and couple unconference sessions. I’m arriving in San Francisco few days before the conference (7th of October) get to the Oracle ACE Directors briefing so I’ll spend the first few day in Redwood Shores and then off to Moscone Center.

Before I get to the schedule, if you want to catch up with me during OOW — tweet me @alexgorbachev. You are likely to see me in the OTN Lounge or in “The Cave” if you know what I’m talking about.

Here is a quick summary of my presentations:

My friend Gabriel came up with a working "progress bar" for ALTER TABLE. Until MySQL / Drizzle can do this "natively", this is a pretty neat trick:

http://gabrielcain.com/blog/2009/08/05/mysql-alter-table-and-how-to-observe-progress/

Key-value databases are catching fire these days. Memcached, Redis, Cassandra, Keyspace, Tokyo Tyrant, and a handful of others are surging in popularity, judging by the contents of my feed reader.

I find a number of things interesting about these tools.

• There are many more of them than open-source traditional relational databases. (edit: I mean that there are many options that all seem similar to each other, instead of 3 or 4 standing out as the giants.)
• It seems that a lot of people are simultaneously inventing solutions to their problems in private without being aware of each other, then open-sourcing the results. That points to a sudden sea change in architectures. Tipping points tend to be abrupt, which would explain isolated redundant development.
• Many of the products are feature-rich with things programmers need: diverse language bindings, APIs, embeddability, and the ability to speak familiar …

Installing Cherokee With PHP5 And MySQL Support On Debian Lenny

Cherokee is a very fast, flexible and easy to configure Web Server. It supports the widespread technologies nowadays: FastCGI, SCGI, PHP, CGI, TLS and SSL encrypted connections, virtual hosts, authentication, on the fly encoding, load balancing, Apache compatible log files, and much more. This tutorial shows how you can install Cherokee on a Debian Lenny server with PHP5 support (through FastCGI) and MySQL support.

I decided to thee other day to investigate using Tokyo Tyrant because I was interested in the fact it has a memcached protocol and I wanted to get a feel for how it works with my memcached functions for MySQL (UDF). Matt Ingenthron came up with a good term recently: Mem-capable, which Tokyo Tyrant is. I find any key/value storage to be of great interest, particularly those that you simply change the port your memcached client is connecting and simply use it the same way you would memcached.

So, just what is Tokyo Tyrant?

Tokyo Tyrant is a database server, written by Mikio Hirabayashi, for Tokyo Cabinet. It provides for concurrent remote connections to Tokyo Cabinet . It provides its own binary protocol as well as a memcached and HTTP compatibility protocols. It also has C, Perl, PHP, Java, Erlang, Python and Ruby bindings. Other features Tokyo Tyrant offers:

* Hot backup and update log
* Async …