Re: [Maria-discuss] Deadlocks in R/W phase of Sysbench

Hi John,


It seems you have

autocommit = 0

in your my.cnf


   Jocelyn


Le 09/06/2017 à 17:46, J. Cassidy a écrit :
> Stephane,
>
> all of my directories, logs, spaces, tables etc. are in RAM. During 
> the course of the benchmark there in ZERO I/O
> which is my intention.
>
> Here is a snippet from the MariaDB KB -
>
>
>       autocommit
>
> By default, MariaDB runs with autocommit 
>  mode 
> enabled. This means that as soon as you execute a statement that 
> updates (modifies) a table, MariaDB stores the update on disk to make 
> it permanent. To disable autocommit mode, use the following statement:
>
> SET autocommit=0;
> So it would seem that I am running with autocommit enabled.
>
> Regards,
>
>
> JC
>
> Re,
> Yep get this but just that io a supposed to be fast but still have to 
> hit FS moved from PB to redo log to tblspace
> In a perfect fast world all layer should have equal ressources so no 
> reasons not giving as many threads to write io vs doing the work 
> itself of modifying pages.
> Disabling auto commit with sysbench is suspicious and intersting : 
> never question myself on this topic before ?
> /svar
>
> Stéphane Varoqui, Senior Consultant
>
> Phone: +33 695-926-401, skype: svaroqui
> http://www.mariadb.com
>
> Le 9 juin 2017 à 16:47, J. Cassidy sean@jdcassidy.eu> a écrit :
> Salut Stephane,
>
> I/O does not enter into the equation, the database is in memory (RAM).
>
>
> Regards,
>
>
> JC
>
> Hello,
> What IO scheduler are you using for FS and witch one ?
> deadline or loop is a must do. Also mounting with noatime can help for 
> write
> Would you try such settings i found to make a difference
> autocommit = 1
> innodb_adaptive_hash_index=0
> innodb_max_dirty_page_pct=20
> innodb_write_io_threads =64
> innodb_log_file_size = 1024M
> innodb_log_file_in_group = 4
> innodb_log_files_in_group = 4
> innodb_thread_concurrency = 0
> innodb_purge_threads = 8
> innodb_change_buffering=none
> innodb_open_files = 16384
> innodb_file_per_table=1
> innodb_autoinc_lock_mode = 2
> And comment
> # innodb_lru_scan_depth = 4096
>
> Stéphane Varoqui, Senior Consultant
>
> Phone: +33 695-926-401, skype: svaroqui
> http://www.mariadb.com 
> Le 9 juin 2017 à 16:22, J. Cassidy sean@jdcassidy.eu 
> > a écrit :
> Mark,
>
>
> still scratching head...
>
>
> Regards,
>
>
> JC
>
> Thanks. I was hoping there was an easy fix if you did something like 
> used serializable isolation, but you haven't done that.
> On Fri, Jun 9, 2017 at 12:27 AM, J. Cassidy sean@jdcassidy.eu 
> > wrote:
>
> All,
>
>
> as discussed yesterday, here is my setup and configuration.
>
>
> *************************************************************
> 16 VCPU 64GB Memory, DB size 12 GB (In RAM), MariaDB 10.1.24*
> Single Image. No clustering or outside network interaction *
> SuSE SLES 12 SP2 using a 4.4.49-92 kernel *
> *************************************************************
> ### Sysbench R/W - 64 Threads
>
> sysbench 0.5: multi-threaded system evaluation benchmark
>
> Running the test with following options:
> Number of threads: 64
> Report intermediate results every 2 second(s)
> Initializing random number generator from seed (42).
>
>
> Threads started!
>
> [ 2s] threads: 64, tps: 51141.62, reads: 717023.15, writes: 204757.48, 
> response time: 4.72ms (99%), errors: 54.00, reconnects: 0.00
> [ 4s] threads: 64, tps: 47938.19, reads: 671195.72, writes: 191759.28, 
> response time: 4.65ms (99%), errors: 5.00, reconnects: 0.00
> [ 6s] threads: 64, tps: 46908.41, reads: 656790.25, writes: 187640.64, 
> response time: 4.65ms (99%), errors: 2.50, reconnects: 0.00
> [ 8s] threads: 64, tps: 45940.12, reads: 643191.64, writes: 183781.47, 
> response time: 4.44ms (99%), errors: 3.00, reconnects: 0.00
> [ 10s] threads: 64, tps: 45789.91, reads: 641083.20, writes: 
> 183161.13, response time: 4.77ms (99%), errors: 2.00, reconnects: 0.00
> [ 12s] threads: 64, tps: 45408.37, reads: 635825.16, writes: 
> 181672.97, response time: 4.80ms (99%), errors: 1.50, reconnects: 0.00
> [ 14s] threads: 64, tps: 44319.93, reads: 620440.07, writes: 
> 177262.74, response time: 4.50ms (99%), errors: 0.50, reconnects: 0.00
> [ 16s] threads: 64, tps: 44634.78, reads: 624843.97, writes: 
> 178523.13, response time: 4.71ms (99%), errors: 0.50, reconnects: 0.00
> .
> .
> .
> [ 572s] threads: 64, tps: 24980.71, reads: 349707.39, writes: 
> 99924.32, response time: 5.92ms (99%), errors: 0.00, reconnects: 0.00
> [ 574s] threads: 64, tps: 25337.03, reads: 354796.98, writes: 
> 101355.13, response time: 6.19ms (99%), errors: 0.00, reconnects: 0.00
> [ 576s] threads: 64, tps: 25196.31, reads: 352683.33, writes: 
> 100777.24, response time: 6.49ms (99%), errors: 0.00, reconnects: 0.00
> [ 578s] threads: 64, tps: 25235.61, reads: 353317.05, writes: 
> 100941.94, response time: 5.94ms (99%), errors: 0.00, reconnects: 0.00
> [ 580s] threads: 64, tps: 25241.11, reads: 353395.59, writes: 
> 100956.96, response time: 6.09ms (99%), errors: 0.00, reconnects: 0.00
> [ 582s] threads: 64, tps: 25146.18, reads: 352056.04, writes: 
> 100599.73, response time: 5.96ms (99%), errors: 0.00, reconnects: 0.00
> [ 584s] threads: 64, tps: 25242.59, reads: 353362.72, writes: 
> 100978.85, response time: 6.21ms (99%), errors: 0.00, reconnects: 0.00
> [ 586s] threads: 64, tps: 25297.02, reads: 354187.80, writes: 
> 101166.58, response time: 5.89ms (99%), errors: 0.00, reconnects: 0.00
> [ 588s] threads: 64, tps: 25070.60, reads: 350944.86, writes: 
> 100283.39, response time: 6.33ms (99%), errors: 0.00, reconnects: 0.00
> [ 590s] threads: 64, tps: 25115.20, reads: 351666.84, writes: 
> 100466.81, response time: 6.21ms (99%), errors: 0.00, reconnects: 0.00
> [ 592s] threads: 64, tps: 25165.11, reads: 352314.52, writes: 
> 100668.94, response time: 5.98ms (99%), errors: 0.00, reconnects: 0.00
> [ 594s] threads: 64, tps: 25165.99, reads: 352322.89, writes: 
> 100703.97, response time: 6.30ms (99%), errors: 0.00, reconnects: 0.00
> [ 596s] threads: 64, tps: 24854.73, reads: 347945.66, writes: 
> 99370.90, response time: 6.17ms (99%), errors: 0.00, reconnects: 0.00
> [ 598s] threads: 64, tps: 24888.16, reads: 348463.80, writes: 
> 99556.16, response time: 6.21ms (99%), errors: 0.00, reconnects: 0.00
> [ 600s] threads: 64, tps: 25158.22, reads: 352195.58, writes: 
> 100633.38, response time: 5.94ms (99%), errors: 0.00, reconnects: 0.00
> OLTP test statistics:
> queries performed:
> read: 256244548
> write: 73212536
> other: 36606172
> total: 366063256
> transactions: 18302990 (30454.11 per sec.)
> read/write requests: 329457084 (548179.44 per sec.)
> other operations: 36606172 (60908.54 per sec.)
> ignored errors: 192 (0.32 per sec.)
> reconnects: 0 (0.00 per sec.)
>
> General statistics:
> total time: 601.0023s
> total number of events: 18302990
> total time taken by event execution: 38450.5507s
> response time:
> min: 0.42ms
> avg: 2.10ms
> max: 62.26ms
> approx. 99 percentile: 5.62ms
>
> Threads fairness:
> events (avg/stddev): 285984.2188/3788.79
> execution time (avg/stddev): 600.7899/0.00
>
>
> =================================================================================================================
>
> ### Excerpt from MariaDB Error Log (Thousands of similiar messages).
> .
> .
> .
> 2017-06-08 8:10:44 4395980287248 [ERROR] mysqld: Deadlock found when 
> trying to get lock; try restarting transaction
> 2017-06-08 8:10:44 4353081699600 [ERROR] mysqld: Deadlock found when 
> trying to get lock; try restarting transaction
> 2017-06-08 8:10:44 4353087458576 [ERROR] mysqld: Deadlock found when 
> trying to get lock; try restarting transaction
> 2017-06-08 8:10:44 4360227998992 [ERROR] mysqld: Deadlock found when 
> trying to get lock; try restarting transaction
> 2017-06-08 8:10:44 4353073515792 [ERROR] mysqld: Deadlock found when 
> trying to get lock; try restarting transaction
> 2017-06-08 8:10:44 4390705949968 [ERROR] mysqld: Deadlock found when 
> trying to get lock; try restarting transaction
> 2017-06-08 8:10:44 4360227392784 [ERROR] mysqld: Deadlock found when 
> trying to get lock; try restarting transaction
> 2017-06-08 8:10:44 4353087458576 [ERROR] mysqld: Deadlock found when 
> trying to get lock; try restarting transaction
> 2017-06-08 8:10:44 4353073515792 [ERROR] mysqld: Deadlock found when 
> trying to get lock; try restarting transaction
> 2017-06-08 8:10:44 4353087458576 [ERROR] mysqld: Deadlock found when 
> trying to get lock; try restarting transaction
> .
> .
> .
>
> ===================================================================================================================
> The configuration below has been tried and tested and works across on 
> different architectures e.g. S390X and S390X.
>
>
> [mysqld_safe]
> user=root
> password=''
> [mysqld]
> symbolic-links=0
> open_files_limit = 65535 # was 102400
> max_allowed_packet = 16M # was 768M
> max-connect-errors = 1000000
> connect_timeout = 120 # was 60
> performance-schema = false
> net_read_timeout = 720
> log-warnings=2
> #####files and sockets
> # innodb_log_group_home_dir= /data-lun22/logs
> pid-file=/tmp/mysqld.pid.sysbench
> port = 3306
> # log-error=/var/log/mariadb/mariadb-error-john.log
> log-error=/var/log/mariadb/mariadb-error-john.log
> general_log_file=/var/log/mariadb/mariadb-john.log
> long_query_time = 0
> slow_query_log = 0
> # slow_query_log_file = /var/log/mariadb/mariadb-slowquery.log
> # general-log
> # general_log_file=/var/log/mariadb/mariadb-general.log
> ##### MariaDB temporary tables
> # tmpdir in RAM
> tmpdir = /mnt/tmpdir
> #####non innodb options (fixed)
> max_connections = 4000
> back_log = 150
> table_open_cache = 8000 # was 2048 # was 4000 # was 8000
> key_buffer_size = 16M
> query_cache_type = 0 #was 1 # was 0
> join_buffer_size = 32K
> sort_buffer_size = 32K
> autocommit = 0
> thread-cache-size = 50
> table-definition-cache = 1024
> table-open-cache = 2048
> ####fixed innodb options
> innodb_file_per_table = true
> innodb_open_files = 4096 # was 8192 # was 4096
> innodb_data_file_path = ibdata1:250M:autoextend
> innodb_flush_log_at_trx_commit = 2
> innodb_flush_method = O_DIRECT_NO_FSYNC
> innodb_log_buffer_size = 256M
> innodb_log_file_size = 4G # was 14G # was 4G
> innodb_log_files_in_group = 2
> innodb_buffer_pool_size = 36G #was 8G # was 48G
> innodb_buffer_pool_instances = 8 # 16 # was 6 # was 32
> innodb_adaptive_hash_index_partitions = 32
> innodb_thread_concurrency = 0
> ####tuning for RAM disk
> innodb_adaptive_flushing = 1
> innodb_flush_neighbors = 0
> innodb_io_capacity = 8000 # was 20000
> innodb_io_capacity_max = 40000
> innodb_lru_scan_depth = 4096
> innodb_purge_threads = 2
> innodb_read_io_threads = 2
> innodb_write_io_threads = 2
>
>
> Note NMON reports 35% memory usage in the middle of the abovementioned 
> Banchmark ergo the DB is in cache. No paging.
>
> ++ Note The R/W TPS rate are "respectable" based on observations from 
> countless other runs ran on other configurations/architecture.
> The TPS rates would obviously improve if the deadlock situation 
> outlined above was resolved.
>
>
> So there you have it. Any tips / pointers that would enlighten me are 
> much appreciated.
>
> Regards,
>
>
> JC
>
>
> Privet Sergey,
>
> do not think I have a problem with my config(s).
>
> I am not on the machine at the moment.
> From memory, with 16 Cores / 32 VCPU, 64 GB memory, buffer pools, logs 
> etc. tailored to this configuration, the TPS rate is
>
> 64 threads - R/O 73K R/W 26K
> 128 threads - R/O 71K R/W 23K.
>
> I am putting the DB in RAM of course to obviate I/O issues. I am 
> interested in seeing what is happening with the CPU caches and nest..
>
> NMON tells me that for the R/O runs I have approximately 85% User, 
> 12-14% System and the rest Idle - this stays consistent throughout the 
> run (300 seconds R/O, 600 seconds R/W) which is reasonable.
>
> On the R/W runs, NMONs CPU usage display shows that some CPUs are 
> barely firing, or only intermittently and the granularity I had with 
> R/O is gone. This would seem to tie in with what I see in the MariaDB 
> log - headwinds, violent storms and deadlocks.
>
> Any further information you require, please let me know.
>
>
>
> Regards,
>
>
> JC
>
> Hi!
>
> I just tried this on x86: 40 threads against single table having 10k rows.
> No deadlocks, "tps: 12372.40, reads: 173219.52, writes: 49490.51".
>
> This is with latest snapshot of 10.1 and sysbench 1.0.
>
> I believe you shouldn't see deadlocks on such a big data set. Probably
>
> something is wrong with your configs?
>
> Regards,
> Sergey
>
> On Thu, Jun 08, 2017 at 04:51:31PM +0200, J. Cassidy wrote:
> >
> >
> > Hello Mark,
> >
> > appreciate the reply.
> >
> > The OLTP table size and
> > OLTP size give me a DB size of appx 12GB. This is what I want. I 
> have in the
> > meantime, looked at some older logs and see that whatever amount of 
> threads I
> > specify (8, 16,32, 64 or 128), the deadlock messages are still 
> surfacing in the
> > R/W phase
> > of Sysbench. I even dropped in two different MariaDB release levels
> > (10.0.19, 10.1.24) to see whether it would make a difference, 
> deadlock still
> > there. I am using Sysbench 0.5 for these tests.
> > I am currently using MariaDB
> > 10.1.24 - built directly on the machine. S390x by the way, but the 
> problem is
> > also
> > occuring on an X86-64 box.
> >
> > Regards,
> >
> >
> > JC
> >
> >
> >
> > I run sysbench frequently with MySQL
> > (not MariaDB yet) and don't recall ever seeing this error. But I 
> use much
> > larger values for --oltp-table-size. You used 85937 and I use 1M or 
> larger.
> > Maybe
> > there is too much data contention with smaller tables, even if a 
> large number of
> > tables is used (128 in your case). I use a smaller number of tables 
> - between 4
> > and 20. On Thu, Jun 8, 2017 at 12:48 AM, J. Cassidy 
> sean@jdcassidy.eu > wrote:
> > Hello all,
> >
> >
> > have used Sysbench extensively to test MariaDB CPU load factors 
> between various
> > architectures.
> >
> > I am noticing massive amounts of "[ERROR] mysqld:
> > Deadlock found when trying to get lock; try restarting transaction" 
> in the
> > MariaDB error log during the R/W phase. The Sysbench R/W TPS rates 
> are still
> > respectable for my purposes, but how do I
> > correct this condition?? Can I
> > ignore this / must live with it?
> > I have tried three different MariaDB
> > releases (10.0.19, 10.1.12, 10.1.24) to narrow things down, but this 
> message (x
> > 1000's) is happening across all releases in the Sysbench R/W phase.
> >
> > Here is the Sysbench command that drives the workload against MariaDB -
> >
> > sysbench --test=lua/oltp.lua --oltp_tables_count=128 
> --oltp-table-size=85937
> > --rand-seed=42 --rand-type=uniform --num-threads=128 
> --oltp-read-only=off
> > --report-interval=2 --mysql-socket=/var/lib/mysql/mysql.sock 
> --max-time=201
> > --max-requests=0 --mysql-user=root --percentile=99 run
> >
> > Would anyone
> > have a tip / idea /pointer?
> >
> >
> > Regards,
> >
> >
> > JC
> >
> >
> > John Cassidy
> >
> > Obere Bühlstrasse 21
> > 8700 Küsnacht
> > (ZH)
> > Switzerland / Suisse / Schweiz
> >
> >
> > Mobile: +49 152 58961601 <../images/blank.png> (Germany)
> > Mobile: +352 621 577 149 (Luxembourg)
> > Mobile: +41 78 769 17 97 <../images/blank.png> (CH)
> > Landline: +41 44 509 1957
> >
> > http://www.jdcassidy.eu 
> >
> > "Aut viam inveniam aut
> > faciam" - Hannibal.
> > _______________________________________________
> > Mailing list: https://launchpad.net/~maria-discuss 
> 
> > Post to :
> > maria-discuss@lists.launchpad.net 
> 
> > Unsubscribe : https://launchpad.net/~maria-discuss 
> 
> > More help : https://help.launchpad.net/ListHelp
> > --Mark Callaghan
> > mdcallag@gmail.com 
> >
> > John Cassidy
> >
> > Obere Bühlstrasse 21
> > 8700
> > Küsnacht (ZH)
> > Switzerland / Suisse / Schweiz
> >
> >
> > Mobile:
> > +49 152 58961601 (Germany)
> > Mobile: +352 621 577 149 <../images/blank.png> (Luxembourg)
> > Mobile:
> > +41 78 769 17 97 <../images/blank.png> (CH)
> > Landline: +41 44 509 1957 <../images/blank.png>
> >
> > http://www.jdcassidy.eu 
> >
> > "Aut viam inveniam aut faciam" -
> > Hannibal.
>
> > _______________________________________________
> > Mailing list: https://launchpad.net/~maria-discuss 
> 
> > Post to : maria-discuss@lists.launchpad.net 
> 
> > Unsubscribe : https://launchpad.net/~maria-discuss 
> 
> > More help : https://help.launchpad.net/ListHelp
>
>
>
>
> John Cassidy
>
> Obere Bühlstrasse 21
> 8700 Küsnacht (ZH)
> Switzerland / Suisse / Schweiz
>
>
> Mobile: +49 152 58961601 <../images/blank.png> (Germany)
> Mobile: +352 621 577 149 <../images/blank.png> (Luxembourg)
> Mobile: +41 78 769 17 97 <../images/blank.png> (CH)
> Landline: +41 44 509 1957 <../images/blank.png>
>
> http://www.jdcassidy.eu 
>
> "Aut viam inveniam aut faciam" - Hannibal.
>
> John Cassidy
>
> Obere Bühlstrasse 21
> 8700 Küsnacht (ZH)
> Switzerland / Suisse / Schweiz
>
>
> Mobile: +49 152 58961601 <../images/blank.png> (Germany)
> Mobile: +352 621 577 149 <../images/blank.png> (Luxembourg)
> Mobile: +41 78 769 17 97 <../images/blank.png> (CH)
> Landline: +41 44 509 1957 <../images/blank.png>
>
> http://www.jdcassidy.eu 
>
> "Aut viam inveniam aut faciam" - Hannibal.
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss 
> 
> Post to : maria-discuss@lists.launchpad.net 
> 
> Unsubscribe : https://launchpad.net/~maria-discuss 
> 
> More help : https://help.launchpad.net/ListHelp
> -- 
> Mark Callaghan
> mdcallag@gmail.com 
>
>
> John Cassidy
>
> Obere Bühlstrasse 21
> 8700 Küsnacht (ZH)
> Switzerland / Suisse / Schweiz
>
>
> Mobile: +49 152 58961601 (Germany)
> Mobile: +352 621 577 149 (Luxembourg)
> Mobile: +41 78 769 17 97 (CH)
> Landline: +41 44 509 1957
>
> http://www.jdcassidy.eu 
>
> "Aut viam inveniam aut faciam" - 
> Hannibal._______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss 
> 
> Post to : maria-discuss@lists.launchpad.net 
> 
> Unsubscribe : https://launchpad.net/~maria-discuss 
> 
> More help : https://help.launchpad.net/ListHelp
>
>
> John Cassidy
>
> Obere Bühlstrasse 21
> 8700 Küsnacht (ZH)
> Switzerland / Suisse / Schweiz
>
>
> Mobile: +49 152 58961601 (Germany)
> Mobile: +352 621 577 149 (Luxembourg)
> Mobile: +41 78 769 17 97 (CH)
> Landline: +41 44 509 1957
>
> http://www.jdcassidy.eu
>
> "Aut viam inveniam aut faciam" - Hannibal.
>
>
> John Cassidy
>
> Obere Bühlstrasse 21
> 8700 Küsnacht (ZH)
> Switzerland / Suisse / Schweiz
>
>
> Mobile: +49 152 58961601 (Germany)
> Mobile: +352 621 577 149 (Luxembourg)
> Mobile: +41 78 769 17 97 (CH)
> Landline: +41 44 509 1957
>
> http://www.jdcassidy.eu
>
> "Aut viam inveniam aut faciam" - Hannibal.
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp