workload

Log-dedicated loop device throughput and time overhead on btrfs 4.x


This is again about real world numbers (which I like so much for being authentic ;-). The context being the throughput and time overhead of a loop device, as a poor or late man’s replacement for a real disk partition, jep I know, on copy-on-write filesystem btrfs, exclusively dedicated for logging, that is appending over and over. Why? The loop device may overflow with data without affecting the underlying filesystem, see Btrfs subvolume quota still in its infancy with btrfs version 4.2.2 for more why’s and what I tried to get btrfs subvolume with quota to work. By the way, about that though, see debian org Btrfs for a down-to-earth assessment of btrfs to date, even uttering a recommendation from what version number (4.4) to start off at the earliest near production. Anyway, what follows, adapts a test setup as in Performance of loopback filesystems, prime credits go there, and expands the layout somewhat for the btrfs C or nodatacow flag. Here we go.

Have this baseline, if you like, test on the raw iron. Well, its not raw iron really, its vmware and tons of storage below, and the shown performance is terrible I know and I only take one testset of bs / count but that won’t matter. There’s something to start off.

mkdir /tmp/loop0
dd if=/dev/zero bs=1M of=/tmp/loop0/file oflag=sync count=1000
1048576000 bytes (1.0 GB) copied, 8.9605 s, 117 MB/s
1048576000 bytes (1.0 GB) copied, 6.52867 s, 161 MB/s
1048576000 bytes (1.0 GB) copied, 5.35716 s, 196 MB/s
1048576000 bytes (1.0 GB) copied, 5.48745 s, 191 MB/s
1048576000 bytes (1.0 GB) copied, 5.14736 s, 204 MB/s

(more…)

Oracle global temp tabs or nologging/append – saved redo in numbers


In an attempt to reduce redo entries (and archive space) generated for only temporarily computed contents of a couple of simple tables, I regarded employing Global Temporary Tables as well as Nologging Inserts as redesign options. This article, however, is not meant to dive into the details of these redesign options at all. There’s plenty of examples and discussions about the pros and cons of each option around the net. Most notably to mention, and a stimulus to post just my redo reduction test results, is Tim Hall’s compact articles on the subject like https://oracle-base.com/articles/misc/temporary-tables and https://oracle-base.com/articles/misc/append-hint that do include an assessment of the redo matter.

So, what’s the lineup for short? On startup, some data will be sourced throughout the database, losely computed and brutely written into a couple of simple tables. Then some third party will read this data for own purposes and will report completion such that the data can eventually be discarded completely. In making shure that an iteration step always starts unsoiled, any data left over from a crashed precursor will be rubbed out as well. Nobody cares about backups of this data, iteration management runs on other tables. You see, this is not really something that takes a database to complete successfully but…

(more…)

Identifying excessive load causes with oracle enterprise manager 11g


There are times when people ask me to identify the root causes of excessive Oracle Database (and host) loads in the past, still being astonished how i do manage to get this information out of nothing. Well, really, there is no out of nothing, never. There may be a complex scenario and a couple of tools at hand which span another level of complexity, unfortunately. That’s all of it. You see, complexity is a bad thing, something you will want to avoid or eliminate at all times. Some people furthermore aim to prove their skills in talking about what degree of complexity they are able to handle… hhm, poor ones, I don’t envy them, always struggling to tighten a plethora of strings, over and over, I’m more a convention-over-configuration guy, but that’s another discussion.

Back to to the point, Oracle Database is a vast stack of technology, indeed inducing a fair level of complexity in analysis and so is Oracle Enterprise Manager, the number one monitoring tool for the database and further (so called) targets like hosts and jvms, in operation. In fact, Oracle Enterprise Manager, being based on data collection in the database (AWR – Automatic Workload Repository), data evaluation in the database (ADDM – Automatic Database Diagnostic Monitor) and data transfer to some production instance (Enterprise Manager Agent) constitutes another mountain top in the Oracle landscape not everyone is able or willing to climb. So iff you misplaced your hiking boots for today, the following is for you.

Ok then, log yourself in, using your EM credentials.

(more…)

(raw) Oracle Linux 6 memory foorprint with/out X11


Running quite a couple of (Linux) guests on a virtual host sooner or later raises the question of the (guests / host resources) ratio. That is, commonly for cpu, ram and i/o, how many guests will fit on that specific host for an average load.

Having this question nagging in my head, I was particularly curious to find out how much memory an OL6 will consume for a pure operating system installation with and without the convenience of running X11 (which is animated by Gnome 3.x in OL6, having most of the autostart apps removed – xfce will be much leaner but that’s another story).

The top Mem: used snapshots were taken immediately after a bounce of the guest to have as less application code inference as possible since Linux never frees memory iff not necessary. Here we go.

(more…)

Implementing CANCEL_SQL with the resource manager


introduction

this article is more a recipe on how to get CANCEL_SQL working for a dedicated use case than a discussion of oracle resource manager concepts and technology. but, however, as long as CANCEL_SQL can not be implemented without having at least a basic understanding of resource manager, be adviced to study the relevant oracle documentation (from 10g onwards) before running or even reintegrating the code below. It is important to learn that activating a resource manager plan is a system wide effect because there can only be one resource manager plan active at one defined point in time. additionally, others may also be granted to activate or switch resource manager plans. sounds like the big wheel and side effects en masse? no, not really, you just need to know where to plug in your resource requirements and how to adapt your application code accordingly.

(more…)