-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathic_slappin.txt
More file actions
143 lines (100 loc) · 6.06 KB
/
ic_slappin.txt
File metadata and controls
143 lines (100 loc) · 6.06 KB
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
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
mysqlslap --user=root --password=oracle --number-of-queries=20000 --iterations=10 --concurrency=4000 --delimiter="\n" --create-schema=nexus --query="ins_slap.sql" --verbose
mysqlslap --user=root --password=oracle --number-of-queries=20000 --iterations=10 --concurrency=5000 --delimiter="\n" --create-schema=nexus --query="ins_slap.sql" --verbose
mysqlslap --user=root --password=oracle --number-of-queries=20000 --iterations=10 --concurrency=6000 --delimiter="\n" --create-schema=nexus --query="ins_slap.sql" --verbose
create database nexus;
create table nexus.replicant (
id INT(11) NOT NULL AUTO_INCREMENT ,
`First name` varchar(40) not null default '',
`Last name` varchar(40) not null default '',
`Replicant` enum('Yes','No') not null default 'Yes',
PRIMARY KEY (id)
) engine=InnoDB row_format=COMPACT;
INSERT INTO nexus.`replicant` (`First name`,`Last name`,`Replicant`)
VALUES
('Roy','Hauer','Yes'),
('Rutger','Batty','Yes'),
('Voight','Kampff','Yes'),
('Pris','Hannah','Yes'),
('Daryl','Stratton','Yes'),
('Rachael','Young','Yes'),
('Sean','Tyrell','Yes'),
('Rick','Ford','No'),
('Harrison','Deckard','Yes');
set global max_connections=100000;
mysqlslap --user=root --password=oracle --number-of-queries=2000 --iterations=10 --concurrency=400 --delimiter="\n" --create-schema=nexus --query="ins_slap.sql" --verbose
Benchmark
Average number of seconds to run all queries: 1.262 seconds
Minimum number of seconds to run all queries: 1.049 seconds
Maximum number of seconds to run all queries: 1.643 seconds
Number of clients running queries: 400
Average number of queries per client: 5
mysqlslap --user=root --password=oracle --number-of-queries=2000 --iterations=10 --concurrency=900 --delimiter="\n" --create-schema=nexus --query="ins_slap.sql" --verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 2.126 seconds
Minimum number of seconds to run all queries: 1.566 seconds
Maximum number of seconds to run all queries: 3.776 seconds
Number of clients running queries: 900
Average number of queries per client: 2
mysqlslap --user=root --password=oracle --number-of-queries=2000 --iterations=100 --concurrency=900 --delimiter="\n" --create-schema=nexus --query="ins_slap.sql" --verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 2.558 seconds
Minimum number of seconds to run all queries: 1.362 seconds
Maximum number of seconds to run all queries: 7.648 seconds
Number of clients running queries: 900
Average number of queries per client: 2
mysqlslap --user=root --password=oracle --auto-generate-sql-write-number=10000 --number-of-queries=2000 --iterations=100 --concurrency=900 --delimiter="\n" --create-schema=nexus --query="ins_slap.sql" --verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
mysqlslap: Cannot run query INSERT INTO `replicant` (`First name`,`Last name`,`Replicant`)
VALUES
('H','M','Yes'), ('G','K','Yes');
ERROR : Lost connection to MySQL server during query
mysqlslap: Cannot run query INSERT INTO `replicant` (`First name`,`Last name`,`Replicant`)
VALUES
('H','M','Yes'), ('G','K','Yes');
ERROR : Lost connection to MySQL server during query
mysqlslap: Cannot run query INSERT INTO `replicant` (`First name`,`Last name`,`Replicant`)
VALUES
('H','M','Yes'), ('G','K','Yes');
ERROR : Lost connection to MySQL server during query
mysqlslap: Cannot run query INSERT INTO `replicant` (`First name`,`Last name`,`Replicant`)
VALUES
('H','M','Yes'), ('G','K','Yes');
ERROR : Lost connection to MySQL server during query
There are also two configuration options related to this at their disposal today (I hope that they're using 5.7.19 or 5.7.20):
1. https://dev.mysql.com/doc/refman/5.7/en/group-replication-options.html#sysvar_group_replication_transaction_size_limit
group_replication_transaction_size_limit = 10485760
2. https://dev.mysql.com/doc/refman/5.7/en/group-replication-options.html#sysvar_group_replication_compression_threshold
group_replication_compression_threshold = 10240
group_replication_flow_control_certifier_threshold = 25000000
group_replication_flow_control_applier_threshold = 25000000
mysqlslap --user=root --password=oracle --auto-generate-sql-write-number=10000 --number-of-queries=2000 --iterations=100 --concurrency=900 --delimiter="\n" --create-schema=nexus --query="ins_slap.sql" --verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
^[[ABenchmark
Average number of seconds to run all queries: 2.512 seconds
Minimum number of seconds to run all queries: 1.420 seconds
Maximum number of seconds to run all queries: 8.710 seconds
Number of clients running queries: 900
Average number of queries per client: 2
mysqlslap --user=root --password=oracle --auto-generate-sql-write-number=50000 --number-of-queries=2000 --iterations=1000 --concurrency=1000 --delimiter="\n" --create-schema=nexus --query="ins_slap.sql" --verbose
mysqlslap: Cannot run query INSERT INTO `replicant` (`First name`,`Last name`,`Replicant`)
VALUES
('H','M','Yes'), ('G','K','Yes');
ERROR : Plugin instructed the server to rollback the current transaction.
vi /etc/my.cnf
plugin-load-add=thread_pool=thread_pool.so
plugin-load-add=tp_thread_state=thread_pool.so
plugin-load-add=tp_thread_group_state=thread_pool.so
plugin-load-add=tp_thread_group_stats=thread_pool.so
# thread_pool max trans size 6secs.
thread_pool_stall_limit=600
thread_pool_size=36
mysqlslap --user=root --password=oracle --auto-generate-sql-write-number=50000 --number-of-queries=2000 --iterations=1000 --concurrency=1000 --delimiter="\n" --create-schema=nexus --query="ins_slap.sql" --verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 4.379 seconds
Minimum number of seconds to run all queries: 1.173 seconds
Maximum number of seconds to run all queries: 12.567 seconds
Number of clients running queries: 1000
Average number of queries per client: 2