PlanetMysql.ru — информация о СУБД MySQL

Background Knowledge

---

The progress of a task in TaskFreak! is shown as a percentage value and is not exactly visually appealing to quickly spot the progress. With a few minor alterations we can show the percentage completed bar that fills as the task progresses and a gradient bar indicating the progress along with the percentage value.

This solution was posted by Searcher at Re: Taskfreak Customizing Status.

Solution

---

1. Edit at line #268 as shown below.
   

   Cod Before

   268	<th width="<?php echo FRK_STATUS_LEVELS * 2; ?>%" onclick="freak_sort('statusKey')" colspan="< ?php echo FRK_STATUS_LEVELS ?>" class="sortable">< ?php echo (FRK_STATUS_LEVELS == 1)?'X':$langForm['status']; ?></th>

   Code After

   268	<th width="80" onclick="freak_sort('statusKey')" class="sortable">< ?php echo (FRK_STATUS_LEVELS == 1)?'X':$langForm['status']; ?></th>

2. Edit at line #382
   

   Code Before

   < ?php
                       $s = $objItem->itemStatus->statusKey;
                       for ($i = 0; $i < FRK_STATUS_LEVELS; $i++) {
                           $j = ($i < $s)?(FRK_STATUS_LEVELS - $i):0;
                   ?>
                           <td id="est<?php echo ($i+1).$objItem->id; ?>" class="sts< ?php echo $j; ?>"< ?php
                           if ($objUser->checkLevel(14) || $objItem->checkRights($objUser->id,8,true))  {
                               echo ' onclick="freak_sts('.$objItem->id.','.($i+1).')" style="cursor:pointer"';
                           }
                       ?>>&nbsp;</td>
                   < ?php
                       }
                   ?>

   Code After

   <!-- status bar update -->
             <td>
               <table width="100%" cellpadding="0" cellspacing="0">
                 <tr>
                   < ?php
                   $s = $objItem->itemStatus->statusKey;
                   for ($i = 0; $i < FRK_STATUS_LEVELS; $i++) {
                     $j = ($i < $s)?(FRK_STATUS_LEVELS - $i):0;
                     ?>
                     <td width="10" onmouseover="this.style.cursor='pointer'" id="est<?php echo ($i+1).$objItem->id; ?>" class="sts< ?php echo $j; ?>"< ?php if ($objUser->checkLevel(14) || $objItem->checkRights($objUser->id,8,true))  { echo ' onclick="freak_sts('.$objItem->id.','.($i+1).')" style="cursor:pointer"'; } ?>>&nbsp;</td>
                     < ?php } ?>
                     <td style="border:0"></td>
                     <td id="status_perc_<?php echo $objItem->id; ?>" style="border:0; font-size:8px; color:#000" align="right">&nbsp;< ?php echo ($s*20)."%"; ?></td>
                   </tr>
                   <tr><td style="border:0; height:2px"></td></tr>
                   <tr>
                     <td colspan="7" style="border:0"><img id="status_bar_<?php echo $objItem-/>id; ?>" src="skins/status.jpg" height="5" width="< ?php echo ($s*16); ?>" /></td>
                 </tr>
               </table>
             </td>
   <!-- status bar update -->

3. Download the status gradient bar image file from “http://demofreak.dracon.biz/skins/status.jpg” and the copy image file to the /taskfreak/skins/ directory.

Source: Taskfreak Customizing Status

PlanetMySQL Voting: Vote UP / Vote DOWN

Background Knowledge

---

The progress of a task in TaskFreak! is shown as a percentage value and is not exactly visually appealing to quickly spot the progress. With a few minor alterations we can show the percentage completed bar that fills as the task progresses and a gradient bar indicating the progress along with the percentage value.

This solution was posted by Searcher at Re: Taskfreak Customizing Status.

Solution

---

1. Edit at line #268 as shown below.
   

   Cod Before

   268	<th width="<?php echo FRK_STATUS_LEVELS * 2; ?>%" onclick="freak_sort('statusKey')" colspan="< ?php echo FRK_STATUS_LEVELS ?>" class="sortable">< ?php echo (FRK_STATUS_LEVELS == 1)?'X':$langForm['status']; ?></th>

   Code After

   268	<th width="80" onclick="freak_sort('statusKey')" class="sortable">< ?php echo (FRK_STATUS_LEVELS == 1)?'X':$langForm['status']; ?></th>

2. Edit at line #382
   

   Code Before

   < ?php
                       $s = $objItem->itemStatus->statusKey;
                       for ($i = 0; $i < FRK_STATUS_LEVELS; $i++) {
                           $j = ($i < $s)?(FRK_STATUS_LEVELS - $i):0;
                   ?>
                           <td id="est<?php echo ($i+1).$objItem->id; ?>" class="sts< ?php echo $j; ?>"< ?php
                           if ($objUser->checkLevel(14) || $objItem->checkRights($objUser->id,8,true))  {
                               echo ' onclick="freak_sts('.$objItem->id.','.($i+1).')" style="cursor:pointer"';
                           }
                       ?>>&nbsp;</td>
                   < ?php
                       }
                   ?>

   Code After

   <!-- status bar update -->
             <td>
               <table width="100%" cellpadding="0" cellspacing="0">
                 <tr>
                   < ?php
                   $s = $objItem->itemStatus->statusKey;
                   for ($i = 0; $i < FRK_STATUS_LEVELS; $i++) {
                     $j = ($i < $s)?(FRK_STATUS_LEVELS - $i):0;
                     ?>
                     <td width="10" onmouseover="this.style.cursor='pointer'" id="est<?php echo ($i+1).$objItem->id; ?>" class="sts< ?php echo $j; ?>"< ?php if ($objUser->checkLevel(14) || $objItem->checkRights($objUser->id,8,true))  { echo ' onclick="freak_sts('.$objItem->id.','.($i+1).')" style="cursor:pointer"'; } ?>>&nbsp;</td>
                     < ?php } ?>
                     <td style="border:0"></td>
                     <td id="status_perc_<?php echo $objItem->id; ?>" style="border:0; font-size:8px; color:#000" align="right">&nbsp;< ?php echo ($s*20)."%"; ?></td>
                   </tr>
                   <tr><td style="border:0; height:2px"></td></tr>
                   <tr>
                     <td colspan="7" style="border:0"><img id="status_bar_<?php echo $objItem-/>id; ?>" src="skins/status.jpg" height="5" width="< ?php echo ($s*16); ?>" /></td>
                 </tr>
               </table>
             </td>
   <!-- status bar update -->

3. Download the status gradient bar image file from “http://demofreak.dracon.biz/skins/status.jpg” and the copy image file to the /taskfreak/skins/ directory.

Source: Taskfreak Customizing Status

PlanetMySQL Voting: Vote UP / Vote DOWN

As a MySQL DBA, I guess you use the SHOW GLOBAL STATUS command or the corresponding INFORMATION_SCHEMA.GLOBAL STATUS table to show current mySQL status quite often. And many of us use different tools to collect that data and view it.

But sometimes we use same command from the SQL prompt, because we have to, because it's the only option or just because that is the most convenient way. And often you kick of two such command in a row, a few seconds apart, just to see how things are moving:
SHOW GLOBAL STATUS LIKE 'innodb%rows%';
... DBA picks his or her nose for a few seconds ...
SHOW GLOBAL STATUS LIKE 'innodb%rows%';

And then you can see how things are advancing. But how much? To figure of how much, you have to calculate the differnce between the values returned by those two statements. And then there is another issue. How much is the difference per second? To know what the difference is per second, we have to includ the UPTIME_SINCE_FLUSH_STATUS status value in those statements. So we do this then:

SHOW GLOBAL STATUS WHERE variable_name like '%rows%' OR variable_name = 'Uptime_since_flush_status';
... Yet another chance for the DBA to pick the nose ...
SHOW GLOBAL STATUS WHERE variable_name like '%rows%' OR variable_name = 'Uptime_since_flush_status';

Now we have the data we need to work with, but we still need to do some math. And math is fine of course, in and of itself, but wouldn't it be nice to have some kind of contraption to do that for us? Like a machine? Let's call it a computer! Whao!

No, joking aside here, this is perfect job for a stored procedure or something. What we need is a stored procedure to to the jobs for us, and somewhere to store the previous value inbetween status runs, and here I will use a MySQL variable for that. Before I show you the code, let me show you what running it looks like:

MySQL> call getstat('%rows%', FALSE);

+--------------------------+-------------+

| variable_name            | current     |

+--------------------------+-------------+

| INNODB_ROWS_DELETED      | 171085420   |

| INNODB_ROWS_INSERTED     | 4940247881  |

| INNODB_ROWS_READ         | 16041373517 |

| INNODB_ROWS_UPDATED      | 90534033    |

| NOT_FLUSHED_DELAYED_ROWS | 0           |

| SORT_ROWS                | 266732753   |

+--------------------------+-------------+

6 rows in set (0.00 sec)



Query OK, 0 rows affected (0.00 sec)



MySQL> call getstat('%rows%', FALSE);

+--------------------------+-------------+-------------+------------+--------------------+

| variable_name            | previous    | current     | difference | difference_per_sec |

+--------------------------+-------------+-------------+------------+--------------------+

| INNODB_ROWS_DELETED      | 171085420   | 171085420   | 0          | 0.00 (2 s)         |

| INNODB_ROWS_INSERTED     | 4940247881  | 4940249044  | 1163       | 581.50 (2 s)       |

| INNODB_ROWS_READ         | 16041373517 | 16041373597 | 80         | 40.00 (2 s)        |

| INNODB_ROWS_UPDATED      | 90534033    | 90534044    | 11         | 5.50 (2 s)         |

| NOT_FLUSHED_DELAYED_ROWS | 0           | 0           | 0          | 0.00 (2 s)         |

| SORT_ROWS                | 266732753   | 266732753   | 0          | 0.00 (2 s)         |

+--------------------------+-------------+-------------+------------+--------------------+

6 rows in set (0.00 sec)



Query OK, 0 rows affected (0.00 sec)

The first parameter to the getstat procedure is the parameter LIKE-string you want to use, the second is a boolean if you want to rerun the base for the saved parameters. In other words, the first time the procedure is run, it collects relevant data, but it only shows current values, as that is what it has. The second and subsequent runs, it shows the difference from the first run, unless you set the other parameter to TRUE, which causes a new set of values to compare with to be set. If you subsequently again run it with the second parameter set to TRUE, the procedure will only show the difference between the two runs.

The nice thing with this procedure is that is uses no temp tables, which means no disk IO or anything, just access to the INFORMATION_SCHEMA GLOBAL_STATUS table, that's it.

So, now you can wait to get the procedure and use it yourself? OK, here we go, enjoy! (And there is actually the procedure and a simple supporting function).

/Karlsson

DROP PROCEDURE IF EXISTS getstat;

delimiter //

CREATE PROCEDURE getstat(p_like VARCHAR(64), p_gennew BOOL)

BEGIN

   DECLARE v_stat TEXT;

   DECLARE v_name VARCHAR(64);

   DECLARE v_value VARCHAR(1024);

   DECLARE v_nodata INTEGER;

   DECLARE v_uptime_current INTEGER;

   DECLARE v_uptime_last INTEGER;

   DECLARE c1 CURSOR FOR SELECT variable_name, variable_value

     FROM information_schema.global_status;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_nodata = 1;



   SET v_nodata = 0;

-- Make sure we always generate new stats the first time we run.

   IF @savedstat IS NULL THEN

      SET p_gennew = TRUE;

   END IF;



   IF p_gennew THEN

      SET v_stat = '';

-- Get the current status now, and loop through it.

      OPEN c1;

      l1: LOOP

         FETCH c1 INTO v_name, v_value;

         IF v_nodata THEN

            LEAVE l1;

         END IF;



-- Add name and value to saved statuses.

         SET v_stat = CONCAT(IF(v_stat = '', '', CONCAT(v_stat, ',')), v_name, ':', v_value);



-- Get current uptime.

         IF v_name = 'UPTIME_SINCE_FLUSH_STATUS' THEN

            SET v_uptime_current = v_value;

         END IF;

      END LOOP;

      CLOSE c1;

   ELSE

      SELECT variable_value INTO v_uptime_current

        FROM information_schema.global_status

        WHERE variable_name = 'UPTIME_SINCE_FLUSH_STATUS';

   END IF;



   SET v_uptime_last = getstatvalue('UPTIME_SINCE_FLUSH_STATUS');

-- If there was a previous value to show, then show difference.

   IF  v_uptime_last != '' THEN

      SELECT variable_name, getstatvalue(variable_name) AS previous,

        variable_value AS current,

        IF(getstatvalue(variable_name) = '', '',

          variable_value - getstatvalue(variable_name)) AS difference,

        IF(getstatvalue(variable_name) = '', '',

          CONCAT(TRUNCATE((variable_value - getstatvalue(variable_name))

          / (v_uptime_current - v_uptime_last), 2), ' (',

          v_uptime_current - v_uptime_last, ' s)')) AS difference_per_sec

      FROM information_schema.global_status

      WHERE variable_name LIKE p_like;

   ELSE

      SELECT variable_name, variable_value AS current

      FROM information_schema.global_status

      WHERE variable_name LIKE p_like;

   END IF;



   IF p_gennew THEN

      SET @savedstat = v_stat;

   END IF;

END//

delimiter ;



DROP FUNCTION IF EXISTS getstatvalue;

delimiter //

CREATE FUNCTION getstatvalue(p_name VARCHAR(64))

RETURNS VARCHAR(1025)

NO SQL

BEGIN

   DECLARE v_pos INTEGER;

   DECLARE v_end INTEGER;



-- If no saved status exists, then return nothing.

   IF @savedstat = '' THEN

      RETURN '';

   END IF;



-- Find the value.

   SET v_pos = INSTR(@savedstat, CONCAT(',', p_name, ':'));



-- Try to find the value name at the beginning of the string.

   IF v_pos = 0 THEN

      IF LEFT(@savedstat, LENGTH(p_name) + 1) = CONCAT(p_name, ':') THEN

         SET v_pos = 0;

      ELSE

         RETURN '';

      END IF;

   END IF;



   SET v_pos = v_pos + LENGTH(p_name) + 2;

-- Now find the value in the string, following the name and a colon.

   SET v_end = LOCATE(',', @savedstat, v_pos);

   IF v_end = 0 THEN

      SET v_end = LENGTH(@savedstat) + 1;

   END IF;



-- Extract the value and return it.

   RETURN SUBSTR(@savedstat, v_pos, v_end - v_pos);

END//

delimiter ;

PlanetMySQL Voting: Vote UP / Vote DOWN

My proposal has been accepted, yay!

I'll be speaking on a topic that I feel passionate about: MySQL Server Diagnostics Beyond Monitoring. MySQL has limitations when it comes to monitoring and diagnosing as it has been widely documented in several blogs.

My goal is to share my experience from the last few years and, hopefully, learn from what others have done. If you have a pressing issue, feel free to comment on this blog and I'll do my best to include the case in my talk and/or post a reply if the time allows.

I will also be discussing my future plans on sarsql. I've been silent about this utility mostly because I've been implementing it actively at work. I'll post a road map shortly based on my latest experience.

I'm excited about meeting many old friends (and most now fellow MySQL alumni) and making new ones. I hope to see you there!
PlanetMySQL Voting: Vote UP / Vote DOWN

I am very fortunate to be sent to a Percona innodb low level conference - with one of the guys who has written the High Performance MySQL book. One of the key items will be to dive deep into the Innodb kernel and find out what the hell the thing is doing. To that end I'm going post what I know about the 'show innodb status' output - now my aim from this conference is to pick up on some of the areas that I'm green in to identify and resolve more MySQL performance problems.

So here is my 'show innodb status' walkthrough:

mysql> show innodb status\G

*************************** 1. row ***************************

Type: InnoDB

Name:

Status:

=====================================

100308 17:05:14 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 14 seconds

The following information is based on stats gathered in the last 14 seconds - Generally a good 30 seconds should pass before the output can be considered an accurate average. There are some stats that are counters since server start however.

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 74964888, signal count 66577404

Mutex spin waits 0, rounds 3978408534, OS waits 51856599

RW-shared spins 16155677, OS waits 7409510; RW-excl spins 19774799, OS waits 3870167

Since server stat, Innodb has had to reserve 74964888 'slots' and innodb has signaled 66577404 threads to proceed - OS waits are very expensive relative to spin waits.

If there is a high concurrency performance issue, there would also be a line under 'OS WAIT' regarding a transaction(s) waiting on a semaphore. Also if there is a transaction, the output also references a c source file - and the name can usually be extracted to find the location of the bottlekneck.

Also, 19774799 times Innodb has used spin waits, and 3870167 Innodb has had to resort to OS waits - OS waits are more expensive.

------------------------

LATEST FOREIGN KEY ERROR

------------------------

100303 12:03:03 Transaction:

TRANSACTION 2 1356465249, ACTIVE 0 sec, process no 16670, OS thread id 1166653776 inserting, thread declared inside InnoDB 500

mysql tables in use 1, locked 1

3 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1

MySQL thread id 6773466308, query id 23928955894 172.24.0.2 readwrite update

insert into `AlertCriteria` (`AlertID`, `Field`, `Value`)

values ('7695501', 'tb', 'WESTMEAD,WATTLE GROVE,HOLSWORTHY,PARRAMATTA,PENDLE HILL,QUAKERS HILL')

Foreign key constraint fails for table `AlertSystem`.`AlertCriteria`:

,

CONSTRAINT `AlertCriteria_ibfk_1` FOREIGN KEY (`AlertID`) REFERENCES `Alerts` (`AlertID`) ON DELETE CASCADE

Trying to add in child table, in index `AlertID_index` tuple:

DATA TUPLE: 2 fields;

0: len 4; hex 80756c8d; asc ul ;; 1: len 4; hex 04650c45; asc e E;;

But in parent table `AlertSystem`.`Alerts`, in index `PRIMARY`,

the closest match we can find is record:

PHYSICAL RECORD: n_fields 13; compact format; info bits 0

0: len 4; hex 80756c92; asc ul ;; 1: len 6; hex 000250d61ec0; asc P ;; 2: len 7; hex 800000288e0110; asc ( ;; 3: len 5; hex 72656e2331; asc ren#1;; 4: len 23; hex 4e4557414c4552544e414d452d50616c6d204265616368; asc NEWALERTNAME-Palm Beach;; 5: len 4; hex 803497c6; asc 4 ;; 6: len 1; hex 81; asc ;; 7: len 4; hex 80000000; asc ;; 8: len 4; hex 80000000; asc ;; 9: len 6; hex 5765656b6c79; asc Weekly;; 10: len 1; hex 81; asc ;; 11: len 4; hex 80000000; asc ;; 12: len 4; hex 80000000; asc ;;

This output only appears if there has been a foreign key constraint issue since server restart.

The above shows that there was a foreign key constraint that was not met and Innodb aborted the transaction - the bellow junk is the actual parts of the Innodb code where it discovered the foreign key miss match and aborted. (Nothing to be scared about).

---- Last detected deadlock---

The last detected deadlock would appear if there was a deadlock since last server restart- unfortunately this is not the case therefore its not in the output of 'show innodb status'.

-----

------------

TRANSACTIONS

------------

Trx id counter 2 1594638995

Purge done for trx's n:o <>

History list length 928

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0 0, not started, process no 16670, OS thread id 1206856016

mysql tables in use 1, locked 1

MySQL thread id 6976314925, query id 24543783827 172.24.0.2 readwrite init

UPDATE Subscribers SET EmailAddress = '12618060932336258813', RealEstProps = '106370836 104481172 105731349 105958174 105958282 105969863 105974146 105998765 106010720 106078593 106102921 106150346 106322759 106337015 106340214 106312885 106343690 106159839 106207162 106290970', LastAccess = 1268028314 WHERE EmailAddress = '12618060932336258813'

---TRANSACTION 2 1594638991, not started, process no 16670, OS thread id 1331722576

MySQL thread id 6976315115, query id 24543783821 172.24.0.2 readwrite

---TRANSACTION 2 1594638994, not started, process no 16670, OS thread id 1366600016

MySQL thread id 6976315285, query id 24543783824 172.24.0.2 readwrite

---TRANSACTION 2 1594638982, not started, process no 16670, OS thread id 1161062736

MySQL thread id 6976315282, query id 24543783797 172.24.0.2 readonly

---TRANSACTION 2 1594638980, not started, process no 16670, OS thread id 1406802256

MySQL thread id 6976315278, query id 24543783787 172.24.0.2 readonly

---TRANSACTION 2 1594638978, not started, process no 16670, OS thread id 1079593296

MySQL thread id 6976315275, query id 24543783783 172.24.0.2 readonly

---TRANSACTION 2 1594638983, not started, process no 16670, OS thread id 1378847056

MySQL thread id 6976315277, query id 24543783782 172.24.21.19 readonly

---TRANSACTION 2 1594638977, not started, process no 16670, OS thread id 1226025296

MySQL thread id 6976314965, query id 24543783781 172.24.0.2 readwrite

---TRANSACTION 2 1594638989, not started, process no 16670, OS thread id 1394821456

MySQL thread id 6976315274, query id 24543783819 172.24.0.2 readonly

---TRANSACTION 2 1594638971, not started, process no 16670, OS thread id 1227622736

MySQL thread id 6976315097, query id 24543783748 172.24.0.2 readwrite

---TRANSACTION 2 1594638974, not started, process no 16670, OS thread id 1186355536

MySQL thread id 6976315255, query id 24543783755 172.24.0.2 readwrite

---TRANSACTION 2 1594638970, not started, process no 16670, OS thread id 1250785616

MySQL thread id 6976315261, query id 24543783747 172.24.0.2 readonly

---TRANSACTION 2 1594638967, not started, process no 16670, OS thread id 1384438096

MySQL thread id 6976315262, query id 24543783740 172.24.0.2 readonly

---TRANSACTION 2 1594638990, not started, process no 16670, OS thread id 1105054032

MySQL thread id 6976315258, query id 24543783820 172.24.0.2 readonly

---TRANSACTION 2 1594638962, not started, process no 16670, OS thread id 1326664016

MySQL thread id 6976315256, query id 24543783723 172.24.0.2 readonly

---TRANSACTION 2 1594638959, not started, process no 16670, OS thread id 1189816656

MySQL thread id 6976315251, query id 24543783709 172.24.0.2 readonly

---TRANSACTION 2 1594638955, not started, process no 16670, OS thread id 1337313616

MySQL thread id 6976314577, query id 24543783701 172.24.0.2 readwrite

---TRANSACTION 2 1594638954, not started, process no 16670, OS thread id 1386568016

MySQL thread id 6976315246, query id 24543783698 172.24.0.2 readwrite

---TRANSACTION 2 1594638952, not started, process no 16670, OS thread id 1349560656

MySQL thread id 6976315241, query id 24543783690 172.24.21.19 readonly

---TRANSACTION 2 1594638949, not started, process no 16670, OS thread id 1331456336

MySQL thread id 6976314979, query id 24543783684 172.24.0.2 readwrite

---TRANSACTION 2 1594638944, not started, process no 16670, OS thread id 1263831376

MySQL thread id 6976314994, query id 24543783675 172.24.0.2 readwrite

---TRANSACTION 2 1594638942, not started, process no 16670, OS thread id 1252649296

MySQL thread id 6976315228, query id 24543783669 172.24.0.2 readonly

---TRANSACTION 2 1594638940, not started, process no 16670, OS thread id 1183959376

MySQL thread id 6976315232, query id 24543783666 172.24.0.2 readonly

---TRANSACTION 2 1594638938, not started, process no 16670, OS thread id 1289124176

MySQL thread id 6976315103, query id 24543783663 172.24.0.2 readwrite

---TRANSACTION 2 1594638948, not started, process no 16670, OS thread id 1376184656

MySQL thread id 6976315205, query id 24543783772 172.24.0.2 readwrite

---TRANSACTION 2 1594638933, not started, process no 16670, OS thread id 1324534096

MySQL thread id 6976315224, query id 24543783641 172.24.0.2 readonly

---TRANSACTION 2 1594638931, not started, process no 16670, OS thread id 1272617296

MySQL thread id 6976315222, query id 24543783634 172.24.0.2 readonly

---TRANSACTION 2 1594638930, not started, process no 16670, OS thread id 1201531216

MySQL thread id 6976315221, query id 24543783630 172.24.0.2 readonly

---TRANSACTION 2 1594638929, not started, process no 16670, OS thread id 1355950416

MySQL thread id 6976315219, query id 24543783624 172.24.0.2 readonly

---TRANSACTION 2 1594638953, not started, process no 16670, OS thread id 1192212816

MySQL thread id 6976315217, query id 24543783694 172.24.0.2 readonly

---TRANSACTION 2 1594638919, not started, process no 16670, OS thread id 1225492816

MySQL thread id 6976315037, query id 24543783602 172.24.0.2 readwrite

---TRANSACTION 2 1594638917, not started, process no 16670, OS thread id 1391360336

MySQL thread id 6976315215, query id 24543783600 172.24.21.19 readonly

---TRANSACTION 2 1594638916, not started, process no 16670, OS thread id 1415588176

MySQL thread id 6976315213, query id 24543783599 172.24.0.2 readonly

---TRANSACTION 2 1594638913, not started, process no 16670, OS thread id 1157601616

MySQL thread id 6976315191, query id 24543783798 172.24.0.2 readwrite

---TRANSACTION 2 1594638905, not started, process no 16670, OS thread id 1314949456

MySQL thread id 6976315202, query id 24543783823 172.24.0.2 readonly

---TRANSACTION 2 1594638903, not started, process no 16670, OS thread id 1237207376

MySQL thread id 6976315200, query id 24543783555 172.24.0.2 readwrite

---TRANSACTION 2 1594638901, not started, process no 16670, OS thread id 1242532176

MySQL thread id 6976315195, query id 24543783543 172.24.0.2 readonly

---TRANSACTION 2 1594638896, not started, process no 16670, OS thread id 1268091216

MySQL thread id 6976315189, query id 24543783526 172.24.0.2 readonly

---TRANSACTION 2 1594638892, not started, process no 16670, OS thread id 1234811216

MySQL thread id 6976314982, query id 24543783521 172.24.0.2 readwrite

---TRANSACTION 2 1594638888, not started, process no 16670, OS thread id 1265428816

MySQL thread id 6976315186, query id 24543783692 172.24.0.2 readonly

---TRANSACTION 2 1594638899, not started, process no 16670, OS thread id 1369262416

MySQL thread id 6976315182, query id 24543783538 172.24.21.19 readonly

---TRANSACTION 2 1594638860, not started, process no 16670, OS thread id 1367132496

MySQL thread id 6976315178, query id 24543783465 172.24.0.2 readonly

---TRANSACTION 2 1594638850, not started, process no 16670, OS thread id 1398016336

MySQL thread id 6976315172, query id 24543783443 172.24.0.2 readonly

---TRANSACTION 2 1594638843, not started, process no 16670, OS thread id 1384171856

MySQL thread id 6976315133, query id 24543783760 172.24.0.2 readwrite

---TRANSACTION 2 1594638907, not started, process no 16670, OS thread id 1319475536

MySQL thread id 6976315152, query id 24543783567 172.24.0.2 readonly

---TRANSACTION 2 1594638802, not started, process no 16670, OS thread id 1310157136

MySQL thread id 6976315119, query id 24543783303 172.24.0.2 readonly

---TRANSACTION 2 1594638795, not started, process no 16670, OS thread id 1275013456

MySQL thread id 6976315110, query id 24543783296 172.24.0.2 readonly

---TRANSACTION 2 1594638951, not started, process no 16670, OS thread id 1288857936

MySQL thread id 6976315118, query id 24543783686 172.24.0.2 readonly

---TRANSACTION 2 1594638897, not started, process no 16670, OS thread id 1075738960

MySQL thread id 6976315049, query id 24543783528 172.24.0.2 readwrite

---TRANSACTION 2 1594638771, not started, process no 16670, OS thread id 1234544976

MySQL thread id 6976315095, query id 24543783365 172.24.0.2 readwrite

---TRANSACTION 2 1594638731, not started, process no 16670, OS thread id 1345300816

MySQL thread id 6976315077, query id 24543783797 172.24.0.2 readonly

---TRANSACTION 2 1594638923, not started, process no 16670, OS thread id 1406269776

MySQL thread id 6976315021, query id 24543783608 172.24.0.2 readwrite

---TRANSACTION 2 1594638975, not started, process no 16670, OS thread id 1348229456

MySQL thread id 6976315074, query id 24543783772 172.24.0.2 readonly

---TRANSACTION 2 1594638687, not started, process no 16670, OS thread id 1244129616

MySQL thread id 6976315053, query id 24543783681 172.24.0.2 readonly

---TRANSACTION 2 1594638780, not started, process no 16670, OS thread id 1395087696

MySQL thread id 6976315033, query id 24543783276 172.24.0.2 readonly

---TRANSACTION 2 1594638782, not started, process no 16670, OS thread id 1202329936

MySQL thread id 6976315030, query id 24543783278 172.24.21.19 readonly

---TRANSACTION 2 1594638645, not started, process no 16670, OS thread id 1203394896

MySQL thread id 6976315028, query id 24543782985 172.24.0.2 readonly

---TRANSACTION 2 1594638634, not started, process no 16670, OS thread id 1310689616

MySQL thread id 6976315022, query id 24543782969 172.24.21.19 readonly

---TRANSACTION 2 1594638633, not started, process no 16670, OS thread id 1190082896

MySQL thread id 6976315023, query id 24543783737 172.24.0.2 readonly

---TRANSACTION 2 1594638787, not started, process no 16670, OS thread id 1385503056

MySQL thread id 6976315011, query id 24543783285 172.24.0.2 readonly

---TRANSACTION 2 1594638602, not started, process no 16670, OS thread id 1409997136

MySQL thread id 6976315007, query id 24543782912 172.24.21.19 readonly

---TRANSACTION 2 1594638579, not started, process no 16670, OS thread id 1346365776

MySQL thread id 6976314184, query id 24543782960 172.24.0.2 readwrite

---TRANSACTION 2 1594638935, not started, process no 16670, OS thread id 1269156176

MySQL thread id 6976314961, query id 24543783645 172.24.0.2 readonly

---TRANSACTION 2 1594638458, not started, process no 16670, OS thread id 1429698896

MySQL thread id 6976314942, query id 24543783593 172.24.0.2 readonly

---TRANSACTION 2 1594638926, not started, process no 16670, OS thread id 1081129296

MySQL thread id 6976314918, query id 24543783618 172.24.0.2 readonly

---TRANSACTION 2 1594638427, not started, process no 16670, OS thread id 1402276176

MySQL thread id 6976314858, query id 24543782714 172.24.0.2 readwrite

---TRANSACTION 2 1594638324, not started, process no 16670, OS thread id 1219103056

MySQL thread id 6976314865, query id 24543782531 172.24.0.2 readonly

---TRANSACTION 2 1594638304, not started, process no 16670, OS thread id 1217505616

MySQL thread id 6976314851, query id 24543782334 172.24.0.2 readonly

---TRANSACTION 2 1594638242, not started, process no 16670, OS thread id 1204992336

MySQL thread id 6976314781, query id 24543782210 172.24.0.2 readwrite

---TRANSACTION 2 1594638104, not started, process no 16670, OS thread id 1208985936

MySQL thread id 6976314732, query id 24543782126 172.24.0.2 readonly

---TRANSACTION 2 1594638084, not started, process no 16670, OS thread id 1372457296

MySQL thread id 6976314714, query id 24543781873 172.24.0.2 readonly

---TRANSACTION 2 1594638083, not started, process no 16670, OS thread id 1227090256

MySQL thread id 6976314689, query id 24543783323 172.24.21.19 readwrite

---TRANSACTION 2 1594638152, not started, process no 16670, OS thread id 1285130576

MySQL thread id 6976314673, query id 24543782470 172.24.21.19 readonly

---TRANSACTION 2 1594638133, not started, process no 16670, OS thread id 1236408656

MySQL thread id 6976314670, query id 24543781963 172.24.0.2 readonly

---TRANSACTION 2 1594638689, not started, process no 16670, OS thread id 1378314576

MySQL thread id 6976314661, query id 24543783582 172.24.0.2 readonly

---TRANSACTION 2 1594637919, not started, process no 16670, OS thread id 1294981456

MySQL thread id 6976314582, query id 24543781464 172.24.0.2 readonly

---TRANSACTION 2 1594638993, not started, process no 16670, OS thread id 1230285136

MySQL thread id 6976314565, query id 24543783822 172.24.0.2 readonly

---TRANSACTION 2 1594637889, not started, process no 16670, OS thread id 1176504656

MySQL thread id 6976314558, query id 24543781388 172.24.0.2 readonly

---TRANSACTION 2 1594637695, not started, process no 16670, OS thread id 1188219216

MySQL thread id 6976314435, query id 24543783810 172.24.0.2 readonly

---TRANSACTION 2 1594636477, not started, process no 16670, OS thread id 1379645776

MySQL thread id 6976313272, query id 24543778354 172.24.0.2 readwrite

---TRANSACTION 2 1594638804, not started, process no 16670, OS thread id 1332521296

MySQL thread id 6976313552, query id 24543783311 172.24.0.2 readonly

---TRANSACTION 2 1594636327, not started, process no 16670, OS thread id 1392159056

MySQL thread id 6976313460, query id 24543777763 172.24.0.2 readonly

---TRANSACTION 2 1594638848, not started, process no 16670, OS thread id 1358080336

MySQL thread id 6976313203, query id 24543783542 172.24.21.19 readonly

---TRANSACTION 2 1594635033, not started, process no 16670, OS thread id 1082997072

MySQL thread id 6976312502, query id 24543774736 172.24.0.2 readwrite

---TRANSACTION 2 1594634847, not started, process no 16670, OS thread id 1302436176

MySQL thread id 6976312376, query id 24543780952 172.24.0.2 readwrite

---TRANSACTION 2 1594634012, not started, process no 16670, OS thread id 1280604496

MySQL thread id 6976311989, query id 24543772726 172.24.0.2 readonly

---TRANSACTION 2 1594633833, not started, process no 16670, OS thread id 1390827856

MySQL thread id 6976311765, query id 24543772353 172.24.0.2 readonly

---TRANSACTION 2 1594638984, not started, process no 16670, OS thread id 1180498256

MySQL thread id 6976311498, query id 24543783824 172.24.0.2 readonly

---TRANSACTION 2 1594638855, not started, process no 16670, OS thread id 1312819536

MySQL thread id 6976311467, query id 24543783563 172.24.0.2 readonly

---TRANSACTION 2 1594633289, not started, process no 16670, OS thread id 1084356944

MySQL thread id 6976311315, query id 24543783517 172.24.0.2 readonly

---TRANSACTION 2 1594633524, not started, process no 16670, OS thread id 1077754192

MySQL thread id 6976311161, query id 24543783604 172.24.0.2 readonly

---TRANSACTION 2 1594632775, not started, process no 16670, OS thread id 1387366736

MySQL thread id 6976310798, query id 24543783824 172.24.0.2 readonly

---TRANSACTION 2 1594638274, not started, process no 16670, OS thread id 1336781136

MySQL thread id 6976308416, query id 24543783827 172.24.0.2 readwrite

---TRANSACTION 2 1594637914, not started, process no 16670, OS thread id 1319209296

MySQL thread id 6976305431, query id 24543781455 172.24.0.2 readonly

---TRANSACTION 2 1594637639, not started, process no 16670, OS thread id 1360476496

MySQL thread id 6976286463, query id 24543780813 172.24.0.2 readonly

---TRANSACTION 2 1594601131, not started, process no 16670, OS thread id 1376450896

MySQL thread id 6976275163, query id 24543671866 172.24.0.2 readonly

---TRANSACTION 0 0, not started, process no 16670, OS thread id 1161861456

MySQL thread id 6976021353, query id 24543783828 localhost root

show innodb status

---TRANSACTION 2 1594617250, not started, process no 16670, OS thread id 1284864336

MySQL thread id 6976219191, query id 24543722876 172.24.0.2 readonly

---TRANSACTION 2 1594503382, not started, process no 16670, OS thread id 1229486416

MySQL thread id 6976183067, query id 24543401940 172.24.0.2 readwrite

---TRANSACTION 2 1594615159, not started, process no 16670, OS thread id 1166653776

MySQL thread id 6976103244, query id 24543716049 172.24.0.2 readonly

---TRANSACTION 2 1594261924, not started, process no 16670, OS thread id 1313085776

MySQL thread id 6975951203, query id 24542727637 172.24.21.19 readonly

---TRANSACTION 2 1593727564, not started, process no 16670, OS thread id 1166387536

MySQL thread id 6975473457, query id 24541302242 172.24.21.19 readonly

---TRANSACTION 2 1593159738, not started, process no 16670, OS thread id 1284331856

MySQL thread id 6975003450, query id 24539858051 172.24.21.19 readonly

---TRANSACTION 2 1594638985, not started, process no 16670, OS thread id 1169848656

MySQL thread id 6974686686, query id 24543783806 172.24.0.2 readonly

---TRANSACTION 2 1592767036, not started, process no 16670, OS thread id 1176770896

MySQL thread id 6974686679, query id 24543783798 172.24.0.2 readwrite

---TRANSACTION 2 1592133017, not started, process no 16670, OS thread id 1225759056

MySQL thread id 6974128272, query id 24537203401 172.24.21.19 readonly

---TRANSACTION 0 0, not started, process no 16670, OS thread id 1615534416

MySQL thread id 5962897775, query id 24543752819 localhost mysqlmonitor

---TRANSACTION 2 1594456162, not started, process no 16670, OS thread id 1185290576

MySQL thread id 4250603963, query id 24543259479 Has read all relay log; waiting for the slave I/O thread to update it

The above details some of the ongoing transaction - similar to 'show processlist' "Trx id counter 2 1594638995" Is a counter that increments upon transactions since server start.

"Purge done for trx's n:o <>

"History list length 928" - Again, I'm not entirely sure that this variable means.

---TRANSACTION 0 0, not started, process no 16670, OS thread id 1206856016

mysql tables in use 1, locked 1

MySQL thread id 6976314925, query id 24543783827 172.24.0.2 readwrite init

UPDATE Subscribers SET EmailAddress = '12618060932336258813', RealEstProps = '106370836 104481172 105731349 105958174 105958282 105969863 105974146 105998765 106010720 106078593 106102921 106150346 106322759 106337015 106340214 106312885 106343690 106159839 106207162 106290970', LastAccess = 1268028314 WHERE EmailAddress = '12618060932336258813'

Information like the above is similar to the output of 'show processlist' at that exact point in time. As I understand it, similar to 'show processlist', if you see many transactions here you have a concurrency performance issue.

--------

FILE I/O

--------

I/O thread 0 state: waiting for i/o request (insert buffer thread)

I/O thread 1 state: waiting for i/o request (log thread)

I/O thread 2 state: waiting for i/o request (read thread)

I/O thread 3 state: waiting for i/o request (write thread)

Pending normal aio reads: 0, aio writes: 0,

ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0

Pending flushes (fsync) log: 0; buffer pool: 0

35818914 OS file reads, 248305163 OS file writes, 47414036 OS fsyncs

4.57 reads/s, 27392 avg bytes/read, 16.57 writes/s, 2.14 fsyncs/s

This shows how Innodb is going reading and writing to disk. There are four threads by default - the buffer pool thread, the log thread, reading and writing. What we see is no pending 'aios' (async io) - which means the server is not waiting on disk. The second last line shows the number of reads, writes and filesystem syncs that have been performed since sever stat - this database server (readb01.kp) is mixed and does slightly more reads than writes.

The last line shows the averages per second since the interval listed at the top of the 'show innodb status' output. So clearly there were some write transactions and not mean read transactions going on.

Again, theres no pending aio's so the server is not IO bound.

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 131, seg size 133,

4000946 inserts, 4000946 merged recs, 601283 merges

Hash table size 26690981, node heap has 69134 buffer(s)

678.95 hash searches/s, 2176.34 non-hash searches/s

Now I'm a little green on the output of the above. The insert buffer is related to the insert buffer thread and the adaptive hash index in the algorithm Innodb uses to insert data into the tablespace.

---

LOG

---

Log sequence number 327 3878392573

Log flushed up to 327 3878369439

Last checkpoint at 327 3640927886

0 pending log writes, 0 pending chkp writes

36089932 log i/o's done, 1.36 log i/o's/second

The above information pertains to the circle logs in Innodb - This shows how much data has not been flushed to disk that still remains in the innodb circle logs. (3878392573-3878369439) 23134 bytes. The innodb log files are set to around 160Mb to reduce the amount of IO at the cost of start up time upon crash.

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 15308894706; in additional pool allocated 45677312

Dictionary memory allocated 658920

Buffer pool size 823168

Free buffers 0

Database pages 754034

Modified db pages 29518

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages read 44983219, created 3689633, written 250224524

7.64 reads/s, 0.07 creates/s, 19.64 writes/s

Buffer pool hit rate 1000 / 1000

The total memory is the total memory Innodb has allocated - 15Gb - the additional buffer pool is set to ~400Mb (for data dictionary etc).

Dictionary memory allocated is much less than the additional buffer pool size suggesting that the additional buffer pool memory size can be dropped.

The buffer pool is used to cache the tablespace, store locks and most things innodb so it needs to be set quiet large. What I take into account is the last line which is a ratio output of the usefulness of the innodb buffer pool - the hit rate is currently 1000/1000 or 100% - in other words all transactions, be them read or write can go via the buffer pool instead of missing the buffer pool and going directly to disk.

--------------

ROW OPERATIONS

--------------

1 queries inside InnoDB, 0 queries in queue

1 read views open inside InnoDB

Main thread process no. 16670, id 1156270416, state: sleeping

Number of rows inserted 200754864, updated 1377590681, deleted 77291280, read 722941029741

4.50 inserts/s, 116.85 updates/s, 0.00 deletes/s, 96297.19 reads/s

----------------------------

END OF INNODB MONITOR OUTPUT

============================

1 row in set, 1 warning (0.00 sec)

The '0 queries in the queue' shows that there are currently no transactions in the Innodb kernel waiting to execute. The remaining lines show the type of work Innodb is doing.

PlanetMySQL Voting: Vote UP / Vote DOWN

In MySQL5 MySQL implemented changes so you can view global status (counters) and session counters. In 5.1 MySQL extended this and implemented this information in the INFORMATION_SCHEMA.GLOBAL_STATUS and INFORMATION_STATUS.SESSION_STATUS tables.
PlanetMySQL Voting: Vote UP / Vote DOWN

A Hard Look Into Replication

Seconds Behind Master

When the slave SQL thread is actively running
(processing updates), this field is the number of
seconds that have elapsed since the timestamp of the
most recent event on the master executed by that thread.

Binary Log Format

Status Variables and Logs

Conclusion

PlanetMySQL Voting: Vote UP / Vote DOWN

This presentation was be done by Sheeri Cabral of The Pythian Group and went into how to use SHOW ENGINE INNODB STATUS to get more information about your Innodb tables, foreign keys and transactions. This is a great presentation to learn how InnoDB works.

It also went through how to use SHOW ENGINE INNODB STATUS to tune several InnoDB variables:

innodb_adaptive_hash_index
innodb_commit_concurrency
innodb_concurrency_tickets
innodb_file_io_threads
innodb_log_buffer_size
innodb_max_purge_lag
innodb_sync_spin_loops
innodb_thread_concurrency
innodb_thread_sleep_delay

The slides can be downloaded from:

http://technocation.org/files/doc/ShowEngineInnoDBStatus.pdf

(note that the slides open up to the middle section, which has slides + notes, but if you just want the slides without notes, that starts on page 1 of the PDF)

The video can be watched below, or directly on YouTube at http://www.youtube.com/watch?v=ocdjspoLM58

PlanetMySQL Voting: Vote UP / Vote DOWN