Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 7

Displaying posts with tag: show processlist (reset)

Time to forget show processlist for monitoring?
+1 Vote Up -0Vote Down

Disclaimer: I’m not specially an expert of benchmarking, this post is to compare different options. All comments and advices are welcome.

I’m not telling you anything new, the show processlist command is a fantastic command line tool for instant check.
But what about monitor your databases with this command embedded in a tool?

Just have a look at this graph:

With 5K queries per seconds, how much will be hidden with a show processlist executed every seconds? …


  [Read more...]
2 cases for MySQL server overload
+1 Vote Up -0Vote Down

Your MySQL server is overloaded. You see hundreds of running queries in the SHOW PROCESSLIST taking many seconds to run, or can’t connect at all because all connections slots are busy. If you have worked with MySQL long enough you surely have seen it, probably more than once. This is what I would call “MySQL server overload” – having more work than the server can possibly handle. At this point I see people often jumping to the conclusion that something went wrong with MySQL and focus all their effort on this belief. This is also often how we see questions framed when they are filed with our …

  [Read more...]
Using SHOW PROCESSLIST and mysqladmin debug Output in Conjunction with SHOW INNODB STATUS
+3 Vote Up -0Vote Down

When InnoDB appears hung, I know the natural reaction is to check SHOW ENGINE INNODB STATUS.

In fact, it’s the first thing I check when InnoDB tables are involved.

However, I just want to iterate how valuable SHOW FULL PROCESSLIST and/or mysqladmin debug outputs can be even when it seems mysqld is hung on on InnoDB table.

Two recent cases I’ve encountered illustrate why.

Case #1:

MySQL appeared hung on the following simple, single-row INSERT:

---TRANSACTION 0 2035648699, ACTIVE 76629 sec, process no 9047,
OS thread id 3069426592, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
...
INSERT INTO test (id, parent, text) …
  [Read more...]
Advanced InnoDB Deadlock Troubleshooting – What SHOW INNODB STATUS Doesn’t Tell You, and What Diagnostics You Should be Looking At
+5 Vote Up -0Vote Down

One common cause for deadlocks when using InnoDB tables is from the existence of foreign key constraints and the shared locks (S-lock) they acquire on referenced rows.

The reason I want to discuss them though is because they are often a bit tricky to diagnose, especially if you are only looking at the SHOW ENGINE INNODB STATUS output (which might be a bit counter-intuitive since one would expect it to contain this info).

Let me show a deadlock error to illustrate (below is from SHOW ENGINE INNODB STATUS\g):

------------------------
LATEST DETECTED DEADLOCK
------------------------
111109 20:10:03
*** (1) TRANSACTION:
TRANSACTION 65839, ACTIVE 19 sec, OS thread id 4264 …
  [Read more...]
How to SHOW PROCESSLIST Every .5 Seconds
+2 Vote Up -0Vote Down

Yes, it has been a while since I last posted, so we'll see how this year goes...Just the other evening I came across something that I should have done much sooner. Run SHOW PROCESSLIST every second without hitting "ENTER" repetitively.The Setup1) Create a temporary user with a non-sensitive password on localhost with SUPER privs. Yes, this crazy, but I did say *temporary*, right? mysql> GRANT

Another MySQL Proxy Tutorial
+1 Vote Up -0Vote Down

Since MySQL Proxy 0.7.0 is soon to be released, I thought another brief tutorial would be helpful. Today we ran across a good use case, and so I wanted to pass this along. If you’ve not used Proxy yet, this is a great opportunity to get your feet wet with it and some Lua.

When queries are routed though MySQL Proxy from various servers, the MySQL Server only sees that the query came from the machine Proxy is running on.

So when you are viewing output of SHOW PROCESSLIST, you have no way of telling what server a particular query originated from.

However, this could be very useful information to have, especially to determine which server a particular long-running …

  [Read more...]
Why You Want to Switch to MySQL 5.1
+0 Vote Up -0Vote Down

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:

  [Read more...]
Showing entries 1 to 7

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.