Salut Jocelyn,


I have just replied to Stephane.

Is there a "best practice" setting guidline for autocommit? In my case - Sysbench R/W, single instance MariaDB, no clustering, DB in RAM.



Regards,



JC


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 (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
> --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
 
--
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


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.