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

$