MySQL problem...
12 views
Skip to first unread message
Silviu Hutanu
Mar 11, 2014, 4:59:52 PM3/11/14
to lu...@googlegroups.com
Salut LUG BV...Salut Doru...
Am o problema cu niste prieteni cu care colaborez de ceva vreme...mysql> show processlist;
+----------+----------+----------------------+----------+-------------+--------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+----------+----------------------+----------+-------------+--------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 85 | root | 192.168.216.25:51589 | NULL | Binlog Dump | 948029 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 86 | root | 192.168.216.12:40501 | NULL | Binlog Dump | 948029 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 87 | root | 192.168.216.70:55839 | NULL | Binlog Dump | 948029 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 12098863 | root | 127.0.0.1:46809 | bob_live | Sleep | 52 | | NULL |
| 12098864 | root | 127.0.0.1:46837 | NULL | Sleep | 2 | | NULL |
| 12142927 | root | 192.168.216.69:45472 | bob_live | Sleep | 1796 | | NULL |
| 12142932 | root | 192.168.216.69:45629 | NULL | Sleep | 1815 | | NULL |
| 12155311 | root | localhost | NULL | Sleep | 26584 | | NULL |
| 12319035 | bob_live | 192.168.216.69:51631 | bob_live | Sleep | 1049 | | NULL |
| 12319036 | bob_live | 192.168.216.69:51632 | bob_live | Sleep | 1049 | | NULL |
| 12319038 | bob_live | 192.168.216.69:51634 | bob_live | Sleep | 1049 | | NULL |
| 12319039 | bob_live | 192.168.216.69:51635 | bob_live | Sleep | 1049 | | NULL |
| 12320106 | root | localhost | bob_live | Query | 51 | NULL | load data local infile '/root/fk/datacleanup/2013-04-30-bob_live.customer_registration_data_step1' i |
| 12320123 | root | localhost | bob_live | Query | 0 | NULL | show processlist |
| 12320150 | bob_live | 192.168.216.79:33982 | bob_live | Query | 50 | update | INSERT INTO `customer_registration_data` (`fk_customer`, `key`, `value`, `created_at`) VALUES ('1204 |
+----------+----------+----------------------+----------+-------------+--------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
15 rows in set (0.00 sec)
mysql> show processlist;
+----------+----------+----------------------+----------+-------------+--------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+----------+----------------------+----------+-------------+--------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 85 | root | 192.168.216.25:51589 | NULL | Binlog Dump | 948032 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 86 | root | 192.168.216.12:40501 | NULL | Binlog Dump | 948032 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 87 | root | 192.168.216.70:55839 | NULL | Binlog Dump | 948032 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 12098863 | root | 127.0.0.1:46809 | bob_live | Sleep | 55 | | NULL |
| 12098864 | root | 127.0.0.1:46837 | NULL | Sleep | 5 | | NULL |
| 12142927 | root | 192.168.216.69:45472 | bob_live | Sleep | 1799 | | NULL |
| 12142932 | root | 192.168.216.69:45629 | NULL | Sleep | 1818 | | NULL |
| 12155311 | root | localhost | NULL | Sleep | 26587 | | NULL |
| 12319035 | bob_live | 192.168.216.69:51631 | bob_live | Sleep | 1052 | | NULL |
| 12319036 | bob_live | 192.168.216.69:51632 | bob_live | Sleep | 1052 | | NULL |
| 12319038 | bob_live | 192.168.216.69:51634 | bob_live | Sleep | 1052 | | NULL |
| 12319039 | bob_live | 192.168.216.69:51635 | bob_live | Sleep | 1052 | | NULL |
| 12320106 | root | localhost | bob_live | Query | 54 | NULL | load data local infile '/root/fk/datacleanup/2013-04-30-bob_live.customer_registration_data_step1' i |
| 12320123 | root | localhost | bob_live | Query | 0 | NULL | show processlist |
| 12320201 | bob_live | 192.168.216.71:54194 | bob_live | Query | 3 | Updating | UPDATE `customer` SET `number_login` = '3' WHERE (id_customer = 694645) |
| 12320208 | bob_live | 192.168.216.79:34318 | bob_live | Query | 1 | Updating | UPDATE `customer` SET `number_login` = '3' WHERE (id_customer = 308883) |
+----------+----------+----------------------+----------+-------------+--------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
16 rows in set (0.00 sec)
mysql> show processlist;
+----------+----------+----------------------+----------+-------------+--------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+----------+----------------------+----------+-------------+--------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 85 | root | 192.168.216.25:51589 | NULL | Binlog Dump | 948044 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 86 | root | 192.168.216.12:40501 | NULL | Binlog Dump | 948044 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 87 | root | 192.168.216.70:55839 | NULL | Binlog Dump | 948044 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 12098863 | root | 127.0.0.1:46809 | bob_live | Sleep | 7 | | NULL |
| 12098864 | root | 127.0.0.1:46837 | NULL | Sleep | 17 | | NULL |
| 12142927 | root | 192.168.216.69:45472 | bob_live | Sleep | 1811 | | NULL |
| 12142932 | root | 192.168.216.69:45629 | NULL | Sleep | 1830 | | NULL |
| 12155311 | root | localhost | NULL | Sleep | 26599 | | NULL |
| 12319035 | bob_live | 192.168.216.69:51631 | bob_live | Sleep | 1064 | | NULL |
| 12319036 | bob_live | 192.168.216.69:51632 | bob_live | Sleep | 1064 | | NULL |
| 12319038 | bob_live | 192.168.216.69:51634 | bob_live | Sleep | 1064 | | NULL |
| 12319039 | bob_live | 192.168.216.69:51635 | bob_live | Sleep | 1064 | | NULL |
| 12320106 | root | localhost | bob_live | Query | 66 | NULL | load data local infile '/root/fk/datacleanup/2013-04-30-bob_live.customer_registration_data_step1' i |
| 12320123 | root | localhost | bob_live | Query | 0 | NULL | show processlist |
| 12320201 | bob_live | 192.168.216.71:54194 | bob_live | Query | 15 | Updating | UPDATE `customer` SET `number_login` = '3' WHERE (id_customer = 694645) |
| 12320208 | bob_live | 192.168.216.79:34318 | bob_live | Query | 13 | Updating | UPDATE `customer` SET `number_login` = '3' WHERE (id_customer = 308883) |
+----------+----------+----------------------+----------+-------------+--------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
16 rows in set (0.00 sec)
mysql> show engine innodb status;
+--------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | |
=====================================
130508 0:47:51 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 42 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 877575 1_second, 877323 sleeps, 86668 10_second, 43144 background, 43144 flush
srv_master_thread log flush and writes: 887788
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3210932, signal count 87767169
Mutex spin waits 165791636, rounds 219143918, OS waits 1934991
RW-shared spins 24240162, OS waits 592478; RW-excl spins 51289992, OS waits 524960
Spin rounds per wait: 1.32 mutex, 4.79 RW-shared, 5.51 RW-excl
--------
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 (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 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
427399 OS file reads, 75391944 OS file writes, 2827267 OS fsyncs
0.31 reads/s, 16384 avg bytes/read, 93.59 writes/s, 24.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 71, seg size 73,
61902 inserts, 61902 merged recs, 60350 merges
Hash table size 31874729, node heap has 36734 buffer(s)
267693.20 hash searches/s, 57622.46 non-hash searches/s
---
LOG
---
Log sequence number 6772984543987
Log flushed up to 6772974250797
Last checkpoint at 6772615405595
Max checkpoint age 433682474
Checkpoint age target 420129897
Modified age 341018365
Checkpoint age 369138392
0 pending log writes, 0 pending chkp writes
67520824 log i/o's done, 9.69 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 16522936320; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
Adaptive hash index 856851904 (254997832 + 601854072)
Page hash 15938152
Dictionary cache 66355433 (63751024 + 2604409)
File system 230848 (82672 + 148176)
Lock system 41418952 (39844312 + 1574640)
Recovery system 0 (0 + 0)
Threads 408112 (406936 + 1176)
Dictionary memory allocated 2604409
Buffer pool size 983039
Buffer pool size, bytes 16106110976
Free buffers 1
Database pages 946209
Old database pages 349264
Modified db pages 23446
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2435539, not young 0
553.77 youngs/s, 0.00 non-youngs/s
Pages read 746550, created 11407492, written 29753244
0.31 reads/s, 106.62 creates/s, 863.12 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 946209, unzip_LRU len: 0
I/O sum[41960]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 8213, id 140004868577024, state: sleeping
Number of rows inserted 4143211299, updated 7100917, deleted 2086448420, read 99987571805
50627.32 inserts/s, 0.07 updates/s, 0.02 deletes/s, 14243.57 reads/s
------------
TRANSACTIONS
------------
Trx id counter 6800E58C
Purge done for trx's n:o < 6800E58B undo n:o < 0
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, process no 8213, OS thread id 140004809389824
MySQL thread id 12320123, query id 278164910 localhost root
show engine innodb status
---TRANSACTION 68007FF9, not started, process no 8213, OS thread id 140004805977856
MySQL thread id 12319039, query id 278124194 192.168.216.69 bob_live
---TRANSACTION 68007FFB, not started, process no 8213, OS thread id 140004806379264
MySQL thread id 12319038, query id 278124195 192.168.216.69 bob_live
---TRANSACTION 68007FFC, not started, process no 8213, OS thread id 140004804372224
MySQL thread id 12319036, query id 278124188 192.168.216.69 bob_live
---TRANSACTION 68007FF7, not started, process no 8213, OS thread id 140004809791232
MySQL thread id 12319035, query id 278124173 192.168.216.69 bob_live
---TRANSACTION 67DF2CFF, not started, process no 8213, OS thread id 140004816013056
MySQL thread id 12142927, query id 278092529 192.168.216.69 root
---TRANSACTION 67D2DBA6, not started, process no 8213, OS thread id 140004806579968
MySQL thread id 12098863, query id 278164870 127.0.0.1 root
---TRANSACTION 6800E57A, ACTIVE 18 sec, process no 8213, OS thread id 140004815611648 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 12320208, query id 278164857 192.168.216.79 bob_live Updating
UPDATE `customer` SET `number_login` = '3' WHERE (id_customer = 308883)
------- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 313477 page no 11184 n bits 136 index `PRIMARY` of table `bob_live`.`customer` trx id 6800E57A lock_mode X locks rec but not gap waiting
------------------
TABLE LOCK table `bob_live`.`customer` trx id 6800E57A lock mode IX
RECORD LOCKS space id 313477 page no 11184 n bits 136 index `PRIMARY` of table `bob_live`.`customer` trx id 6800E57A lock_mode X locks rec but not gap waiting
---TRANSACTION 6800E54E, ACTIVE 20 sec, process no 8213, OS thread id 140004812601088 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 12320201, query id 278164781 192.168.216.71 bob_live Updating
UPDATE `customer` SET `number_login` = '3' WHERE (id_customer = 694645)
------- TRX HAS BEEN WAITING 20 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 313477 page no 25709 n bits 128 index `PRIMARY` of table `bob_live`.`customer` trx id 6800E54E lock_mode X locks rec but not gap waiting
------------------
TABLE LOCK table `bob_live`.`customer` trx id 6800E54E lock mode IX
RECORD LOCKS space id 313477 page no 25709 n bits 128 index `PRIMARY` of table `bob_live`.`customer` trx id 6800E54E lock_mode X locks rec but not gap waiting
---TRANSACTION 6800E1FA, ACTIVE 71 sec, process no 8213, OS thread id 140004811798272 inserting, thread declared inside InnoDB 284
mysql tables in use 1, locked 1
17360 lock struct(s), heap size 1571256, 978963 row lock(s), undo log entries 3450604
MySQL thread id 12320106, query id 278163465 localhost root
load data local infile '/root/fk/datacleanup/2013-04-30-bob_live.customer_registration_data_step1' into table customer_registration_data
TABLE LOCK table `bob_live`.`customer_registration_data` trx id 6800E1FA lock mode IX
TABLE LOCK table `bob_live`.`customer` trx id 6800E1FA lock mode IS
RECORD LOCKS space id 313477 page no 11 n bits 136 index `PRIMARY` of table `bob_live`.`customer` trx id 6800E1FA lock mode S locks rec but not gap
TABLE LOCK table `bob_live`.`customer_registration_data` trx id 6800E1FA lock mode AUTO-INC
RECORD LOCKS space id 313477 page no 12 n bits 128 index `PRIMARY` of table `bob_live`.`customer` trx id 6800E1FA lock mode S locks rec but not gap
RECORD LOCKS space id 313477 page no 13 n bits 128 index `PRIMARY` of table `bob_live`.`customer` trx id 6800E1FA lock mode S locks rec but not gap
RECORD LOCKS space id 313477 page no 14 n bits 128 index `PRIMARY` of table `bob_live`.`customer` trx id 6800E1FA lock mode S locks rec but not gap
RECORD LOCKS space id 313477 page no 15 n bits 128 index `PRIMARY` of table `bob_live`.`customer` trx id 6800E1FA lock mode S locks rec but not gap
RECORD LOCKS space id 313477 page no 16 n bits 128 index `PRIMARY` of table `bob_live`.`customer` trx id 6800E1FA lock mode S locks rec but not gap
RECORD LOCKS space id 313477 page no 17 n bits 128 index `PRIMARY` of table `bob_live`.`customer` trx id 6800E1FA lock mode S locks rec but not gap
TOO MANY LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS
----------------------------
END OF INNODB MONITOR OUTPUT
============================
|
+--------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Reply all
Reply to author
Forward
0 new messages