MySQL Forums
Forum List  »  InnoDB

Database hungs
Posted by: Michal Kurgan
Date: January 17, 2013 03:52PM

Recently i started to observe strange MySQL hangs that happen sporadically. It looks like threads that manage to get inside the InnoDB kernel are just waiting there causing great havoc. As per configuration 8 threads are processed concurrently but nothing happens (last 8 transactions in the output). I have no idea how to debug and resolve the problem that is causing this wait. Any idea what may be the cause?

Here are some details about InnoDB status during such prolonged hang with additional information about tables that are processed, etc. I did remove transactions that were analogous the other waiting transaction before and after them in the status output.

Thanks in advance for help and your time.

$ mysql --version
mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1

mysql> show engine innodb status;
+----------------------------------------------------------------------------------------------------------------------------------------------------
| Status
+----------------------------------------------------------------------------------------------------------------------------------------------------
|
=====================================
130117 14:21:28 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 32 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 500992476, signal count 424872833
Mutex spin waits 0, rounds 84325675415, OS waits 141673210
RW-shared spins 519650182, OS waits 211141086; RW-excl spins 264836484, OS waits 31954121
------------------------
LATEST DETECTED DEADLOCK
------------------------
121225 17:53:20
*** (1) TRANSACTION:
TRANSACTION 1 1122730579, ACTIVE 0 sec, process no 4419, OS thread id 1328355648 inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1216
MySQL thread id 750877018, query id 7560152383 localhost fluik_services update
INSERT INTO app_dev_buys SET app_id = '66', dev_id = '3604686',
app_version = '10800039', os_id = '2433', stat_id = '167058', type = 'apple', product_id = '18', transactionid = 'A220000013331967',
rare_currency = '0', common_currency = '0', value = '99', country = 'US',
created_at = UNIX_TIMESTAMP()
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2275 page no 3087 n bits 288 index `PRIMARY` of table `fluik_services/app_dev_buys` trx id 1 1122730579 lock_mode X locks gap b
Record lock, heap no 196 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 2; hex 0042; asc B;; 1: len 4; hex 003709a4; asc 7 ;; 2: len 1; hex 03; asc ;; 3: len 4; hex d09464d1; asc d ;; 4: len 6; hex 000119c95

*** (2) TRANSACTION:
TRANSACTION 1 1122730581, ACTIVE 0 sec, process no 4419, OS thread id 1549867328 inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1216
MySQL thread id 750877019, query id 7560152394 localhost fluik_services update
INSERT INTO app_dev_buys SET app_id = '66', dev_id = '3604686',
app_version = '10800039', os_id = '2433', stat_id = '167059', type = 'apple', product_id = '15', transactionid = 'A220000013083770',
rare_currency = '0', common_currency = '0', value = '99', country = 'US',
created_at = UNIX_TIMESTAMP()
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2275 page no 3087 n bits 288 index `PRIMARY` of table `fluik_services/app_dev_buys` trx id 1 1122730581 lock mode S locks gap b
Record lock, heap no 196 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 2; hex 0042; asc B;; 1: len 4; hex 003709a4; asc 7 ;; 2: len 1; hex 03; asc ;; 3: len 4; hex d09464d1; asc d ;; 4: len 6; hex 000119c95

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2275 page no 3087 n bits 288 index `PRIMARY` of table `fluik_services/app_dev_buys` trx id 1 1122730581 lock_mode X locks gap b
Record lock, heap no 196 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 2; hex 0042; asc B;; 1: len 4; hex 003709a4; asc 7 ;; 2: len 1; hex 03; asc ;; 3: len 4; hex d09464d1; asc d ;; 4: len 6; hex 000119c95

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 1 1417365222
Purge done for trx's n:o < 1 1417365032 undo n:o < 0 0
History list length 2
Total number of lock structs in row lock hash table 0
... truncated...
ECT * FROM devs WHERE udid = 'D201d9258b56b2c2ee6399f2aaceaee6b' LIMIT 0,1
---TRANSACTION 1 1417365201, ACTIVE 36 sec, process no 4419, OS thread id 1230911808 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849715, query id 8262341431 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = '5734dac8cafa3a3febd399a3c9d17f8dbe0ba774' LIMIT 0,1

[ ... ]

---TRANSACTION 1 1417365091, ACTIVE 41 sec, process no 4419, OS thread id 1344330048 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849605, query id 8262340988 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = 'ea2a472e5f12c1235cab37d5576d719037675f1c' LIMIT 0,1
---TRANSACTION 1 1417365090, ACTIVE 41 sec, process no 4419, OS thread id 1214138688 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849604, query id 8262340984 localhost fluik_services statistics
SELECT * FROM app_devs WHERE app_id = '120' AND dev_id = '27350360' LIMIT 0,1
Trx read view will not see trx with id >= 1 1417365091, sees < 1 1417364223
---TRANSACTION 1 1417365089, ACTIVE 41 sec, process no 4419, OS thread id 1318771008 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849603, query id 8262340978 localhost fluik_services statistics
SELECT * FROM app_devs WHERE app_id = '136' AND dev_id = '30959625' LIMIT 0,1
Trx read view will not see trx with id >= 1 1417365090, sees < 1 1417364223
---TRANSACTION 1 1417365088, ACTIVE 42 sec, process no 4419, OS thread id 1235704128 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849602, query id 8262340972 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = 'a3fc9b19d20c820a1d0a2c7a0e2c1d62c537ff60' LIMIT 0,1

[ ... ]

---TRANSACTION 1 1417365061, ACTIVE 43 sec, process no 4419, OS thread id 1163020608 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849575, query id 8262340860 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = '4e72eeb1d4b091abd056b4e5317912a9ac0e072e' LIMIT 0,1
---TRANSACTION 1 1417365060, ACTIVE 43 sec, process no 4419, OS thread id 1453754688 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849573, query id 8262340856 localhost fluik_services statistics
SELECT * FROM app_devs WHERE app_id = '120' AND dev_id = '27350360' LIMIT 0,1
Trx read view will not see trx with id >= 1 1417365061, sees < 1 1417364223
---TRANSACTION 1 1417365059, ACTIVE 43 sec, process no 4419, OS thread id 1164884288 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849574, query id 8262340855 localhost fluik_services statistics
SELECT * FROM app_devs WHERE app_id = '136' AND dev_id = '28169949' LIMIT 0,1
Trx read view will not see trx with id >= 1 1417365060, sees < 1 1417364223
---TRANSACTION 1 1417365058, ACTIVE 43 sec, process no 4419, OS thread id 1458014528 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849572, query id 8262340844 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = '55c68d5c7bff72e542d4fec05dcb5516a5326d51' LIMIT 0,1

[ ... ]

---TRANSACTION 1 1417365050, ACTIVE 43 sec, process no 4419, OS thread id 1172339008 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849564, query id 8262340812 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = '3b813478f7e404f22f078ef22619b0c33bf9b9be' LIMIT 0,1
---TRANSACTION 1 1417365049, ACTIVE 43 sec, process no 4419, OS thread id 1449228608 waiting in InnoDB queue
mysql tables in use 2, locked 0
MySQL thread id 814849563, query id 8262340808 localhost fluik_services statistics
SELECT launch_count, ads.created_at AS installed_at, avds.created_at AS updated_at, country, dev_type_id, versions
FROM app_version_devs AS avds
LEFT JOIN app_devs AS ads USING(app_id, dev_id) WHERE
avds.app_id = '141' AND avds.app_version = '10600289' AND avds.dev_id = '33138900'
---TRANSACTION 1 1417365048, ACTIVE 43 sec, process no 4419, OS thread id 1177397568 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849562, query id 8262340802 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = '33906d6f7e2b4c784b4a96b8a144aea7118caeb5' LIMIT 0,1
---TRANSACTION 1 1417365047, ACTIVE 43 sec, process no 4419, OS thread id 1199495488 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849561, query id 8262340798 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = 'c5bb00f04ca9a00961da9325511816cfce0773b4' LIMIT 0,1
---TRANSACTION 1 1417365046, ACTIVE 43 sec, process no 4419, OS thread id 1441773888 waiting in InnoDB queue
mysql tables in use 2, locked 0
MySQL thread id 814849560, query id 8262340794 localhost fluik_services statistics
SELECT launch_count, ads.created_at AS installed_at, avds.created_at AS updated_at, country, dev_type_id, versions
FROM app_version_devs AS avds
LEFT JOIN app_devs AS ads USING(app_id, dev_id) WHERE
avds.app_id = '136' AND avds.app_version = '10300009' AND avds.dev_id = '32275099'
---TRANSACTION 1 1417365045, ACTIVE 44 sec, process no 4419, OS thread id 1315842368 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849559, query id 8262340788 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = 'D28a51d7d95c4a58ea3d58e12e2f1f88e' LIMIT 0,1
---TRANSACTION 1 1417365044, ACTIVE 44 sec, process no 4419, OS thread id 1264724288 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849558, query id 8262340784 localhost fluik_services statistics
SELECT * FROM app_devs WHERE app_id = '136' AND dev_id = '30247985' LIMIT 0,1
Trx read view will not see trx with id >= 1 1417365045, sees < 1 1417364223
---TRANSACTION 1 1417365043, ACTIVE 44 sec, process no 4419, OS thread id 1260464448 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849556, query id 8262340778 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = '8e396f47b17813460f1210ce40bce2495ac83318' LIMIT 0,1
---TRANSACTION 1 1417365042, ACTIVE 44 sec, process no 4419, OS thread id 1320368448 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849557, query id 8262340777 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = '8943f46af3d91e5ecd67b0076db22ea6c3908cf0' LIMIT 0,1
---TRANSACTION 1 1417365041, ACTIVE 44 sec, process no 4419, OS thread id 1426331968 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849555, query id 8262340770 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = 'D99f55aa88e0fa904291e170731cfc8c2' LIMIT 0,1
---TRANSACTION 1 1417365040, ACTIVE 44 sec, process no 4419, OS thread id 1458547008 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368
MySQL thread id 814849544, query id 8262340729 localhost fluik_services update
INSERT INTO app_version_devs SET dev_id = '33137829', app_id = '146', app_version = '10200079',
launch_count = '1', crash_count = '0', playtime = '0',
created_at = UNIX_TIMESTAMP()
ON DUPLICATE KEY UPDATE
launch_count = '1', crash_count = '0', playtime = '0'
---TRANSACTION 1 1417365038, ACTIVE 44 sec, process no 4419, OS thread id 1456949568 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849554, query id 8262340765 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = 'Dd07d7b27ff4ac363bc7f2220a3e24155' LIMIT 0,1
---TRANSACTION 1 1417365037, ACTIVE 44 sec, process no 4419, OS thread id 1281231168 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849553, query id 8262340761 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = '86790219c2cd19dc1ce1f3fc54e6e656bb4c4257' LIMIT 0,1
---TRANSACTION 1 1417365036, ACTIVE 44 sec, process no 4419, OS thread id 1168345408 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849552, query id 8262340757 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = 'bc092708c600cf318de0ca0f986b38555c8db59a' LIMIT 0,1
---TRANSACTION 1 1417365035, ACTIVE 44 sec, process no 4419, OS thread id 1217333568 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849548, query id 8262340750 localhost fluik_services statistics
SELECT * FROM msgs WHERE id = '150' LIMIT 0,1
Trx read view will not see trx with id >= 1 1417365036, sees < 1 1417364223
---TRANSACTION 1 1417365033, ACTIVE 44 sec, process no 4419, OS thread id 1325959488 setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 1 lock struct(s), heap size 368
MySQL thread id 814849546, query id 8262340746 localhost fluik_services update
INSERT INTO devs SET udid = 'daa36eb6130ba0c3c59c4f8721359b1adf807963'
------- TRX HAS BEEN WAITING 44 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `fluik_services/devs` trx id 1 1417365033 lock mode AUTO-INC waiting
------------------
---TRANSACTION 1 1417365030, ACTIVE 44 sec, process no 4419, OS thread id 1219197248 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849551, query id 8262340743 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = '52d719430bb556c94cb4279e95f234b2b2bd6161' LIMIT 0,1
---TRANSACTION 1 1417365029, ACTIVE 44 sec, process no 4419, OS thread id 1377876288 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849550, query id 8262340739 localhost fluik_services statistics
SELECT * FROM devs WHERE udid = 'f8ddb947e4dc7e1d42a4b677d05be81f0a81a41a' LIMIT 0,1
---TRANSACTION 1 1417365028, ACTIVE 44 sec, process no 4419, OS thread id 1416747328 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 814849549, query id 8262340735 localhost fluik_services statistics
SELECT * FROM app_devs WHERE app_id = '120' AND dev_id = '27350360' LIMIT 0,1
Trx read view will not see trx with id >= 1 1417365029, sees < 1 1417364223
---TRANSACTION 1 1417365023, ACTIVE 44 sec, process no 4419, OS thread id 1439910208 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368
MySQL thread id 814849541, query id 8262340691 localhost fluik_services update
INSERT INTO app_version_devs SET dev_id = '33013501', app_id = '120', app_version = '10300009',
launch_count = '10', crash_count = '0', playtime = '2321',
created_at = UNIX_TIMESTAMP()
ON DUPLICATE KEY UPDATE
launch_count = '10', crash_count = '0', playtime = '2321'
---TRANSACTION 1 1417365014, ACTIVE 44 sec, process no 4419, OS thread id 1206151488 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368
MySQL thread id 814849540, query id 8262340673 localhost fluik_services update
INSERT INTO app_version_devs SET dev_id = '16245647', app_id = '20', app_version = '10800039',
launch_count = '4', crash_count = '0', playtime = '1137',
created_at = UNIX_TIMESTAMP()
ON DUPLICATE KEY UPDATE
launch_count = '4', crash_count = '0', playtime = '1137'
---TRANSACTION 1 1417365004, ACTIVE 44 sec, process no 4419, OS thread id 1453488448 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368
MySQL thread id 814849536, query id 8262340644 localhost fluik_services update
INSERT INTO devs SET udid = '377b288a199db8dab3afbf438cfa57eb42e91daa'
---TRANSACTION 1 1417364986, ACTIVE 45 sec, process no 4419, OS thread id 1315043648 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368, undo log entries 1
MySQL thread id 814849530, query id 8262340597 localhost fluik_services update
INSERT INTO app_dev_pushes SET dev_id = '32912804', app_id = '20',
updated_at = UNIX_TIMESTAMP(), app_version = '10800039', token = 'Oz1xC6/1mHkw8Olz+TasfPraBwfNk+u1rRUP57AjnVU=', timezone = '-6', country = 'US'
ON DUPLICATE KEY UPDATE
updated_at = UNIX_TIMESTAMP(), app_version = '10800039', token = 'Oz1xC6/1mHkw8Olz+TasfPraBwfNk+u1rRUP57AjnVU=', timezone = '-6', country = 'US'
---TRANSACTION 1 1417364982, ACTIVE 45 sec, process no 4419, OS thread id 1394649408 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368
MySQL thread id 814849529, query id 8262340585 localhost fluik_services update
INSERT INTO app_version_devs SET dev_id = '7496291', app_id = '23', app_version = '10800039',
launch_count = '5', crash_count = '0', playtime = '1444',
created_at = UNIX_TIMESTAMP()
ON DUPLICATE KEY UPDATE
launch_count = '5', crash_count = '0', playtime = '1444'
---TRANSACTION 1 1417364969, ACTIVE 45 sec, process no 4419, OS thread id 1257535808 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368
MySQL thread id 814849524, query id 8262340541 localhost fluik_services update
INSERT INTO msg_devs SET msg_id = '642', dev_id = '33138451',
clicks = 0, views = 1,
created_at = UNIX_TIMESTAMP(), updated_at = UNIX_TIMESTAMP()
ON DUPLICATE KEY UPDATE
views = views + 1,
updated_at = UNIX_TIMESTAMP()
---TRANSACTION 1 1417364223, ACTIVE 51 sec, process no 4419, OS thread id 1446033728 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368
MySQL thread id 814849353, query id 8262338732 localhost fluik_services update
INSERT INTO msg_devs SET msg_id = '624', dev_id = '31976980',
clicks = 0, views = 1,
created_at = UNIX_TIMESTAMP(), updated_at = UNIX_TIMESTAMP()
ON DUPLICATE KEY UPDATE
views = views + 1,
updated_at = UNIX_TIMESTAMP()
--------
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
426071763 OS file reads, 1879988329 OS file writes, 399621309 OS fsyncs
0 pending preads, 1 pending pwrites
0.00 reads/s, 0 avg bytes/read, 7.87 writes/s, 0.56 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5496, seg size 5498,
3892282 inserts, 3892282 merged recs, 3480794 merges
Hash table size 12750011, used cells 7478040, node heap has 16603 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 224 400136832
Log flushed up to 224 400136832
Last checkpoint at 224 393282445
0 pending log writes, 0 pending chkp writes
1148098659 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 7181806082; in additional pool allocated 1048576
Buffer pool size 393216
Free buffers 0
Database pages 376613
Modified db pages 10262
Pending reads 0
Pending writes: LRU 0, flush list 129, single page 0
Pages read 541020099, created 8898289, written 908637390
0.00 reads/s, 0.00 creates/s, 9.94 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
8 queries inside InnoDB, 191 queries in queue
10 read views open inside InnoDB
Main thread process no. 4419, id 1160358208, state: waiting for buffer pool flush to end
Number of rows inserted 1087940482, updated 1584310191, deleted 9539400, read 15600333671583
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

mysql> show variables like '%innodb%';
+---------------------------------+------------------------+
| Variable_name | Value |
+---------------------------------+------------------------+
| have_innodb | YES |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 6442450944 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_adaptive_hash_index | ON |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
+---------------------------------+------------------------+

mysql> show create table app_dev_pushes\G
*************************** 1. row ***************************
Table: app_dev_pushes
Create Table: CREATE TABLE `app_dev_pushes` (
`app_id` smallint(5) unsigned NOT NULL,
`dev_id` int(10) unsigned NOT NULL,
`token` blob NOT NULL,
`timezone` tinyint(4) default NULL,
`country` char(3) character set ascii collate ascii_bin NOT NULL,
`app_version` int(11) NOT NULL default '0',
`updated_at` int(11) NOT NULL,
`pushed_at` int(11) default NULL,
PRIMARY KEY (`app_id`,`dev_id`),
KEY `timezone` (`app_id`,`timezone`,`dev_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.05 sec)

mysql> show create table devs\G
*************************** 1. row ***************************
Table: devs
Create Table: CREATE TABLE `devs` (
`id` int(10) unsigned NOT NULL auto_increment,
`udid` varchar(40) collate utf8_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `udid_UNIQUE` (`udid`)
) ENGINE=InnoDB AUTO_INCREMENT=33141339 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.05 sec)

mysql> show create table app_devs\G
*************************** 1. row ***************************
Table: app_devs
Create Table: CREATE TABLE `app_devs` (
`app_id` smallint(5) unsigned NOT NULL,
`dev_id` int(10) unsigned NOT NULL,
`type` enum('none','pay','cheat') collate utf8_bin NOT NULL default 'none',
`app_version` int(11) NOT NULL default '0',
`versions` tinyint(3) unsigned NOT NULL default '1',
`timezone` tinyint(4) default NULL,
`ip` int(10) unsigned NOT NULL default '0',
`lang` char(3) character set ascii collate ascii_bin NOT NULL,
`country` char(3) character set ascii collate ascii_bin NOT NULL,
`os_id` smallint(5) unsigned default NULL,
`dev_type_id` smallint(5) unsigned default NULL,
`rare_currency` smallint(5) unsigned NOT NULL default '0',
`common_currency` mediumint(8) unsigned NOT NULL default '0',
`created_at` int(11) NOT NULL,
`updated_at` int(11) NOT NULL,
PRIMARY KEY (`app_id`,`dev_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.05 sec)

mysql> show create table app_version_devs\G
*************************** 1. row ***************************
Table: app_version_devs
Create Table: CREATE TABLE `app_version_devs` (
`app_id` smallint(5) unsigned NOT NULL,
`app_version` int(10) unsigned NOT NULL,
`dev_id` int(10) unsigned NOT NULL,
`launch_count` smallint(5) unsigned NOT NULL default '0',
`crash_count` smallint(5) unsigned NOT NULL default '0',
`playtime` smallint(5) unsigned NOT NULL default '0',
`created_at` int(11) NOT NULL,
PRIMARY KEY (`app_id`,`app_version`,`dev_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.05 sec)

mysql> show create table msg_devs\G
*************************** 1. row ***************************
Table: msg_devs
Create Table: CREATE TABLE `msg_devs` (
`msg_id` smallint(5) unsigned NOT NULL,
`dev_id` int(10) unsigned NOT NULL,
`views` smallint(5) unsigned NOT NULL default '1',
`clicks` tinyint(3) unsigned NOT NULL default '0',
`created_at` int(11) NOT NULL,
`updated_at` int(11) NOT NULL,
PRIMARY KEY (`msg_id`,`dev_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.06 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
Database hungs
2107
January 17, 2013 03:52PM
1063
January 18, 2013 04:23PM


Sorry, you can't reply to this topic. It has been closed.

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.