Showing entries 1 to 7
Displaying posts with tag: processlist (reset)
on time in mysql processlist

There was one MySQL feature that I was always very afraid to ask for. I sure needed it for past 15 years of my MySQL practice, and yet I never got to it. I wanted MySQL processlist (‘SHOW PROCESSLIST’, later – information_schema.processlist) to have more accurate query execution time.

I submitted a patch for that to MongoDB (and it got merged and released really quickly). I couldn’t admit to myself and others that MySQL does not have this functionality, even though it is hard to reason about systems in production without such data.

When 99.999% of queries happen within 1s, one has to resort to statistical analysis of zeroes and ones to determine how long they may be running (that is, if nine queries are at 0s and one is at 1s, there’s a chance that all of them are running for 0.1s). Unfortunately, this kind of statistical analysis is not feasible …

[Read more]
Discover MyXplain, another way to learn, search, find and stroll



I’m very glad to announce you that MyXplain is released today.

This project is the result of many weeks of work for my friend Max and I and we are obviously very proud to present it.

Please take a time to browse it and find all the surprises that awaiting you.

We wanted to make an unique place, a gathering place where you can find all you want to know about the most useful MySQL commands.
We started with the explain command because we consider it as one of the most important.
show processlist and show slave status will be the next proposed commands on MyXplain.

The concept is pretty simple, we would like to offer you a new documentation to access all needed …

[Read more]
Q/A on Kontrollbase slow/gen query logs and processlist

I recently was asked the following from a Kontrollbase user via email. Since this question might be asked by others I’m posting my response so that the user community can have the answer available right here, right now. Question “I am unable to locate the slow / general queries list as well as processlist. Does [...]

Killing my softly with QUERY

The MySQL KILL command as the name suggests kills queries that are running.

After identifying the Id using the SHOW PROCESSLIST command, the User of the connection/thread or a database user with SUPER privileges can execute KILL [id]; to remove the connection/thread.

However, there is an ability to kill just the query that is being executed rather the entire connection. The default when not specified is to kill the connection, however you can optional specify the CONNECTION or QUERY keywords.

For example, below is an interactive test.

Thread 1:

mysql> select sleep(10);

Thread 2:

mysql> show processlist;
| Id | User | Host      | db   | Command | Time | State     | Info             | …
[Read more]
MySQL processlist – (show/kill processes)

It”s not the most common task in the world, but you might want to view processes from a particular user and once in a while you might even need to kill processes from a single user, be it during an attack or because you simply got a bug in an application bombarding your db server with connections!

Here is a small stored procedure which does exactly that!

call process_list("show","username","hostname");

– shows all processes owned by username@hostname

call process_list("kill","username","hostname");

– kills all processes owned by username@hostname

The code for this stored procedure can be found below. If you have any comments / suggestions feel free to comment below.

##                                        …

[Read more]
Query profiling using SHOW PROCESSLIST

The MySQL database server can display a lot of performance statistics. However, most of them relate to general server parameters, such as buffer sizes and query cache settings. Of course, such settings are important to get right, and can make a huge difference in performance. Once you've tuned them however, you need to start looking at the tables and queries. When a single bad query can cost 90 - 99% of the total performance, server tuning doesn't fix the problem. But obtaining query profiling information from MySQL can be tricky. Here are some of the options:Slow query logYou can use the built-in slow_query_log from within the server. It will show you queries that take more than a second. Such queries typically don't use indices properly and are important to address. But only slow queries end up there, and many performance problems aren't caused by slow queries, but by queries that are run very often (for example, for-loops in the code …

[Read more]
Why You Want to Switch to MySQL 5.1

In two words: online operations. In a paragraph: Forget partitioning, row-based replication and events. The big reasons most people are going to salivate over 5.1, and probably start plans to upgrade now, are the online operations:

  • online ALTER TABLE for column rename, column default value change, and adding values to the end of an ENUM/SET
  • Online, table-based logging. No more need to restart your server to enable or change the general or slow query logs. You can have the standard file-based output or choose a table format…which you can query.


Showing entries 1 to 7