Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.2.6
-
Win 7
-
10.2.10
Description
Dear Team,
when I fill up one of my tables with more then 18 GB of data, I get errors like this and the server is complete dead. I cannot connect or perform any queries on it anymore.
And this is the output which I get in the *.err file.
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
|
InnoDB: Pending reads 0, writes 0
|
InnoDB: ###### Diagnostic info printed to the standard error stream
|
2017-06-25 8:22:29 3068 [Warning] InnoDB: A long semaphore wait:
|
--Thread 7552 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Warning] InnoDB: A long semaphore wait:
|
--Thread 6820 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Warning] InnoDB: A long semaphore wait:
|
--Thread 6220 has waited at ibuf0ibuf.cc line 4588 for 303.00 seconds the semaphore:
|
S-lock on RW-latch at 000000000A72B798 created in file buf0buf.cc line 1443
|
a writer (thread id 7788) has reserved it in mode exclusive
|
number of readers 0, waiters flag 1, lock_word: 0
|
Last time read locked in file ibuf0ibuf.cc line 4588
|
Last time write locked in file mtr0mtr.ic line 147
|
2017-06-25 8:22:29 3068 [Warning] InnoDB: A long semaphore wait:
|
--Thread 8668 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Warning] InnoDB: A long semaphore wait:
|
--Thread 4260 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Warning] InnoDB: A long semaphore wait:
|
--Thread 9120 has waited at buf0buf.cc line 4084 for 301.00 seconds the semaphore:
|
S-lock on RW-latch at 00000000801DAC38 created in file buf0buf.cc line 1443
|
a writer (thread id 0) has reserved it in mode exclusive
|
number of readers 0, waiters flag 1, lock_word: 0
|
Last time read locked in file not yet reserved line 0
|
Last time write locked in file buf0buf.cc line 5286
|
2017-06-25 8:22:29 3068 [Warning] InnoDB: A long semaphore wait:
|
--Thread 10052 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Warning] InnoDB: A long semaphore wait:
|
--Thread 7788 has waited at buf0buf.cc line 4084 for 303.00 seconds the semaphore:
|
S-lock on RW-latch at 0000000288C0CD58 created in file buf0buf.cc line 1443
|
a writer (thread id 0) has reserved it in mode exclusive
|
number of readers 0, waiters flag 1, lock_word: 0
|
Last time read locked in file not yet reserved line 0
|
Last time write locked in file buf0buf.cc line 5286
|
2017-06-25 8:22:29 3068 [Warning] InnoDB: A long semaphore wait:
|
--Thread 9184 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Warning] InnoDB: A long semaphore wait:
|
--Thread 4220 has waited at ibuf0ibuf.cc line 2542 for 302.00 seconds the semaphore:
|
S-lock on RW-latch at 000000000A72B798 created in file buf0buf.cc line 1443
|
a writer (thread id 7788) has reserved it in mode exclusive
|
number of readers 0, waiters flag 1, lock_word: 0
|
Last time read locked in file ibuf0ibuf.cc line 4588
|
Last time write locked in file mtr0mtr.ic line 147
|
2017-06-25 8:22:29 3068 [Warning] InnoDB: A long semaphore wait:
|
--Thread 7996 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Warning] InnoDB: A long semaphore wait:
|
--Thread 10080 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Warning] InnoDB: A long semaphore wait:
|
--Thread 4380 has waited at ibuf0ibuf.cc line 4588 for 303.00 seconds the semaphore:
|
S-lock on RW-latch at 000000000A72B798 created in file buf0buf.cc line 1443
|
a writer (thread id 7788) has reserved it in mode exclusive
|
number of readers 0, waiters flag 1, lock_word: 0
|
Last time read locked in file ibuf0ibuf.cc line 4588
|
Last time write locked in file mtr0mtr.ic line 147
|
2017-06-25 8:22:29 3068 [Warning] InnoDB: A long semaphore wait:
|
--Thread 4920 has waited at ibuf0ibuf.cc line 4404 for 303.00 seconds the semaphore:
|
Mutex at 00000001405B3F40, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2
|
 |
2017-06-25 8:22:29 3068 [Warning] InnoDB: A long semaphore wait:
|
--Thread 4984 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Note] InnoDB: A semaphore wait:
|
--Thread 7552 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Note] InnoDB: A semaphore wait:
|
--Thread 6820 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Note] InnoDB: A semaphore wait:
|
--Thread 6220 has waited at ibuf0ibuf.cc line 4588 for 303.00 seconds the semaphore:
|
S-lock on RW-latch at 000000000A72B798 created in file buf0buf.cc line 1443
|
a writer (thread id 7788) has reserved it in mode exclusive
|
number of readers 0, waiters flag 1, lock_word: 0
|
Last time read locked in file ibuf0ibuf.cc line 4588
|
Last time write locked in file mtr0mtr.ic line 147
|
2017-06-25 8:22:29 3068 [Note] InnoDB: A semaphore wait:
|
--Thread 8668 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Note] InnoDB: A semaphore wait:
|
--Thread 4260 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Note] InnoDB: A semaphore wait:
|
--Thread 9120 has waited at buf0buf.cc line 4084 for 301.00 seconds the semaphore:
|
S-lock on RW-latch at 00000000801DAC38 created in file buf0buf.cc line 1443
|
a writer (thread id 0) has reserved it in mode exclusive
|
number of readers 0, waiters flag 1, lock_word: 0
|
Last time read locked in file not yet reserved line 0
|
Last time write locked in file buf0buf.cc line 5286
|
2017-06-25 8:22:29 3068 [Note] InnoDB: A semaphore wait:
|
--Thread 10052 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Note] InnoDB: A semaphore wait:
|
--Thread 7788 has waited at buf0buf.cc line 4084 for 303.00 seconds the semaphore:
|
S-lock on RW-latch at 0000000288C0CD58 created in file buf0buf.cc line 1443
|
a writer (thread id 0) has reserved it in mode exclusive
|
number of readers 0, waiters flag 1, lock_word: 0
|
Last time read locked in file not yet reserved line 0
|
Last time write locked in file buf0buf.cc line 5286
|
2017-06-25 8:22:29 3068 [Note] InnoDB: A semaphore wait:
|
--Thread 9184 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Note] InnoDB: A semaphore wait:
|
--Thread 4220 has waited at ibuf0ibuf.cc line 2542 for 302.00 seconds the semaphore:
|
S-lock on RW-latch at 000000000A72B798 created in file buf0buf.cc line 1443
|
a writer (thread id 7788) has reserved it in mode exclusive
|
number of readers 0, waiters flag 1, lock_word: 0
|
Last time read locked in file ibuf0ibuf.cc line 4588
|
Last time write locked in file mtr0mtr.ic line 147
|
2017-06-25 8:22:29 3068 [Note] InnoDB: A semaphore wait:
|
--Thread 7996 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Note] InnoDB: A semaphore wait:
|
--Thread 10080 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Note] InnoDB: A semaphore wait:
|
--Thread 4380 has waited at ibuf0ibuf.cc line 4588 for 303.00 seconds the semaphore:
|
S-lock on RW-latch at 000000000A72B798 created in file buf0buf.cc line 1443
|
a writer (thread id 7788) has reserved it in mode exclusive
|
number of readers 0, waiters flag 1, lock_word: 0
|
Last time read locked in file ibuf0ibuf.cc line 4588
|
Last time write locked in file mtr0mtr.ic line 147
|
2017-06-25 8:22:29 3068 [Note] InnoDB: A semaphore wait:
|
--Thread 4920 has waited at ibuf0ibuf.cc line 4404 for 303.00 seconds the semaphore:
|
Mutex at 00000001405B3F40, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2
|
 |
2017-06-25 8:22:29 3068 [Note] InnoDB: A semaphore wait:
|
--Thread 4984 has waited at btr0cur.cc line 978 for 301.00 seconds the semaphore:
|
SX-lock on RW-latch at 00000003FFAEBAB0 created in file dict0dict.cc line 2610
|
a writer (thread id 9120) has reserved it in mode SX
|
number of readers 0, waiters flag 1, lock_word: 10000000
|
Last time read locked in file btr0cur.cc line 1010
|
Last time write locked in file btr0cur.cc line 978
|
2017-06-25 8:22:29 3068 [Note] InnoDB: A semaphore wait:
|
--Thread 8904 has waited at ibuf0ibuf.cc line 4931 for 50.00 seconds the semaphore:
|
Mutex at 00000001405B3F40, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2
|
 |
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
|
InnoDB: Pending reads 0, writes 0
|
InnoDB: ###### Diagnostic info printed to the standard error stream
|
Do you have any idea for that what it could be?
.ini file:port=3306
|
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
|
default_storage_engine=innodb
|
#below should not be changed just like that....: https://www.psce.com/blog/2012/04/04/how-to-resize-innodb-logs/
|
innodb_log_file_size=2G
|
innodb_adaptive_hash_index=0
|
innodb_flush_log_at_trx_commit = 2
|
innodb_thread_concurrency =0
|
innodb_force_recovery=0
|
innodb_lock_wait_timeout = 86400 # this is when you delete entries that the db can be looked for this long for one query for it to finish...
|
innodb_buffer_pool_size=15G
|
max_connections = 1100
|
startup mode
2017-06-25 7:45:17 8920 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
|
2017-06-25 7:45:17 8920 [Note] InnoDB: Uses event mutexes
|
2017-06-25 7:45:17 8920 [Note] InnoDB: Compressed tables use zlib 1.2.3
|
2017-06-25 7:45:17 8920 [Note] InnoDB: Number of pools: 1
|
2017-06-25 7:45:17 8920 [Note] InnoDB: Using generic crc32 instructions
|
2017-06-25 7:45:17 8920 [Note] InnoDB: Initializing buffer pool, total size = 15G, instances = 8, chunk size = 128M
|
2017-06-25 7:45:18 8920 [Note] InnoDB: Completed initialization of buffer pool
|
2017-06-25 7:45:18 8920 [Note] InnoDB: Highest supported file format is Barracuda.
|
2017-06-25 7:45:18 8920 [Note] InnoDB: 13 transaction(s) which must be rolled back or cleaned up in total 363297 row operations to undo
|
2017-06-25 7:45:18 8920 [Note] InnoDB: Trx id counter is 84910592
|
2017-06-25 7:45:18 8920 [Note] InnoDB: 128 out of 128 rollback segments are active.
|
2017-06-25 7:45:18 9752 [Note] InnoDB: Starting in background the rollback of recovered transactions
|
2017-06-25 7:45:18 9752 [Note] InnoDB: Rolling back trx with id 84908546, 648 rows to undo
|
2017-06-25 7:45:18 8920 [Note] InnoDB: Creating shared tablespace for temporary tables
|
2017-06-25 7:45:18 8920 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
|
2017-06-25 7:45:18 9752 [Note] InnoDB: Rollback of trx with id 84908546 completed
|
2017-06-25 7:45:18 9752 [Note] InnoDB: Rolling back trx with id 84908544, 12094 rows to undo
|
 |
InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 1002017-06-25 7:45:18 9752 [Note] InnoDB: Rollback of trx with id 84908544 completed
|
2017-06-25 7:45:18 9752 [Note] InnoDB: Rolling back trx with id 84908542, 19705 rows to undo
|
 |
InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 302017-06-25 7:45:18 8920 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
|
31 32 33 34 35 36 37 382017-06-25 7:45:18 8920 [Note] InnoDB: 5.7.14 started; log sequence number 2029751316249
|
392017-06-25 7:45:18 8072 [Note] InnoDB: Loading buffer pool(s) from C:\Program Files\MariaDB 10.2\data\ib_buffer_pool
|
40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 1002017-06-25 7:45:18 9752 [Note] InnoDB: Rollback of trx with id 84908542 completed
|
2017-06-25 7:45:18 9752 [Note] InnoDB: Rolling back trx with id 84908540, 16060 rows to undo
|
 |
InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 182017-06-25 7:45:18 8920 [Note] Plugin 'FEEDBACK' is disabled.
|
19 20 21 22 23 242017-06-25 7:45:18 8920 [Note] Server socket created on IP: '::'.
|
25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 642017-06-25 7:45:18 8920 [Note] Reading of all Master_info entries succeded
|
65 662017-06-25 7:45:18 8920 [Note] Added new Master_info '' to hash table
|
67 682017-06-25 7:45:18 8920 [Note] C:\Program Files\MariaDB 10.2\bin\mysqld.exe: ready for connections.
|
Version: '10.2.6-MariaDB' socket: '' port: 3306 mariadb.org binary distribution
|