Archive for the 'oracle' Category

Sneak Preview of pgio (The SLOB Method for PostgreSQL) Part IV: How To Reduce The Amount of Memory In The Linux Page Cache For Testing Purposes.

I hope these sneak peeks are of interest…

PostgreSQL and Buffered I/O

PostgreSQL uses buffered I/O. If you want to test your storage subsystem capabilities with database physical I/O you have to get the OS page cache “out of the way”–unless you want to load really large test data sets.

Although pgio (the SLOB Method for PostgreSQL) is still in Beta, I’d like to show this example of the tool I provide for users to make a really large RAM system into an effectively smaller RAM system.

Linux Huge Pages

Memory allocated to huge pages is completely cordoned off unless a process allocates some IPC shared memory (shmget(1)).  The pgio kit comes with a simple tools called pgio_reduce_free_memory.sh which leverages this quality of huge pages in order to draw down available memory so that one can test physical I/O with a database size that is quite smaller than the amount of physical memory in the database host.

The following picture shows an example of using pgio_reduce_free_memory.sh to draw set aside 443 gigabytes of available memory so as to leave only 32 gigabytes for OS page cache. As such, one can test a pgio database of, say, 64 gigabytes and generate a tremendous about of physical I/O.

 

pgio1

 

I should think this little tool could be helpful for a lot of testing purposes beyond pgio.

Sneak Preview of pgio (The SLOB Method for PostgreSQL) Part III: Link To The Full README file for Beta pgio v0.9.

If you are interested in a head start on pgio, the following is a link to the full README file which has some loading and testing how-to:

The pgio text README file version 0.9 Beta

Sneak Preview of pgio (The SLOB Method for PostgreSQL) Part II: Bulk Data Loading.

Bulk Data Loading With pgio Version 0.9 Beta

Now that pgio (the SLOB Method for PostgreSQL) is in Beta users’ hands I’m going to make a few quick blog entries with examples of pgio usage. The following are screen grabs taken while loading 1 terabyte into the pgio schemas. As the example shows, pgio (on a system with ample storage performance) can ready a 1 terabyte data set for testing in only 1014 seconds (roughly 3.5TB/h loading rate).

$ cat pgio.conf
UPDATE_PCT=0
RUN_TIME=120
NUM_SCHEMAS=4
NUM_THREADS=2
WORK_UNIT=255
UPDATE_WORK_UNIT=8
SCALE=1G

DBNAME=pg10
CONNECT_STRING=”pg10″

CREATE_BASE_TABLE=TRUE

$ echo ”
> UPDATE_PCT=0
> RUN_TIME=120
> NUM_SCHEMAS=64
> NUM_THREADS=16
> WORK_UNIT=255
> UPDATE_WORK_UNIT=8
> SCALE=16G
> DBNAME=pg10
> CONNECT_STRING=\”pg10\”
> CREATE_BASE_TABLE=TRUE ” > pgio.conf
$
$ sh ./setup.sh

Job info: Loading 16G scale into 64 schemas as per pgio.conf->NUM_SCHEMAS.
Batching info: Loading 16 schemas per batch as per pgio.conf->NUM_THREADS.
Base table loading time: 23 seconds.
Waiting for batch. Global schema count: 16. Elapsed: 0 seconds.
Waiting for batch. Global schema count: 31. Elapsed: 243 seconds.
Waiting for batch. Global schema count: 46. Elapsed: 475 seconds.
Waiting for batch. Global schema count: 61. Elapsed: 697 seconds.
Waiting for batch. Global schema count: 64. Elapsed: 935 seconds.

Group data loading phase complete. Elapsed: 1014 seconds.
$
$ psql pg10
psql (10.0)
Type “help” for help.

pg10=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
——–+——————+——-+———-+————+————-
public | pgio1 | table | postgres | 16 GB |
public | pgio10 | table | postgres | 16 GB |
public | pgio11 | table | postgres | 16 GB |
public | pgio12 | table | postgres | 16 GB |
public | pgio13 | table | postgres | 16 GB |
public | pgio14 | table | postgres | 16 GB |
public | pgio15 | table | postgres | 16 GB |
public | pgio16 | table | postgres | 16 GB |
public | pgio17 | table | postgres | 16 GB |
public | pgio18 | table | postgres | 16 GB |
public | pgio19 | table | postgres | 16 GB |
public | pgio2 | table | postgres | 16 GB |
public | pgio20 | table | postgres | 16 GB |
public | pgio21 | table | postgres | 16 GB |
public | pgio22 | table | postgres | 16 GB |
public | pgio23 | table | postgres | 16 GB |
public | pgio24 | table | postgres | 16 GB |
public | pgio25 | table | postgres | 16 GB |
public | pgio26 | table | postgres | 16 GB |
public | pgio27 | table | postgres | 16 GB |
public | pgio28 | table | postgres | 16 GB |
public | pgio29 | table | postgres | 16 GB |
public | pgio3 | table | postgres | 16 GB |
public | pgio30 | table | postgres | 16 GB |
public | pgio31 | table | postgres | 16 GB |
public | pgio32 | table | postgres | 16 GB |
public | pgio33 | table | postgres | 16 GB |
public | pgio34 | table | postgres | 16 GB |
public | pgio35 | table | postgres | 16 GB |
public | pgio36 | table | postgres | 16 GB |
public | pgio37 | table | postgres | 16 GB |
public | pgio38 | table | postgres | 16 GB |
public | pgio39 | table | postgres | 16 GB |
public | pgio4 | table | postgres | 16 GB |

Sneak Preview of pgio (The SLOB Method for PostgreSQL) Part I: The Beta pgio README File.

The pgio kit is the only authorized port of the SLOB Method for PostgreSQL. I’ve been handing out Beta kits to some folks already but I thought I’d get some blog posts underway in anticipation of users’ interest.

The following is part of the README.txt for pgio v0.9 (Beta). SLOB users will find it all easy to understand. This is the section of the README that discusses pgio.conf parameters:

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

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

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.

 

 

 

 

SLOB Can Now Be Downloaded From GitHub.

This is a quick blog entry to announce that the SLOB distribution will no longer be downloadable from Syncplicity. Based on user feedback I have switched to making the kit available on GitHub. I’ve updated the LICENSE.txt file to reflect this distribution locale as authorized and the latest SLOB version is 2.4.2.1.

Please visit kevinclosson.net/slob for more information.

downloadbutton

Whitepaper Announcement: Migrating Oracle Database Workloads to Oracle Linux on AWS

This is just a quick blog entry to share a good paper on migrating Oracle Database workloads to Amazon Web Services EC2 instances running Oracle Linux.

Please click the following link for a copy of the paper:  Click Here.

 

Whitepaper Announcement: Benchmarking Amazon Aurora.

This is just a quick blog post to inform readers of a good paper that shows some how-to information for benchmarking Amazon Aurora PostgreSQL. This is mostly about sysbench which is used to test transactional capabilities.

As an aside, many readers my have heard that I’m porting SLOB to PostgreSQL and will make that available in May 2018. It’ll be called “pgio” and is an implemention of the SLOB Method as described in the SLOB documentation. Adding pgio, to tools like sysbench, rounds-out the toolkit for testing platform readiness for your PostgreSQL applications.

To get a copy of the benchmarking paper, click here.


DISCLAIMER

I work for Amazon Web Services. The opinions I share in this blog are my own. I'm *not* communicating as a spokesperson for Amazon. In other words, I work at Amazon, but this is my own opinion.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,018 other followers

Oracle ACE Program Status

Click It

website metrics

Fond Memories

Copyright

All content is © Kevin Closson and "Kevin Closson's Blog: Platforms, Databases, and Storage", 2006-2015. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Kevin Closson and Kevin Closson's Blog: Platforms, Databases, and Storage with appropriate and specific direction to the original content.

%d bloggers like this: