[MDEV-13168] Maria DB crashes after about 18GB of table size Created: 2017-06-25  Updated: 2017-11-12  Resolved: 2017-11-12

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert, Storage Engine - InnoDB
Affects Version/s: 10.2.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Michael Assignee: Jan Lindström (Inactive)
Resolution: Incomplete Votes: 1
Labels: need_feedback
Environment:

Win 7

Sprint: 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



 Comments   
Comment by Elena Stepanova [ 2017-06-25 ]

Does it only happen with one table, or with any table as soon as it goes over 18G?
What is 18G the size of – the ibd file, or initial data you load, or ...?
At which point the server stops responding – when you are still loading the data, or after that when you do something with the table?
How exactly do you load the data, is it LOAD DATA, or a set of INSERTs, or...?
Is it done within one big transaction or many small transactions?
Could you please provide SHOW CREATE TABLE for the table in question?

Thanks.

Comment by Michael [ 2017-06-25 ]

It happens only with this one table. I have another tbl which is 500 GB.

The size of the ibd file.

Yes, it stops when I still upload the data. Though, if I do a innodb_force_recovery=4, and then a innodb_force_recovery=3 and then do a query on the table when I set: innodb_force_recovery=0, the server hangs again.
I do an Insert. with on duplicate key update ....
probably 200 transactions to get to the 18gb. 

CREATE TABLE `filter_criteriadata` (
 
	`date_time` BIGINT(20) UNSIGNED NOT NULL,
 
	`idSymbol` MEDIUMINT(8) UNSIGNED NOT NULL,
 
	`idFilter` TINYINT(3) UNSIGNED NOT NULL,
 
	`valuee` DOUBLE NULL DEFAULT NULL,
 
	PRIMARY KEY (`date_time`, `idSymbol`, `idFilter`),
 
	INDEX `date_time_idSymbol_idFilter_value` (`date_time`, `idSymbol`, `idFilter`, `valuee`)
 
)
 
COMMENT=
 
COLLATE='latin1_swedish_ci'
 
ENGINE=InnoDB
 
ROW_FORMAT=DYNAMIC
 
;

Now, I changed my config around quite a bit from what I have send before, and now, seems to run smoother. I have already inserted 55 GB of 255 GB.
here it comes:

port=3306
 
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
 
default_storage_engine=innodb
 
innodb_buffer_pool_size=15G
 
innodb_log_file_size=2G
 
innodb_adaptive_hash_index=0
 
innodb_flush_log_at_trx_commit = 2 #http://stackoverflow.com/questions/17508721/innodb-engine-simple-update-query-takes-way-too-long-query-end
 
#innodb_thread_concurrency =0#changed this below
 
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...
 
max_connections = 1100 
# this has been added after problems of adding new data for over 18GB
 
sync_binlog=0 #http://www.speedemy.com/mysql-troubleshooting-crash-course/
 
innodb_flush_method=O_DIRECT #http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof/
 
innodb_buffer_pool_instances=8
 
innodb_thread_concurrency =8
 
innodb_flush_sync = 0
 
innodb_io_capacity=60000 # was 200 in normal config none SSD values
 
innodb_io_capacity_max=85000 # was 2000 in normal config none SSD values
 
innodb_read_io_threads= 32
 
innodb_write_io_threads=16

Comment by Jan Lindström (Inactive) [ 2017-10-10 ]

Can you provide show provesslist, show engine innodb status, attach a debugger to mysqld and output from thread apply all bt , what you do when it starts to show those semaphore errors ?

Comment by Sergei Golubchik [ 2017-11-12 ]

There was no reply for a month, so this issue is closed.

If you can, any time in the future, provide additional information, please do so and we'll reopen this issue.

Generated at Thu Feb 08 08:03:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.