There is a feature in MySQL to log all queries that take longer than long_query_time to execute, and optionally also log the queries that don't use indexes. If you are not familar with it, I recommend reading this page in our manual.

One issue to be aware of, is that some queries may take much longer than long_query_time to execute, but will not show up in your slow query log. The manual gives a very good explaination for this;

The time to acquire the initial table locks is not counted as execution time.

There are two status variables that may help in seeing if you are hitting read/write concurrency issues because of table-level locking;

mysql> show global status like 'Table_locks%';
+-----------------------+-------+
| Variable_name         | Value | …

Using freely available ZRM for MySQL , you can set up a solution to backup and restore your MySQL database, within minutes. This article has all the details.

If you're following MySQL or PHP landscape you should have seen announcement by MySQL to develop pure PHP driver. If not - Here is FAQ .

I'm to meet the team (Georg, Andrey etc) which will be developing this driver during my visit to Open Source Database Conference in November so I thought it would be good idea to gather some wish list for things nice to have in this new driver. Below is my list and I would appreciate to hear your ideas.

Build In Profiling I would like to see how many connections and queries page generated and how long they took - I will place this information in the log. For debugging I'd like to be able to get a table below page output listing all the queries, their exec times and number of rows they are returned. So far it has to be done in inherited class.

Auto Explain Would be very helpful for …

I have decided to withdraw definitively from the political debate on the European Patent Litigation Agreement (EPLA). The process itself may very well take several more years, but I will not do any more lobbying nor make any more public statements in this regard.

This year I made a lot of effort to inform politicians, the media and the public of the shortcomings of the present EPLA proposal, and I had the chance to make my contribution in the build-up to the European Parliament’s October 13 resolution, but I kept rather silent in recent weeks and will not speak out on this particular issue again. Until there is a new patent policy process in which I might participate, I will not comment on any patent-related issues. Last year I returned to the fray after three months of absence ? this time there definitely won’t be …

Ah well, since my first article on this, MySQL has had multiple version changes and I have gained more experience in using MySQL. (Off topic...... I really like the fact that I am stuck in everything from MySQL server config/support thru Apache thru WebObjects/Java development, deployment and administration ...... never a dull day in the job!). Here is my "new improved" process for setting up a replication master-slave configuration using MySQL 4.1.21. These instructions will definitely not work for version 4.0.XX and earlier and may not work for some earlier versions of 4.1. Earlier version incompatabilities are mostly related to the parameters used in mysqldump.

This article assumes a basic knowledge of unix (cd, ssh, scp, mkdir, chown) and a basic knowledge of mysql (mysqld, mysql, mysqldump, mysqladmin)

Ah well, since my first article on this, MySQL has had multiple version changes and I have gained more experience in using MySQL. (Off topic...... I really like the fact that I am stuck in everything from MySQL server config/support thru Apache thru WebObjects/Java development, deployment and administration ...... never a dull day in the job!). Here is my "new improved" process for setting up a replication master-slave configuration using MySQL 4.1.21. These instructions will definitely not work for version 4.0.XX and earlier and may not work for some earlier versions of 4.1. Earlier version incompatabilities are mostly related to the parameters used in mysqldump.

This article assumes a basic knowledge of unix (cd, ssh, scp, mkdir, chown) and a basic knowledge of mysql (mysqld, mysql, mysqldump, mysqladmin)

There are essentially two main choices for scaling MySQL - cluster (NDB) or replication.

For many people replication works fine because their application is mostly read based. Just throw a few MySQL slave servers into the mix and you can scale out pretty well.

My guess is that this only works well for about 80-90% of users.

You update your database on the master but perform queries on the slaves. If you need more queries you can just add more slaves.

The problem with replication is that you can't scale your writes. If you buy an expensive RAID array you can probably get 1500 transactions per second (maybe more) out of your IO array but that's the best you can do for the whole cluster.

As soon as you hit 100% of your transactions as writes you're done. You've hit a scaling wall with replication and you can't go any farther.

You can of course go with vertical replication partitioning which works …

Many of you might know HowToForge, but for those who don't - there's a bunch of excellent and interesting tutorials relating to Linux and OpenSource.

There are also many about MySQL or using MySQL - here are some examples:

• How To Set Up Database Replication In MySQL
  
• MySQL Master Master Replication
  
• How To Set Up A Load-Balanced MySQL Cluster
  
• Running A MySQL-Based DNS Server: MyDNS
  
• …

Dear Diary, today I ventured into one of the darkest realms of the sysadmin profession: I started playing with SNMP. My goal was very simple and quite clearly stated: Make the output of "SHOW GLOBAL STATUS" available to a SNMP client. One would think that this is a reasonable and easily fulfilled wish.

Little did I know of the madness and despair that linger in the depths which are guarded by the dread named ASN.1 and where the noxious fumes of the organisation no longer called CCITT can still strongly be smelled.

But let us begin this story at the beginning - with a clean install of Suse Linux 10.0 and my trusty apt4rpm and me. It was my thought that the perl support of net-snmp might me handy to get me where I wanted to me: perl is easily the more convenient language for prototyping that C or C++ and getting to the data …

I got a lot of feedback on my question a few weeks back when pondering what I might choose for an OS to run MySQL. I also got a few "so what did you go with" questions.

A week ago I decided to give Ubuntu server a shot at winning my favor. I haven't used Ubuntu much, but all the fuss over it warrants giving it a shot.

I'm quite pleased so far. It meets at least two of the items on my list from that original post (minimal processes and minimal footprint). I'm not familiar with the philosophy behind building the Ubuntu kernel, but I'll accept the fact that there's nothing specifically tailored for MySQL.

Four clues that the server is installed minimal:

• 
  
• 17 minutes from start to finish (including times when it sits …