Feed aggregator
Question - increase size of number column
SQL Performance differences with STATS gather
AUD$ EXP/TRUNCATE/IMP (feasibility)
How does Oracle Database provide the user$.user#
LLM Structured Output for Function Calling with Ollama
Der Aufschlag im #Pickleball
Das Beste vorweg: Der Aufschlag im Pickleball ist leicht zu lernen und auszuführen. Anders als etwa im Tennis oder Tischtennis, wo man typischerweise viel Zeit mit der Übung des Aufschlags verbringt, um konkurrenzfähig zu sein. Die Regeln erschweren es zudem beträchtlich, dass der Aufschlag zur spielentscheidenden „Waffe“ werden kann. Asse oder zwingender Vorteil nach dem Aufschlag sind daher ziemlich selten.
RegelnDie Regeln zur Positionierung gelten sowohl für den Volley-Serve als auch für den Drop-Serve:
Zum Zeitpunkt, wenn der Ball beim Aufschlag auf den Schläger trifft, müssen die Füße des aufschlagenden Spielers hinter der Grundlinie und innerhalb verlängerten Linien der jeweiligen Platzhälfte sein:
Der Oberkörper des Spielers und der Ball dürfen sich dabei innerhalb des Spielfelds befinden. Außerdem darf der Spieler das Spielfeld betreten, unmittelbar nachdem der Ball den Schläger verlassen hat.
Vorher aber nicht:
Schon das Berühren der Grundlinie mit der Fußspitze beim Aufschlag ist ein Fehler.
Man darf auch nicht beliebig weit außen stehen:
Der linke Fuß ist hier außerhalb der verlängerten Linien der linken Platzhälfte, weshalb dieser Aufschlag nicht regelgerecht wäre.
Wer schlägt wann wohin auf, und was ist mit der NVZ?Es muss jeweils das diagonal gegenüberliegende Feld getroffen werden, wobei der Ball nicht in der NVZ landen darf. Die Linien gehören dabei zur NVZ: Ein Ball auf die hintere Linie der NVZ ist ein Fehler. Genauso gehören die Linien zum Aufschlagfeld: Ein Ball auf die Grundlinie oder ein Ball auf die Außenlinie ist also kein Fehler. Wird der Punkt gewonnen, wechselt der Aufschläger mit seinem Partner die Seite. Verliert der erste Aufschläger die Rally, macht sein Partner von der Seite weiter, wo er grad steht. Verliert auch der zweite Aufschläger die Rally, wechselt der Aufschlag auf das andere Team. Die Zählweise und die jeweilige Positionierung der Spieler hab ich in diesem Artikel behandelt.
Volley-ServeDas ist derzeit der beliebteste Aufschlag und ursprünglich auch der einzig erlaubte Aufschlag. Der Ball wird dabei aus der Hand aufgeschlagen.
Der Schlägerkopf muss dabei eine Bewegung von unten nach oben ausführen, wie in 4-3 zu sehen.
Der Schlägerkopf darf sich zum Zeitpunkt des Auftreffen des Balls nicht über dem Handgelenk befinden (4-1 zeigt die korrekte Ausführung, 4-2 ist ein häufig zu beobachtender Fehler).
Außerdem muss der Ball unterhalb der Taille des Aufschlägers getroffen werden, wie in 4-3 zu sehen.
Das obige Bild stammt aus dem Official Rulebook der USAP.
Alle Regeln zum Volley-Serve sollen im Grunde sicherstellen, dass dieser Aufschlag eben nicht zum spielentscheidenden Vorteil wird. Im folgenden Clip sehen wir einen Aufschlag von Anna Leigh Waters, der momentan besten Spielerin der Welt:
Diese Art des Aufschlags ist bei den Pros am häufigsten zu sehen: Volley-Serve, Top-Spin, tief ins Feld gespielt. Wir sehen aber auch, dass ihre Gegenspielerin den Aufschlag ohne große Mühe returniert. Asse oder direkt aus dem Aufschlag resultierender Punktgewinn sind im Pickleball ziemlich selten. Ganz im Gegensatz etwa zu Tennis und Tischtennis.
Drop-ServeDiese Art des Aufschlags ist erst seit 2021 erlaubt. Abgesehen von den oben beschriebenen Regeln zur Positionierung der Füße gibt es beim Drop-Serve nur eine weitere Regel: Der Ball muss aus der Hand fallengelassen werden. Hochwerfen oder nach unten Stoßen/Werfen des Balls ist nicht erlaubt. Insbesondere darf der Ball auf beliebige Art geschlagen werden. Das macht diesen Aufschlag besonders Einsteigerfreundlich, weil man kaum die Regeln verletzen kann.
Ich habe dem Drop-Serve bereits diesen Artikel gewidmet.
Abgesehen von den Regeln – wie sollte man aufschlagen?Es gibt hier zwei grundsätzliche Herangehensweisen:
Die einen sagen, weil man mit dem Aufschlag ohnehin selten einen direkten Punkt macht, sollte man ihn nur möglichst sicher in das hintere Drittel des Felds reinspielen:
Ins hintere Drittel, weil die Rückschläger sonst zu leicht einen starken Return spielen und die Aufschläger hinten halten können. Mit der gelben Zielzone ist es unwahrscheinlich, dass der Aufschlag aus geht.
Die anderen (zu denen ich auch gehöre) sagen: Mit dem Aufschlag kann man ruhig etwas Risiko eingehen. Schließlich kann das Return-Team keinen Punkt machen. Es ist okay, wenn von 10 Aufschlägen 2 ausgehen und die übrigen 8 es den Rückschlägern schwer machen, uns hinten zu halten. Der eine oder andere direkte Punkt sollte auch dabei sein. Darum sehen meine Zielzonen so aus:
Die meisten Aufschläge gehen Richtung Zielzone 1, ab und an mal einer nach 2 und 3. Die roten Zonen sind deutlich näher an den Linien als die gelbe, was natürlich die Gefahr eines Ausballs erhöht.
Im Allgemeinen kann man zum Aufschlag im Pickleball sagen:
Länge ist wichtiger als Härte oder Spin. Ein entspannt in hohem Bogen ins hintere Drittel des Aufschlagfelds gelobbter Ball macht dem Rückschläger mehr Probleme als ein harter Topspin in die Mitte. Kurze Aufschläge sind sporadisch eingesetzt als Überraschungswaffe gut, ansonsten erleichtern sie es dem Rückschläger nur, nach vorn an die NVZ zu kommen.
Grid Infrastructure --- OCR and Voting Disks Location at Installation
In Oracle Grid Infrastructure, the OCR (Oracle Cluster Registry) and Voting "Disks" must be on Shared Storage accessible by all the nodes of the Cluster. Typically, these are on ASM.
In ASM, a DiskGroup is created for the disks that hold the OCR.
Normally, an ASM DiskGroup may use External Redundancy (Mirroring or other protection against Physical Disk or LUN failure is provided by the underlying Storage) or Normal Redundancy (Two-Way Mirroring, i.e. two Disks or LUN devices) or High Redundancy (Three-Way Mirroring with three Disks).
However, for the OCR and Voting "Disks" (i.e. Voting File), Normal Redundancy requires three Disks or LUN devices where three Voting Files and one OCR (Primary and Secondary copy) are created. High Redundancy requires five Disks or LUN devices where five Voting Files and once OCR (with one Primary and two Secondary copies) are created.
In Test or Lab environments, you might have created your OCR/Vote DiskGroup on ASM storage with External Redundancy so as to not have to provision 3 or 5 disks.
However, in the 19c Lab environment with 2 Virtual Box VMs that I recently built on my Home PC, I created 5 ASM Disks of 2GB each (using ASMLib instead of udev persistent naming) to hold the OCR + VOTE DiskGroup. I then selected High Redundancy for the consequent DiskGroup.
This is the Installer Screen :
This is the subsequent output from running root.sh from the Grid ORACLE_HOME towards the end of the installation :
[datetime] CLSRSC-482: Running command: '/u01/app/grid/product/19.3/bin/ocrconfig -upgrade grid grid'
CRS-4256: Updating the profile
Successful addition of voting disk 6c3ea5fbf0254fd5bfd489fc5c674409.
Successful addition of voting disk ff3b9da031064fccbfab4b57933f12e1.
Successful addition of voting disk 44e50015bcf24f7cbfc1b9348fdbe568.
Successful addition of voting disk de64da366c164f5cbfba2761df5948d5.
Successful addition of voting disk 4485ff5940384f85bf524a81090c6bd8.
Successfully replaced voting disk group with +OCR_VOTE.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 6c3ea5fbf0254fd5bfd489fc5c674409 (/dev/oracleasm/disks/OCR_VOTE_DISK_1) [OCR_VOTE]
2. ONLINE ff3b9da031064fccbfab4b57933f12e1 (/dev/oracleasm/disks/OCR_VOTE_DISK_2) [OCR_VOTE]
3. ONLINE 44e50015bcf24f7cbfc1b9348fdbe568 (/dev/oracleasm/disks/OCR_VOTE_DISK_3) [OCR_VOTE]
4. ONLINE de64da366c164f5cbfba2761df5948d5 (/dev/oracleasm/disks/OCR_VOTE_DISK_4) [OCR_VOTE]
5. ONLINE 4485ff5940384f85bf524a81090c6bd8 (/dev/oracleasm/disks/OCR_VOTE_DISK_5) [OCR_VOTE]
Located 5 voting disk(s).
Thus it did create 5 Voting "Disks" (Voting Files).
After the installation is completed, I verified this again
from the first node "srv1":
and from the second node "srv2" :
Note : Whether I create the DiskGroup with Normal or High Redundancy, it will still show only 1 OCR because there is only 1 Primary OCR location (Normal or High Redundancy will automatically create 1 or 2 Secondary OCR copy).
It is possible to add another location for OCR in this manner (where I add to the FRA DiskGroup):
Furthermore, each node of the Cluster has a Local Cluster Registry (that is called an OLR) :
If you are worried about Failure Groups for the OCR_VOTE DiskGroup, you can see that the FailureGroups are automatically created for this High Redundancy DiskGroup :
Index Usage – 2
In the first part of this series I introduced Index Usage Tracking and the view dba_index_usage – a feature that appeared in 12.2 as a replacement for index monitoring and the view dba_object_usage. In this note I’ll give a quick sketch of the technicalities of the implementation and comments on how to test and use the feature. Actual tests, results and observations will have to wait until part 3.
A not very deep diveThere are three parameters relating to Index Usage Tracking (iut), shown below with their default values:
- _iut_enable [TRUE]
- _iut_max_entries [30000]
- _iut_stat_collection_type [SAMPLED]
The feature is, as you can see, enabled by default; the tracking, however, is “sampled”, which seems to mean that a tiny number of executions end up being tracked. I can’t find any information about how the sampling is done, and having tried a few tests that executed thousands of statements in a couple of minutes without capturing any details of index usage I’ve given up trying and done all my testing with “_iut_stat_collection_type” set to ALL.
SQL> alter session set "_iut_stat_collection_type"=all;
According to a note on MOS (Doc ID 2977302.1) it doesn’t matter whether you set this parameter for the session or the system the effect is the same; and I found that this seemed to be true in my testing on Oracle 19.11 – either way the effect appeared across all sessions connecting to the PDB, though it didn’t seem to persist across a database restart.
The parameter _iut_max_entries probably limits the amount of global memory allowed for collecting stats about indexes. You might ask whether the 30,000 is per PDB or for the entire instance; I suspect it’s for the instance as a whole, but I’m not going to run up a test to scale on that. While I know of several 3rd party applications holding far more indexes than this, the number is probably sufficient for most investigations.
There are eight objects visibly related to Index Usage Tracking: three views, one table, three memory structures and one latch:
- dba_index_usage – the user (dba) friendly view of the accumulated statistics of index usage
- cdb_index_usage – the cdb equivalent of the above
- v$index_usage_info – a view (holding one row) summarising the current tracking status
- sys.wri$_index_usage – the main table behind the xxx_index_usage views above; the views join this table to obj$ and user$, so dropped indexes (and users) disappear from the views.
- x$keiut_info – the memory structure (held in the shared pool) behind the v$index_usage_info
- x$keiut – a structure holding a brief summary for each index actively being tracked. This is generated on demand in the session/process memory and my guess is that it’s an extract or summary of a larger memory structure in the shared pool holding the full histogram data for each index.
- htab_keiutsg – a (small) memory allocation reported by v$sgastat in the shared pool. In my 19.11 the memory size was initially 512 bytes, and in a test with 140 indexes showing up in x$keiut the memory reported was still only 512 bytes (so it’s not a simple list of pointers, more likely a set of pointers to pointers/arrays.
- “keiut hash table modification” – a single parent latch which I assume protects the htab_keiutsg memory. It’s possible that this latch is used to add an entry to the x$keiut structure (or, rather, the larger structure behind it) when an index is first tracked by the software, and that each entry in that larger structure is then protected by its own mutex to minimise collision time on updates as the stats are updated (or cleared after flushing).
Given that there’s a limit of 30,000 for iut_max_entries and only a small memory allocation for the keiut hash table, it does sound as if Oracle could end up walking a fairly long linked list or array to find the correct entry to update, which makes me wonder about two things: first, have I missed something obvious, secondly will Oracle skip updating the stats if the alternative means waiting for a mutex? There’s also the question of whether Oracle simply stops collecting when the limit is reached or whether there’s some sort LRU algorithm that allows it to discard entries for rarely used indexes to get maximum benefit from the available limit.
Another thought that goes with the 30,000 limit. I can find the merge statement that Oracle uses to update the wri$_index_usage table when the stats are flushed from memory to table (an activity that takes place every 15 minutes, with no obvious parameter to change the timing). In my19.11 instance its sql_id is 5cu0x10yu88sw, and it starts with the text:
merge into
sys.wri$_index_usage iu
using
dual
on (iu.obj# = :objn)
when matched then
update set
iu.total_access_count = iu.total_access_count + :ns,
iu.total_rows_returned = iu.total_rows_returned + :rr,
iu.total_exec_count = iu.total_exec_count + :ne,
...
This statement updates the table one row at a time (which you can confirm if you can find it in v$sql and compare rows_processed with executions). This could take a significant amount of time to complete on a system with a very large number of indexes.
The other thing that comes with finding the merge statement is that I couldn’t find any indication that there is a delete statement – either in v$sql, or in the Oracle executable. Spreading the search a little further I queried dba_dependencies and found that the package dbms_auto_index_internal references wri$_index_usage and various of the “autotask” packages – so perhaps there’s something a couple of layers further down the PL/SQL stack that generates dynamic SQL to delete tracking data. On the other hand, there are entries in my copy of wri$_index_usage where the last_used column has dates going back to September 2021, and there are a number of rows where the reported index has been dropped.
Testing the feature.The most significant difficulty testing the mechanism is that it flushes the in-memory stats to the table every 15 minutes, and it’s only possible to see the histogram of index usage from the table. Fortunately it is possible to use oradebug to force mmon to trigger a flush, but I found in my Oracle 19.11 PDB I had to have a session logged into the server as the owner of the Oracle executable, and logged into the cdb$root as the SYS user (though a couple of colleagues had different degrees of success on different versions of Oracle and O/S). The following is a cut and paste after logging in showing appropriate calls to oradebug:
SQL> oradebug setorapname mmon
Oracle pid: 31, Unix process pid: 11580, image: oracle@linux19c (MMON)
SQL> oradebug call keiutFlush
Function returned 0
SQL>
Initially I had assumed I could log on as a rather more ordinary O/S user and connect as SYS to the PDB, but this produced an unexpected error when I tried to execute the flush call:
SQL> oradebug call keiutFlush
ORA-32519: insufficient privileges to execute ORADEBUG command: OS debugger privileges required for client
In my testing, then, I’m going to open three sessions:
- End-user session – a session to execute some carefully designed queries.
- cdb$root SYS session – a session to flush stats from memory to disc.
- PDB SYS session – a session to show the effects of the end-user activity (reporting figures from x$keiut_info, x$keiut, and dba_index_usage)
I’ll be running some simple tests, covering select, insert, update, delete and merge statements with single-column indexes, multi-column indexes, locally partitioned indexes, single table queries, nested loop joins, range scans, fast full scans, skip scans, inlist iterators, union views, stats collection and referential integrity. For each test I’ll describe how the index will be used, then show what the stats look like. Given that what we really need to see are the changes in x$keiut and dba_index_usage I’ll only show the complete “before / after” values in one example here. In part 3 of the series you’ll have to trust that I can do the arithmetic and report the changes correctly.
ExampleFrom the end-user session I have a table created with the following code:
rem
rem Script: iut_01.sql
rem Author: Jonathan Lewis
rem Dated: Mar 2024
rem Purpose:
rem
rem Last tested
rem 19.11.0.0
create table t1 as
select
rownum id,
mod(rownum-1,10000) n1,
trunc((rownum - 1)/20) col1,
trunc((rownum - 1)/10) col2,
rownum - 1 col3,
cast(rpad(rownum,25) as varchar2(25)) v1,
cast(rpad('x',80,'x') as varchar2(80)) padding
from
all_objects
where
rownum <= 50000
/
create index t1_pk on t1(id);
create index t1_n1 on t1(n1);
create index t1_i1 on t1(col1, col2, col3);
From the cdb$root logged on as oracle (executable owner) and connected as SYS:
SQL> startup force
ORACLE instance started.
Total System Global Area 1476391568 bytes
Fixed Size 9134736 bytes
Variable Size 822083584 bytes
Database Buffers 637534208 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> oradebug setorapname mmon
Oracle pid: 31, Unix process pid: 27738, image: oracle@linux19c (MMON)
SQL> oradebug call keiutFlush
Function returned 0
From an ordinary O/S user, connected to the PDB as SYS:
SQL> select index_stats_collection_type, alloc_elem_count, active_elem_count, flush_count, last_flush_time from x$keiut_info;
INDEX_STATS_COLLECTION_TYPE ALLOC_ELEM_COUNT ACTIVE_ELEM_COUNT FLUSH_COUNT LAST_FLUSH_TIME
--------------------------- ---------------- ----------------- ----------- ----------------------------
1 0 0 2 19-MAR-24 10.53.50.584 PM
1 row selected.
SQL> alter session set "_iut_stat_collection_type"=all;
Session altered.
SQL> select index_stats_collection_type, alloc_elem_count, active_elem_count, flush_count, last_flush_time from x$keiut_info;
INDEX_STATS_COLLECTION_TYPE ALLOC_ELEM_COUNT ACTIVE_ELEM_COUNT FLUSH_COUNT LAST_FLUSH_TIME
--------------------------- ---------------- ----------------- ----------- ----------------------------
0 0 0 2 19-MAR-24 10.53.50.584 PM
1 row selected.
Note how the index_stats_collection_type changes from 1 to 0 after the “alter session”. I don’t know why the flush_count showed up as 2 when I had only flushed once – but perhaps the second flush is a side effect of altering the collection type.
From an ordinary end-user session
SQL> set feedback only
SQL> select n1 from t1 where id between 1 and 5;
5 rows selected.
SQL> select n1 from t1 where id between 1 and 5;
5 rows selected.
SQL> select n1 from t1 where id between 1 and 50;
50 rows selected.
These queries will use the index t1_pk in an index range scan to access the table by rowid.
From the PDB / SYS
SQL> select index_stats_collection_type, alloc_elem_count, active_elem_count, flush_count, last_flush_time from x$keiut_info;
INDEX_STATS_COLLECTION_TYPE ALLOC_ELEM_COUNT ACTIVE_ELEM_COUNT FLUSH_COUNT LAST_FLUSH_TIME
--------------------------- ---------------- ----------------- ----------- ----------------------------
0 1 1 2 19-MAR-24 10.53.50.584 PM
1 row selected.
SQL> select objnum, objname, num_starts, num_execs, rows_returned from x$keiut;
OBJNUM OBJNAME NUM_STARTS NUM_EXECS ROWS_RETURNED
---------- -------------------------------- ---------- ---------- -------------
208077 TEST_USER.T1_PK 3 3 60
1 row selected.
In the x$keiut_info you can see that Oracle has now allocated one “element”, and has one “active” element. Checking x$keiut (which will report some details of each active element) we can see that my t1_pk index has been used in 3 statement executions, starting a scan a total of 3 times (which matches our expectation) with a total of 60 (= 5 + 5 + 50) rows returned. Of course all we could infer from this one row is that we have returned an average of 20 rows per start, and an average of one start per execution.
From the cdb$root SYS
SQL> oradebug call keiutFlush
Function returned 0
From the PDB SYS (using Tom Kyte’s “print_table”)
SQL> execute print_table(q'[select * from dba_index_usage where name = 'T1_PK' and owner = 'TEST_USER']')
OBJECT_ID : 208077
NAME : T1_PK
OWNER : TEST_USER
TOTAL_ACCESS_COUNT : 3
TOTAL_EXEC_COUNT : 3
TOTAL_ROWS_RETURNED : 60
BUCKET_0_ACCESS_COUNT : 0
BUCKET_1_ACCESS_COUNT : 0
BUCKET_2_10_ACCESS_COUNT : 2
BUCKET_2_10_ROWS_RETURNED : 10
BUCKET_11_100_ACCESS_COUNT : 1
BUCKET_11_100_ROWS_RETURNED : 50
BUCKET_101_1000_ACCESS_COUNT : 0
BUCKET_101_1000_ROWS_RETURNED : 0
BUCKET_1000_PLUS_ACCESS_COUNT : 0
BUCKET_1000_PLUS_ROWS_RETURNED : 0
LAST_USED : 19-mar-2024 23:08:02
From the data saved in the table we can see that we’ve logged 3 accesses, of which 2 accesses returned (individually) something between 2 and 10 rows (rowids) for a total of 10 rows (5 + 5) and one access returned (individually) something between 11 and 100 rows (rowids) for a total of 50 rows.
Of course we can say confidently that the one larger access actually did return 50 rows; but looking at nothing but these figures we can’t infer that the other two access returned 5 rows each, it could have been one query returning 2 rows and the other returning 8, or 3 and 7, or 4 and 6, but we do get a reasonable indication of the volume of data from the breakdown of 0, 1, 2 – 10, 11 – 100, 101 – 1000, 1000+
You might note that we can also see our flush time (reported below) reappearing as the last_used date and time – so we know that we are looking at current statistics.
From the PDB / SYS (again)
SQL> select index_stats_collection_type, alloc_elem_count, active_elem_count, flush_count, last_flush_time from x$keiut_info;
INDEX_STATS_COLLECTION_TYPE ALLOC_ELEM_COUNT ACTIVE_ELEM_COUNT FLUSH_COUNT LAST_FLUSH_TIME
--------------------------- ---------------- ----------------- ----------- ----------------------------
0 1 1 3 19-MAR-24 11.08.02.013 PM
1 row selected.
SQL> select objnum, objname, num_starts, num_execs, rows_returned from x$keiut;
OBJNUM OBJNAME NUM_STARTS NUM_EXECS ROWS_RETURNED
---------- -------------------------------- ---------- ---------- -------------
208077 TEST_USER.T1_PK 0 0 0
1 row selected.
The x$keiut_info shows that a third flush has taken place (and any index flushed at that time will have its last_used set very near to that flush time – the merge command uses sysdate, so the last_used could be a tiny bit after the last_flush_time). It still shows an “active” element and when we check x$keiut we find that t1_pk is still listed but the stats have been reset to zero across the board.
If we were to repeat the flush command the active count would drop to zero and the t1_pk entry would disappear from x$keiut. (Oracle doesn’t remove an element until an entire tracking period has passed with no accesses – a typical type of “lazy” strategy aimed at avoiding unnecessary work.)
That’s all for now – if there are any questions put them in the comments and if their answers belong in this note I’ll update the note.
SPM and GTTs
Kubernetes Network Policy by using Cilium – Beginner Level
In my blog post series about Kubernetes networking, I wrote here that by default, all pods can communicate together in a “vanilla” Kubernetes. Whatever they belong to the same namespace or not. To provide network separation, we need to implement Kubernetes Network Policy.
If your Kubernetes cluster is multi-tenant this is going to be a requirement. Multi-tenant means the same Kubernetes cluster host several applications. Each of them is in their own namespace. However the product owners are different for each application. In this case you’ll need to isolate each application from each other. It would be very bad if from a pod of your application you could reach and connect to a pod of another application and the other way around. To provide this networking isolation we need to configure and deploy Network Policy. They are often the pet peeve of many Kubernetes Administrator that are not familiar with networking. If it is your case, this blog post is for you. I’ll give you all the basics to understand it completely.
Ingress and EgressKubernetes Network Policy is like a police officer in front of your pods. It allows or denies traffic entering or exiting the pod. This is the first concept to grasp, the traffic can flow in 2 directions: entering or exiting. In traditional networking we call it incoming and outgoing (sometimes it is inbound and outbound) traffic. In Kubernetes we are talking about ingress and egress but they are the same thing.
Ingress or egress traffic depends on the point of reference. We basically apply a Network Policy to a pod. Cilium calls it an endpoint but it is the same. For this pod, the direction of traffic is egress when it comes from the pod to the outside. An ingress traffic is for traffic coming from outside and entering into this pod. The drawing below illustrates that concept:
On the left, the police officer looks to the pod so she will stop and filter any traffic coming from that pod before it goes outside. On the right, he looks to the outside with his back to the pod. He will then filter and stop any traffic from outside before it enters that pod.
LabelsAs you may know, pods get their IP Address from Cilium when they start. A pod by design can be deleted and recreated which means its IP Address will change. In traditional networking, we filter traffic with firewalls or Access Control List (ACL). This layer 3 filtering is based on the IP Address. In Kubernetes we then have to use another way than IP Addresses and so we use labels instead. We set a label to each pod and if pods use the same label then they belong to the same group. It is the case for the replicas of a front end or a back end pod that will share the same label. We apply a Network Policy to a group of pods based on this label. Let’s look at this on a drawing:
In our example we have 2 applications in 2 separate namespaces. Each application has 2 Front End (FE) and 2 Back End (BE). In the namespace app1, the FE have the same label “app: frontend1″ and the BE have the label “app:backend1″. We apply the same to the namespace app2.
Cilium Network PolicyKubernetes provides an object called NetworkPolicy that is implemented by the Container Network Interface (CNI). Cilium is using its own object called CiliumNetworkPolicy that extends the capabilities of that standard Network Policy object. You can learn more about it from this Isovalent blog post.
A Network Policy is a yaml file where we configure to which pod labels it applies as well as the ingress and/or egress rules we apply to them. When we apply that yaml file in our cluster, we set the police officer in front of the pods door.
Kubernetes Network Policy ingress ruleLet’s look at an example and start with an ingress rule. We will use the simple CiliumNetworkPolicy below:
apiVersion: "cilium.io/v2"
kind: CiliumNetworkPolicy
metadata:
name: "ingress-backend-rule"
namespace: app1
spec:
endpointSelector:
matchLabels:
app: backend1
ingress:
- fromEndpoints:
- matchLabels:
app: frontend1
This CiliumNetworkPolicy applies to any pod in the namespace app1 that has the label “app: backend1“. Only one ingress rule is applied here where traffic from all pods with the label “app: frontend1” is allowed. Any other ingress traffic will be discarded. Below is the drawing showing it in a visual way:
When any traffic in this Kubernetes cluster reach the pods BE 1 or BE 2 in the namespace app1, the ingress rules of our Network Policy applies. Only traffic coming from the pods FE 1 or FE 2 in the namespace app1 will be allowed to enter because they have the authorized label.
Kubernetes Network Policy egress ruleLet’s continue with a simple egress rule. We will use the CiliumNetworkPolicy below:
apiVersion: "cilium.io/v2"
kind: CiliumNetworkPolicy
metadata:
name: "egress-frontend-rule"
namespace: app1
spec:
endpointSelector:
matchLabels:
app: frontend1
egress:
- toEndpoints:
- matchLabels:
app: backend1
This CiliumNetworkPolicy applies to any pod in the namespace app1 that has the label “app: frontend1“. Only one egress rule is applied here where traffic to pods with the label “app: backend1” is allowed. Any other egress traffic from there pods will be discarded. Below is the drawing showing it in a visual way:
When traffic from the pods FE 1 or FE 2 in the namespace app1 is going out, the egress rule of our Network Policy applies. Only traffic to pods BE 1 and BE 2 will be allowed as they have the authorized label.
Wrap upWe’ve covered the basics of Kubernetes Network Policy by explaining what are its key components: Ingress, egress, labels and the CiliumNetworkPolicy object. Then we’ve seen a simple example of ingress and egress rules and illustrated them with a drawing. These basics should get you started in the world of Kubernetes Network Policy.
With the simple ingress and egress rules above did we completely isolate the traffic flow between FE and BE in app1? The answer is no because from BE 1 or BE 2 I can still reach any pods in the cluster. There is only an ingress rule but no egress rule. The same, any traffic can reach FE 1 or FE 2 because there is only an egress rule but no ingress rule. I would then need to configure an ingress rule and an egress rule (the same as above) into the same CiliumNetworkPolicy. Congratulations, you’ve just entered into the complex world of Kubernetes Network Policy!
L’article Kubernetes Network Policy by using Cilium – Beginner Level est apparu en premier sur dbi Blog.
Pickleball Übung: Drop Spiel 7-11
Einer der wichtigsten Schläge im Pickleball ist der 3rd Shot Drop – also der dritte Schlag einer Rally, wo das aufschlagende Team mittels eines kurzen Balls in die NVZ nach vorn kommen will.
Leider ist das auch ein ziemlich schwieriger Ball, weshalb er häufig geübt werden sollte.
Wenn es euch geht wie mir, findet ihr Spiele um Punkte viel spannender als Übungen. Darum hab ich mir dieses Spiel ausgedacht.
Es geht mit vier, drei oder sogar nur zwei Teilnehmern. Die Beschreibung ist für vier Spieler.
Spieler A und B sollen den Drop Shot üben. Sie stehen so, wie im normalen Spiel das aufschlagende Team vor dem 3. Schlag steht. Spieler C und D stehen so, wie im normalen Spiel das rückschlagende Team nach dem Return steht – nämlich an der NVZ. Hier in der Übung starten C und D jede Rally. Zuerst spielt D einen langen Ball diagonal. A versucht einen Drop Shot. Anschließend rücken A und B nach vorn:
Je nachdem, wie gut der Drop Shot war, kommen sie gleich nach vorn oder rücken allmählich durch die Transition-Zone vor.
Das Spiel geht mit Rally-Scoring, also sowohl Team AB als auch Team CD können jederzeit Punkte machen. C und D starten abwechselnd die Rally. Ist also der erste Punkt ausgespielt, beginnt nun C:
Für C und D ist es etwas leichter, Punkte zu machen als für A und B. Darum gewinnen C und D mit 11 Punkten, während A und B schon mit 7 Punkten gewinnen.
Sind nur drei Spieler am Start, übt einer den Drop Shot. Er wechselt dabei jeweils die Seite. Die Gegner dürfen nur auf diese Seite spielen.
Bei zwei Spielern spielt man nur auf einer Hälfte des Platzes.
Das Spiel hat für beide Teams einen guten Übungseffekt, denn diese Schläge sind typischerweise die kritischen Schläge jedes Ballwechsels bei fortgeschrittenen Spielern – und man spielt/übt eben nur diese.
Durch das Scoring bleibt die Motivation hoch. Bei unseren bisherigen Drop Spielen hat sich gezeigt, dass nach relativ kurzer Zeit häufiger das Drop Team mit 7 Punkten gewinnt. Das ist aber auch ganz okay so, finde ich. Denn das gibt ja das Feedback, dass man es mit dem Drop Shot richtig macht.
Number Data type declaration with different length and performance impact
Update Partition table from another Partition table Performance issue
Gather STATS on Partitioned Table and Parallel for Partitioned Table
DR setup involving replicated database
Locate an Error in Wrapped PL/SQL
Posted by Pete On 18/03/24 At 01:00 PM
Getting started with Greenplum – 6 – Why Greenplum?
Now that we have the basic knowledge for operating a Greenplum cluster we should talk about why Greenplum can be option and what are the benefits of such a system. Again, here are the previous posts: Getting started with Greenplum – 1 – Installation, Getting started with Greenplum – 2 – Initializing and bringing up the cluster, Getting started with Greenplum – 3 – Behind the scenes, Getting started with Greenplum – 4 – Backup & Restore – databases, Getting started with Greenplum – 5 – Recovering from failed segment nodes.
First of all, Greenplum is for data analytics and data warehousing. It is not meant as an OLTP system. Greenplum also describes itself as an MPP system, which means “Massively Parallel Processing”. The idea behind that is quite simple: Use as many hosts as you can, all of them with their own CPUs, memory disks, and operating system, process the required work in parallel on those nodes, combine the results and give it back to the client. This is also known as a “shared nothing” architecture.
Coming back to the setup we’ve running now:
|-------------------|
|------6000|primary--------- |
| | Segment 1 | |
| 7000|mirror<------| | |
| |-------------------|
| | |
|-------------------| | |
| | | |
5432| Coordinator | | |
| | | |
|-------------------| | |
| | |
| |-------------------|
|------6000|primary ------ | |
| Segment 2 | |
7000|mirror<--------| |
|-------------------|
We have the coordinator node in front, this one receives all the client requests (the coordinator host can also be fault tolerant by adding a standby coordinator host). Work is then distributed to the amount of segment nodes you have available in the cluster. In my case this are just small VMs running on the same host so it will not give me any benefit performance wise. In a real setup all those nodes would run either on bare metal nodes or at least on different host nodes in a virtualized setup. The more segment nodes get added to the cluster, the more compute resources become available to be utilized. A critical part in such a setup is of course the networking. All the traffic goes through the network and the faster the network between the Greenplum nodes (which is called the interconnect) the better the whole cluster will perform.
Let’s assume we got all these building blocks right, do we need to consider more points to get most out of such a setup? Yes, we do.
In PostgreSQL all tables are heap organized. With Greenplum you can choose between heap oriented and append optimized tables. Heap oriented tables should be used when you expect frequent updates and deletes, append oriented tables should be used for initial load tables which only receive bulk inserts after loading.
The following statements create two simple tables, one heap organized, the other one append optimized, both distributed by id:
postgres=# create table t1 ( id int primary key
, dummy text
)
using heap
distributed by (id);
CREATE TABLE
postgres=# create table t2 ( id int primary key
, dummy text
)
using ao_row
distributed by (id);
CREATE TABLE
Populating both tables with the same amount of data and comparing the size of the tables give this:
postgres=# \timing
Timing is on.
postgres=# insert into t1 select i, i::text from generate_series(1,1000000) i;
INSERT 0 1000000
Time: 2639.981 ms (00:02.640)
postgres=# insert into t2 select i, i::text from generate_series(1,1000000) i;
INSERT 0 1000000
Time: 2878.901 ms (00:02.879)
postgres=# select pg_relation_size('t1');
pg_relation_size
------------------
44072960
(1 row)
Time: 1.394 ms
postgres=# select pg_relation_size('t2');
pg_relation_size
------------------
25226336
(1 row)
Time: 2.035 ms
The append optimized table is much smaller than the traditional heap organized table even without compression. The reason is that the tuple headers are much smaller for append optimized tables. This is also reason why they should not be used for frequent update and delete operations. Append only is only meant for bulk loading and bulk insert operations.
Another option you have if you go for append optimized tables is columnar storage but again, consider when you to use them: Columnar storage is read optimized and is not mean for write heavy operations. If you only access a small number of columns this can reduce the required I/O significantly. A table which is organized by columns is created like this:
postgres=# create table t4 ( id int primary key
, dummy text
)
using ao_row
distributed by (id);
CREATE TABLE
On top of all that you can chose to go for compression and partitioning, which gives you even more choice on how you want to layout your data. Connectors are something you might want to look as well.
Coming back to the initial question: Why Greenplum?
If you have the requirement for massive parallel data processing and you want to have a system which very much feels like a standard PostgreSQL for the clients, Greenplum is a valid option. As there is an open source edition give it a try and explore the possibilities. There is a lot of choice for various use cases and access patterns.
L’article Getting started with Greenplum – 6 – Why Greenplum? est apparu en premier sur dbi Blog.
FastAPI File Upload and Temporary Directory for Stateless API
If you think I’m geeky, you should meet my friend.
I’d like to talk about a very good friend of mine.
Whilst he’s much older than me ( 11 or 12 weeks at least), we do happen to share interests common to programmers of a certain vintage.
About a year ago, he became rather unwell.
Since then, whenever I’ve gone to visit, I’ve taken care to wear something that’s particular to our friendship and/or appropriately geeky.
At one point, when things were looking particularly dicey, I promised him, that whilst “Captain Scarlet” was already taken, if he came through he could pick any other colour he liked.
As a life-long Luton Town fan, his choice was somewhat inevitable.
So then, what follows – through the medium of Geeky T-shirts – is a portrait of my mate Simon The Indestructable Captain Orange…
When we first met, Windows 3.1 was still on everyone’s desktop and somewhat prone to hanging at inopportune moments. Therefore, we are fully aware of both the origins and continuing relevance of this particular pearl of wisdom :
Fortunately, none of the machines Simon was wired up to in the hospital seemed to be running any version of Windows so I thought he’d be reassured by this :
Whilst our first meeting did not take place on a World riding through space on the back of a Giant Turtle ( it was in fact, in Milton Keynes), Simon did earn my eternal gratitude by recommending the book Good Omens – which proved to be my gateway to Discworld.
The relevance of this next item of “Geek Chic” is that, when Simon later set up his own company, he decided that it should have a Latin motto.
In this, he was inspired by the crest of the Ankh-Morpork Assassins’ Guild :
His motto :
Nil codex sine Lucre
…which translates as …
No code without payment
From mottoes to something more akin to a mystic incantation, chanted whenever you’re faced with a seemingly intractable technical issue. Also, Simon likes this design so…
As we both know, there are 10 types of people – those who understand binary and those who don’t…
When confronted by something like this, I am able to recognise that the binary numbers are ASCII codes representing alphanumeric characters. However, I’ve got nothing on Simon, a one-time Assembler Programmer.
Whilst I’m mentally removing my shoes and socks in preparation to translate the message, desperately trying to remember the golden rule of binary maths ( don’t forget to carry the 1), he’ll just come straight out with the answer (“Geek”, in this case).
Saving the geekiest to last, I’m planning to dazzle with this on my next visit :
Techie nostalgia and a Star Wars reference all on the one t-shirt. I don’t think I can top that. Well, not for now anyway.