Alex Stetsenko

MySQL’s show full processlist is a great way to monitor and diagnose what is going on with the database server at certain moment.

However often it is needed to have an aggregated view or evaluate only specific type of users and\or queries.

The solution turned out to be pretty simple since show full processlist is just an alias for a regular query to processlist table within information_schema database.

So the following queries are equivalent:

SHOW FULL PROCESSLIST;
 
SELECT * FROM information_schema.processlist;

As result it is possible to present the data from process list any convenient way. There are some basic examples below.

-- display number of connections for each user
SELECT `USER`, COUNT(*) FROM information_schema.processlist
GROUP BY `USER`;
 
-- display number of connections for each host
SELECT `HOST`, COUNT(*) FROM information_schema.processlist
GROUP BY `HOST`;
 
-- display root user activity
SELECT * FROM information_schema.processlist
WHERE `USER` = 'root';
 
-- display processes associated with SELECT queries
SELECT * FROM information_schema.processlist
WHERE `INFO` LIKE 'SELECT %';
 
-- display average query time for each database
SELECT `DB`, AVG(`TIME`) FROM information_schema.processlist
GROUP BY `DB`;

In this short note I simply want to second Joel Spolsky’s post on backups: Let’s stop talking about “backups”.

For instance many IT related people are comfortable enough knowing that necessary backups are done. However not many are concerned about restoring from the backup or actually tried the whole backup restore process. Nonetheless there is always something unexpected happens during restore…

Thus the right question to ask is not “are you doing backups?” but “can you restore your backup?” or “are you doing restores?” is even better.

The minimum bar for a reliable service is not that you have done a backup, but that you have done a restore. If you’re running a web service, you need to be able to show me that you can build a reasonably recent copy of the entire site, in a reasonable amount of time, on a new server or servers without ever accessing anything that was in the original data center. The bar is that you’ve done a restore.

Often it is quite reasonable to have the database to take care of some audit related columns like date added or updated.
Unfortunately MySQL does NOT allow functions within table definitions like let’s say MS SQL does, e.g.:

CREATE TABLE t (f datetime NOT NULL DEFAULT now());

The only work around is to use timestamp column instead of date or datetime ones. There can be only one auto set timestamp column though.
So I normally use it to store date updated and save the date created manually.

CREATE TABLE t
(
  name VARCHAR(20),
  date_updated TIMESTAMP NOT NULL
               DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

Also there are two things to keep in mind:

• date_updated will be set based on MySQL server time. This is especially important if other data is stored using GMT or non-server time zone.
• date_updated must NOT be a part of update or insert query.

-- set date_updated to current time
INSERT INTO t (name) VALUES ('test');
UPDATE t SET name = 'test';
 
-- override date_updated to certain value
INSERT INTO t (name, date_updated)
       VALUES ('test', '2011-08-02 20:00:00');
UPDATE t SET name = 'test',
             date_updated = '2011-08-02 20:00:00';

Alternatively it is possible to use triggers to have as many auto updated datetime columns as needed. However personally I prefer to avoid triggers as much as possible. Mostly because often triggers are misused and as result application complexity is increased for no reason.