forked from therealkevinc/pgio
-
Notifications
You must be signed in to change notification settings - Fork 0
/
README
300 lines (239 loc) · 12.8 KB
/
README
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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
# Copyright 1999 Kevin Closson
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
# http://www.apache.org/licenses/LICENSE-2.0
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
MENU
INTRODUCTION
INTRODUCING THE pgio.conf FILE
LOADING DATA WITH THE setup.sh SCRIPT
VALIDATING THE DATA LOADED BY THE setup.sh SCRIPT
TESTING I/O WITH THE runit.sh SCRIPT
REDUCING THE AMOUNT OF SYSTEM MEMORY AVAILABLE TO THE OS PAGE CACHE (FILE SYSTEM BUFFER CACHE)
TESTING I/O AFTER REDUCING THE SIZE OF THE OS PAGE CACHE
VALIDATING PHYSICAL I/O WITH THE iostat.out FILE
LOADING ONE TERABYTE TEST DATA WITH THE setup.sh SCRIPT
TESTING WITH THE ONE TERABYTE ACTIVE DATA SET WITH THE runit.sh SCRIPT
INTRODUCTION
------------
This is the pgio software package. If you don't know SLOB very well it might not
be all that simple to understand. Please visit www.kevinclosson.net for more
information or search the web for "pgio" as there is quite a lot of information
available, online, from pgio beta release users.
INTRODUCING THE pgio.conf FILE
------------------------------
This is a very brief description of what the pgio.conf parameters effect:
UPDATE_PCT - The percentage of SQL that will be UPDATE DML
RUN_TIME - runit.sh run duration in seconds
NUM_SCHEMAS - pgio data is loaded into either a big single schema or multiple.
NUM_SCHEMAS directs setup.sh to create and load NUM_SCHEMAS
schemas.
NUM_THREADS - For setup.sh:
* This parameter controls the number of concurrent
data loading streams.
- For runit.sh:
* This parameter controls how many sessions will attach to
each NUM_SCHEMAS schema.
For example, if NUM_SCHEMAS is set to 32 and NUM_THREADS is set
to 8, setup.sh will load data into 32 schemas in batches of
8 concurrent data laoding streams. On the other hand, if set to the
same 32 and 8 respectively for NUM_SCHEMAS and NUM_THREADS then
runit.sh will run 8 sessions accessing each of the 32 schemas for a
total of 256 sessions.
WORK_UNIT - WORK_UNIT controls the bounds of the BETWEEN clause for each
SELECT statement as it executes. For example, if set to 255
each SELECT will visit 255 random blocks. Smaller values require
more SQL executions to drive the same IOPS.
UPDATE_WORK_UNIT - UPDATE_WORK_UNIT is the UPDATE DML corollary for WORK_UNIT. This
allows for a mixed SELECT/UPDATE workload where SELECT statements
can visit more blocks than UPDATES.
SCALE - The amount of data to load into each schema. Values can be N as
a number of 8KB blocks or N modified with [MG] for megabytes or
gigabytes. For example, if set to 1024 setup.sh will load 8MB
( 1024 * 8192 bytes) into each schema whereas set to 1024M will load
1024 megabytes into each schema.
DBNAME - The PostgreSQL database that holds the pgio objects
CONNECT_STRING - This parameter is passed to the psql command. The pgio kit expects that
.pgpass and all other authentication is configured. For example,
if CONNECT_STRING is set to "pg10" then the following command must
succeed in your pgio environment:
$ psql pg10
CREATE_BASE_TABLE - The loader, setup.sh, creates a dense "seed" table as the source from
which to load the test tables. This seed table persists after
setup.sh exits. If this parameter is set to true the seed table will not
be regenerated.
Loading Data With the setup.sh SCRIPT
-------------------------------------
$ cat pgio.conf
UPDATE_PCT=0
RUN_TIME=120
NUM_SCHEMAS=16
NUM_THREADS=8
WORK_UNIT=255
UPDATE_WORK_UNIT=8
SCALE=8G
DBNAME=pg10
CONNECT_STRING="pg10"
CREATE_BASE_TABLE=TRUE
$
$ sh ./setup.sh
Job info: Loading 8G scale into 16 schemas as per pgio.conf->NUM_SCHEMAS.
Batching info: Loading 8 schemas per batch as per pgio.conf->NUM_THREADS.
Base table loading time: 9 seconds.
Waiting for batch. Global schema count: 8. Elapsed: 0 seconds.
Waiting for batch. Global schema count: 15. Elapsed: 59 seconds.
Waiting for batch. Global schema count: 16. Elapsed: 111 seconds.
Group data loading phase complete. Elapsed: 136 seconds.
$
VALIDATING THE DATA LOADED BY THE setup.sh SCRIPT
-------------------------------------------------
$ echo '\d+' | psql pg10
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------------------+-------+----------+------------+-------------
public | pgio1 | table | postgres | 8192 MB |
public | pgio10 | table | postgres | 8192 MB |
public | pgio11 | table | postgres | 8192 MB |
public | pgio12 | table | postgres | 8192 MB |
public | pgio13 | table | postgres | 8192 MB |
public | pgio14 | table | postgres | 8192 MB |
public | pgio15 | table | postgres | 8192 MB |
public | pgio16 | table | postgres | 8192 MB |
public | pgio2 | table | postgres | 8192 MB |
public | pgio3 | table | postgres | 8192 MB |
public | pgio4 | table | postgres | 8192 MB |
public | pgio5 | table | postgres | 8192 MB |
public | pgio6 | table | postgres | 8192 MB |
public | pgio7 | table | postgres | 8192 MB |
public | pgio8 | table | postgres | 8192 MB |
public | pgio9 | table | postgres | 8192 MB |
public | pgio_audit_table | table | postgres | 8192 bytes |
public | pgio_base | table | postgres | 1170 MB |
(18 rows)
$
TESTING I/O WITH THE runit.sh SCRIPT
------------------------------------
$ cat pgio.conf
UPDATE_PCT=0
RUN_TIME=120
NUM_SCHEMAS=16
NUM_THREADS=8
WORK_UNIT=255
UPDATE_WORK_UNIT=8
SCALE=8G
DBNAME=pg10
CONNECT_STRING="pg10"
CREATE_BASE_TABLE=TRUE
$
$ sh ./runit.sh
Date: Tue May 22 16:30:36 UTC 2018
Database connect string: "pg10".
Shared buffers: 1GB.
Testing 16 schemas with 8 thread(s) accessing 8G (1048576 blocks) of each schema.
Running iostat, vmstat and mpstat on current host--in background.
Launching sessions. 16 schema(s) will be accessed by 8 thread(s) each.
pg_stat_database stats:
datname| blks_hit| blks_read|tup_returned|tup_fetched|tup_updated
BEFORE: pg10 | 792845449 | 1708568898 | 1803194168 | 1510064234 | 5536938
AFTER: pg10 | 793924241 | 1767645677 | 1862352512 | 1569219899 | 5536938
DBNAME: pg10. 16 schemas, 8 threads(each). Run time: 120 seconds. RIOPS >492306< CACHE_HITS/s >8989<
$
REDUCING THE AMOUNT OF SYSTEM MEMORY AVAILABLE TO THE OS PAGE CACHE (FILE SYSTEM BUFFER CACHE)
----------------------------------------------------------------------------------------------
#
#
# sh ./pgio_reduce_free_memory.sh
Usage: ./pgio_reduce_free_memory.sh <integer amount of memory (in gigabytes) to remain free.>
#
# sh ./pgio_reduce_free_memory.sh 16
Enter "YES" to consume free memory leaving only 16GB free ( 3.3 % of all RAM) : YES
Taking action to reduce free memory down to 16GB available.
total used free shared buff/cache available
Mem: 503413700 4321864 498782052 33868 309784 496886768
Swap: 0 0 0
Attempting to allocate 235354 huge pages
MemAvailable: 16026932 kB
HugePages_Total: 235354
#
# free -h
total used free shared buff/cache available
Mem: 480G 462G 17G 33M 301M 15G
Swap: 0B 0B 0B
#
# exit
exit
$
TESTING I/O AFTER REDUCING THE SIZE OF THE OS PAGE CACHE
--------------------------------------------------------
$ sh ./runit.sh
Date: Tue May 22 16:39:58 UTC 2018
Database connect string: "pg10".
Shared buffers: 1GB.
Testing 16 schemas with 8 thread(s) accessing 8G (1048576 blocks) of each schema.
Running iostat, vmstat and mpstat on current host--in background.
Launching sessions. 16 schema(s) will be accessed by 8 thread(s) each.
pg_stat_database stats:
datname| blks_hit| blks_read|tup_returned|tup_fetched|tup_updated
BEFORE: pg10 | 793926878 | 1767645848 | 1862362328 | 1569220779 | 5536940
AFTER: pg10 | 794529712 | 1796882661 | 1891681812 | 1598537584 | 5536962
DBNAME: pg10. 16 schemas, 8 threads(each). Run time: 120 seconds. RIOPS >243640< CACHE_HITS/s >5023<
$
VALIDATING PHYSICAL I/O WITH THE iostat.out FILE
------------------------------------------------
$ mount | grep data
/dev/md127 on /data type xfs (rw,relatime,seclabel,attr2,inode64,sunit=2048,swidth=16384,noquota)
$ grep Device iostat.out | tail -1; grep '^md127' iostat.out | head -10
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
md127 0.00 0.00 716.23 100.24 7.11 2.65 24.47 0.00 0.00 0.00 0.00 0.00 0.00
md127 0.00 0.00 263726.33 212.33 2318.33 1.86 18.00 0.00 0.00 0.00 0.00 0.00 0.00
md127 0.00 0.00 267530.67 181.67 2875.68 1.53 22.01 0.00 0.00 0.00 0.00 0.00 0.00
md127 0.00 0.00 248699.00 201.33 2834.69 1.74 23.34 0.00 0.00 0.00 0.00 0.00 0.00
md127 0.00 0.00 242310.70 224.08 2734.00 1.89 23.10 0.00 0.00 0.00 0.00 0.00 0.00
md127 0.00 0.00 237546.00 201.67 2638.42 1.71 22.74 0.00 0.00 0.00 0.00 0.00 0.00
md127 0.00 0.00 233098.67 180.07 2680.31 1.54 23.54 0.00 0.00 0.00 0.00 0.00 0.00
md127 0.00 0.00 240512.00 181.33 2706.39 1.53 23.04 0.00 0.00 0.00 0.00 0.00 0.00
md127 0.00 0.00 237074.67 180.00 2669.72 1.49 23.06 0.00 0.00 0.00 0.00 0.00 0.00
md127 0.00 0.00 233455.33 116.67 2630.99 0.98 23.08 0.00 0.00 0.00 0.00 0.00 0.00
$
LOADING ONE TERABYTE TEST DATA WITH THE setup.sh SCRIPT
-------------------------------------------------------
$ cat pgio.conf
UPDATE_PCT=0
RUN_TIME=120
NUM_SCHEMAS=16
NUM_THREADS=16
WORK_UNIT=255
UPDATE_WORK_UNIT=8
SCALE=64G
DBNAME=pg10
CONNECT_STRING="pg10"
CREATE_BASE_TABLE=TRUE
$ sh ./setup.sh
Job info: Loading 64G scale into 16 schemas as per pgio.conf->NUM_SCHEMAS.
Batching info: Loading 16 schemas per batch as per pgio.conf->NUM_THREADS.
Base table loading time: 112 seconds.
Waiting for batch. Global schema count: 16. Elapsed: 0 seconds.
Waiting for batch. Global schema count: 16. Elapsed: 2052 seconds.
Group data loading phase complete. Elapsed: 2052 seconds.
$
TESTING WITH THE ONE TERABYTE ACTIVE DATA SET WITH THE runit.sh SCRIPT
----------------------------------------------------------------------
$ sh ./runit.sh
Date: Tue May 22 18:59:25 UTC 2018
Database connect string: "pg10".
Shared buffers: 1GB.
Testing 16 schemas with 32 thread(s) accessing 64G (8388608 blocks) of each schema.
Running iostat, vmstat and mpstat on current host--in background.
Launching sessions. 16 schema(s) will be accessed by 32 thread(s) each.
Sessions launched. NOTE: Launching the sessions took 288 seconds.
pg_stat_database stats:
datname| blks_hit| blks_read|tup_returned|tup_fetched|tup_updated
BEFORE: pg10 | 325562895 | 349278820 | 249077711 | 114757082 | 201
AFTER: pg10 | 325909798 | 365573275 | 265373121 | 131049941 | 201
DBNAME: pg10. 16 schemas, 32 threads(each). Run time: 123 seconds. RIOPS >390969< CACHE_HITS/s >7795<
$