Skip to content

Instantly share code, notes, and snippets.

@ololobus

ololobus/1-gsoc-final-submission.md

Last active September 6, 2017 12:32
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ololobus/249432b663e29d111f36b12458e3f30a to your computer and use it in GitHub Desktop.
Save ololobus/249432b663e29d111f36b12458e3f30a to your computer and use it in GitHub Desktop.
Download ZIP
Final submission for a GSOC project 'Add errors handling and parallel execution to COPY'
Raw
1-gsoc-final-submission.md

Final submission for a PostgreSQL GSOC'17 project 'Add errors handling and parallel execution to COPY'

Alexey Kondratov (kondratov.aleksey@gmail.com).

Errors handling in COPY FROM

Details

In my initial proposal I was planning to use subtransactions for errors handling, since it is the only one completely safe way to catch all possible errors during the COPY FROM execution. However, it would cause a serious problem – severe transactional IDs (XIDs) consumption hidden from the end-user. It may lead to a huge performance drop in the case, when errors are too frequent in the input data.

Following multiple advises received in the pgsql-hackers mailing list thread I have focused more on my main motivation – handle only input data errors (e.g. extra or not enough data/columns, wrong type formating). This goal may be acheived without subtransactions.

Thus, in my current patch:

  • New option IGNORE_ERRORS added to COPY FROM syntax
  • Multiple validations rise WARNINGs insead of ERRORs, when errors handling is turned on
  • InputFunctionCall is wrapped with PG_TRY/PG_CATCH to safely catch input data type formatting errors

Usage example:

COPY test_table
FROM '/path/to/test.csv'
WITH (
    IGNORE_ERRORS,
    FORMAT csv
);

Results

Because of the PostgreSQL core development and release specifics no patch can be merged without passing detailed multi-step review process, including pgsql-hackers discussion, code-review, etc. However, all required steps toward the successful merging of this patch were made.

Parallel COPY FROM

Details

In order to understand are there any expensive calls in COPY, which can be executed in parallel, I did a small research. First, find flame graph of the most expensive copy.c calls during the 'COPY FROM file' attached (see 2-copy_from.svg). It reveals, that inevitably serial operations like CopyReadLine (<15%), heap_multi_insert (~15%) take less than 50% of time in summary, while remaining operations like heap_form_tuple and multiple checks inside NextCopyFrom probably can be executed well in parallel.

Second, I have compared an execution time of 'COPY FROM a single large file (~300 MB, 50000000 lines)' vs. 'COPY FROM four equal parts of the original file executed in the four parallel processes'. Though it is a very rough test, it helps to obtain an overall estimation:

Serial:

real 0m56.571s
user 0m0.005s
sys  0m0.006s

Parallel (x4):

real 0m22.542s
user 0m0.015s
sys  0m0.018s

Thus, it results in a ~60% performance boost per each x2 multiplication of parallel processes, which is consistent with the initial estimation. Taking into account all gathered information I have choosen a parallel architecture with one master process and multiple worker processes, so in my current implementation:

Master process does:

  • Dynamic shared memory allocation with parallel state across BGWorkers and master
  • Attaching every worker to the personal message query (shm_mq)
  • Wait workers initialization using Latch
  • Read raw text lines using CopyReadLine and puts them into shm_mq's via round-robin to balance queries load
  • When EOF is reached sends zero-length message and workers are safely shut down when receive it
  • Wait using Latch for worker until they complete their jobs

Each BGWorker does:

  • Signal master on initialization via Latch
  • Reinitialize db connection using the same db_id and user_id as main process
  • Receive raw text lines over the personal shm_mq, process them and put them into the heap
  • Signal master via Latch on job done

All parallel state modifications are done under LWLocks.

Real performance boost is slightly less than expected: generally between x1.2-1.4 (for two parallel workers compared to serial code) and up to x1.5 from time to time, though it is still notable improvement.

Results

Although parallel COPY FROM is working, there are a few things to be fixed and polished to finalize parallel code integration, since COPY is a core functionality in PostgreSQL and any architectural changes cause unpredictable crashes and problems. Anyway, I believe that the main goals of this project are achieved.

Display the source blob
Display the rendered blob
Raw
2-copy_from.svg
<?xml version="1.0" standalone="no"?>
<!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 1.1//EN" "http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd">
<svg version="1.1" width="1200" height="292" onload="init(evt)" viewBox="0 0 1200 292" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink">
<!-- Flame graph stack visualization. See https://github.com/brendangregg/FlameGraph for latest version, and http://www.brendangregg.com/flamegraphs.html for examples. -->
<defs >
<linearGradient id="background" y1="0" y2="1" x1="0" x2="0" >
<stop stop-color="#eeeeee" offset="5%" />
<stop stop-color="#eeeeb0" offset="95%" />
</linearGradient>
</defs>
<style type="text/css">
.func_g:hover { stroke:black; stroke-width:0.5; cursor:pointer; }
</style>
<script type="text/ecmascript">
<![CDATA[
var details, searchbtn, matchedtxt, svg;
function init(evt) {
details = document.getElementById("details").firstChild;
searchbtn = document.getElementById("search");
matchedtxt = document.getElementById("matched");
svg = document.getElementsByTagName("svg")[0];
searching = 0;
}
// mouse-over for info
function s(node) { // show
info = g_to_text(node);
details.nodeValue = "Function: " + info;
}
function c() { // clear
details.nodeValue = ' ';
}
// ctrl-F for search
window.addEventListener("keydown",function (e) {
if (e.keyCode === 114 || (e.ctrlKey && e.keyCode === 70)) {
e.preventDefault();
search_prompt();
}
})
// functions
function find_child(parent, name, attr) {
var children = parent.childNodes;
for (var i=0; i<children.length;i++) {
if (children[i].tagName == name)
return (attr != undefined) ? children[i].attributes[attr].value : children[i];
}
return;
}
function orig_save(e, attr, val) {
if (e.attributes["_orig_"+attr] != undefined) return;
if (e.attributes[attr] == undefined) return;
if (val == undefined) val = e.attributes[attr].value;
e.setAttribute("_orig_"+attr, val);
}
function orig_load(e, attr) {
if (e.attributes["_orig_"+attr] == undefined) return;
e.attributes[attr].value = e.attributes["_orig_"+attr].value;
e.removeAttribute("_orig_"+attr);
}
function g_to_text(e) {
var text = find_child(e, "title").firstChild.nodeValue;
return (text)
}
function g_to_func(e) {
var func = g_to_text(e);
// if there's any manipulation we want to do to the function
// name before it's searched, do it here before returning.
return (func);
}
function update_text(e) {
var r = find_child(e, "rect");
var t = find_child(e, "text");
var w = parseFloat(r.attributes["width"].value) -3;
var txt = find_child(e, "title").textContent.replace(/\([^(]*\)$/,"");
t.attributes["x"].value = parseFloat(r.attributes["x"].value) +3;
// Smaller than this size won't fit anything
if (w < 2*12*0.59) {
t.textContent = "";
return;
}
t.textContent = txt;
// Fit in full text width
if (/^ *$/.test(txt) || t.getSubStringLength(0, txt.length) < w)
return;
for (var x=txt.length-2; x>0; x--) {
if (t.getSubStringLength(0, x+2) <= w) {
t.textContent = txt.substring(0,x) + "..";
return;
}
}
t.textContent = "";
}
// zoom
function zoom_reset(e) {
if (e.attributes != undefined) {
orig_load(e, "x");
orig_load(e, "width");
}
if (e.childNodes == undefined) return;
for(var i=0, c=e.childNodes; i<c.length; i++) {
zoom_reset(c[i]);
}
}
function zoom_child(e, x, ratio) {
if (e.attributes != undefined) {
if (e.attributes["x"] != undefined) {
orig_save(e, "x");
e.attributes["x"].value = (parseFloat(e.attributes["x"].value) - x - 10) * ratio + 10;
if(e.tagName == "text") e.attributes["x"].value = find_child(e.parentNode, "rect", "x") + 3;
}
if (e.attributes["width"] != undefined) {
orig_save(e, "width");
e.attributes["width"].value = parseFloat(e.attributes["width"].value) * ratio;
}
}
if (e.childNodes == undefined) return;
for(var i=0, c=e.childNodes; i<c.length; i++) {
zoom_child(c[i], x-10, ratio);
}
}
function zoom_parent(e) {
if (e.attributes) {
if (e.attributes["x"] != undefined) {
orig_save(e, "x");
e.attributes["x"].value = 10;
}
if (e.attributes["width"] != undefined) {
orig_save(e, "width");
e.attributes["width"].value = parseInt(svg.width.baseVal.value) - (10*2);
}
}
if (e.childNodes == undefined) return;
for(var i=0, c=e.childNodes; i<c.length; i++) {
zoom_parent(c[i]);
}
}
function zoom(node) {
var attr = find_child(node, "rect").attributes;
var width = parseFloat(attr["width"].value);
var xmin = parseFloat(attr["x"].value);
var xmax = parseFloat(xmin + width);
var ymin = parseFloat(attr["y"].value);
var ratio = (svg.width.baseVal.value - 2*10) / width;
// XXX: Workaround for JavaScript float issues (fix me)
var fudge = 0.0001;
var unzoombtn = document.getElementById("unzoom");
unzoombtn.style["opacity"] = "1.0";
var el = document.getElementsByTagName("g");
for(var i=0;i<el.length;i++){
var e = el[i];
var a = find_child(e, "rect").attributes;
var ex = parseFloat(a["x"].value);
var ew = parseFloat(a["width"].value);
// Is it an ancestor
if (0 == 0) {
var upstack = parseFloat(a["y"].value) > ymin;
} else {
var upstack = parseFloat(a["y"].value) < ymin;
}
if (upstack) {
// Direct ancestor
if (ex <= xmin && (ex+ew+fudge) >= xmax) {
e.style["opacity"] = "0.5";
zoom_parent(e);
e.onclick = function(e){unzoom(); zoom(this);};
update_text(e);
}
// not in current path
else
e.style["display"] = "none";
}
// Children maybe
else {
// no common path
if (ex < xmin || ex + fudge >= xmax) {
e.style["display"] = "none";
}
else {
zoom_child(e, xmin, ratio);
e.onclick = function(e){zoom(this);};
update_text(e);
}
}
}
}
function unzoom() {
var unzoombtn = document.getElementById("unzoom");
unzoombtn.style["opacity"] = "0.0";
var el = document.getElementsByTagName("g");
for(i=0;i<el.length;i++) {
el[i].style["display"] = "block";
el[i].style["opacity"] = "1";
zoom_reset(el[i]);
update_text(el[i]);
}
}
// search
function reset_search() {
var el = document.getElementsByTagName("rect");
for (var i=0; i < el.length; i++) {
orig_load(el[i], "fill")
}
}
function search_prompt() {
if (!searching) {
var term = prompt("Enter a search term (regexp " +
"allowed, eg: ^ext4_)", "");
if (term != null) {
search(term)
}
} else {
reset_search();
searching = 0;
searchbtn.style["opacity"] = "0.1";
searchbtn.firstChild.nodeValue = "Search"
matchedtxt.style["opacity"] = "0.0";
matchedtxt.firstChild.nodeValue = ""
}
}
function search(term) {
var re = new RegExp(term);
var el = document.getElementsByTagName("g");
var matches = new Object();
var maxwidth = 0;
for (var i = 0; i < el.length; i++) {
var e = el[i];
if (e.attributes["class"].value != "func_g")
continue;
var func = g_to_func(e);
var rect = find_child(e, "rect");
if (rect == null) {
// the rect might be wrapped in an anchor
// if nameattr href is being used
if (rect = find_child(e, "a")) {
rect = find_child(r, "rect");
}
}
if (func == null || rect == null)
continue;
// Save max width. Only works as we have a root frame
var w = parseFloat(rect.attributes["width"].value);
if (w > maxwidth)
maxwidth = w;
if (func.match(re)) {
// highlight
var x = parseFloat(rect.attributes["x"].value);
orig_save(rect, "fill");
rect.attributes["fill"].value =
"rgb(230,0,230)";
// remember matches
if (matches[x] == undefined) {
matches[x] = w;
} else {
if (w > matches[x]) {
// overwrite with parent
matches[x] = w;
}
}
searching = 1;
}
}
if (!searching)
return;
searchbtn.style["opacity"] = "1.0";
searchbtn.firstChild.nodeValue = "Reset Search"
// calculate percent matched, excluding vertical overlap
var count = 0;
var lastx = -1;
var lastw = 0;
var keys = Array();
for (k in matches) {
if (matches.hasOwnProperty(k))
keys.push(k);
}
// sort the matched frames by their x location
// ascending, then width descending
keys.sort(function(a, b){
return a - b;
if (a < b || a > b)
return a - b;
return matches[b] - matches[a];
});
// Step through frames saving only the biggest bottom-up frames
// thanks to the sort order. This relies on the tree property
// where children are always smaller than their parents.
for (var k in keys) {
var x = parseFloat(keys[k]);
var w = matches[keys[k]];
if (x >= lastx + lastw) {
count += w;
lastx = x;
lastw = w;
}
}
// display matched percent
matchedtxt.style["opacity"] = "1.0";
pct = 100 * count / maxwidth;
if (pct == 100)
pct = "100"
else
pct = pct.toFixed(1)
matchedtxt.firstChild.nodeValue = "Matched: " + pct + "%";
}
function searchover(e) {
searchbtn.style["opacity"] = "1.0";
}
function searchout(e) {
if (searching) {
searchbtn.style["opacity"] = "1.0";
} else {
searchbtn.style["opacity"] = "0.1";
}
}
]]>
</script>
<rect x="0.0" y="0" width="1200.0" height="292.0" fill="url(#background)" />
<text text-anchor="middle" x="600.00" y="24" font-size="17" font-family="Verdana" fill="rgb(0,0,0)" >Flame Graph</text>
<text text-anchor="" x="10.00" y="275" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" id="details" > </text>
<text text-anchor="" x="10.00" y="24" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" id="unzoom" onclick="unzoom()" style="opacity:0.0;cursor:pointer" >Reset Zoom</text>
<text text-anchor="" x="1090.00" y="24" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" id="search" onmouseover="searchover()" onmouseout="searchout()" onclick="search_prompt()" style="opacity:0.1;cursor:pointer" >Search</text>
<text text-anchor="" x="1090.00" y="275" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" id="matched" > </text>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>heap_compute_data_size (1,316 samples, 6.99%)</title><rect x="1003.2" y="84" width="82.5" height="34.0" fill="rgb(254,177,12)" rx="2" ry="2" />
<text text-anchor="" x="1006.17" y="104" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >heap_comp..</text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>all (18,832 samples, 100%)</title><rect x="10.0" y="224" width="1180.0" height="34.0" fill="rgb(232,13,0)" rx="2" ry="2" />
<text text-anchor="" x="13.00" y="244" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" ></text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>CopyReadLineText (1,394 samples, 7.40%)</title><rect x="789.1" y="14" width="87.3" height="34.0" fill="rgb(241,182,53)" rx="2" ry="2" />
<text text-anchor="" x="792.10" y="34" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >CopyReadLi..</text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>CopyReadAttributesText (1,694 samples, 9.00%)</title><rect x="649.4" y="49" width="106.2" height="34.0" fill="rgb(209,28,27)" rx="2" ry="2" />
<text text-anchor="" x="652.43" y="69" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >CopyReadAttr..</text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>CopyReadLine (2,316 samples, 12.30%)</title><rect x="755.6" y="49" width="145.1" height="34.0" fill="rgb(220,14,45)" rx="2" ry="2" />
<text text-anchor="" x="758.59" y="69" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >CopyReadLine</text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>NextCopyFromRawFields (4,281 samples, 22.73%)</title><rect x="632.5" y="84" width="268.2" height="34.0" fill="rgb(217,59,48)" rx="2" ry="2" />
<text text-anchor="" x="635.46" y="104" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >NextCopyFromRawFields</text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>InputFunctionCall (1,670 samples, 8.87%)</title><rect x="527.8" y="84" width="104.7" height="34.0" fill="rgb(223,194,54)" rx="2" ry="2" />
<text text-anchor="" x="530.79" y="104" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >InputFunctio..</text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>DoCopy (18,832 samples, 100.00%)</title><rect x="10.0" y="189" width="1180.0" height="34.0" fill="rgb(215,222,46)" rx="2" ry="2" />
<text text-anchor="" x="13.00" y="209" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >DoCopy</text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>heap_form_tuple (4,017 samples, 21.33%)</title><rect x="935.9" y="119" width="251.7" height="34.0" fill="rgb(206,213,14)" rx="2" ry="2" />
<text text-anchor="" x="938.93" y="139" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >heap_form_tuple</text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>heap_multi_insert (2,979 samples, 15.82%)</title><rect x="58.5" y="84" width="186.7" height="34.0" fill="rgb(242,149,24)" rx="2" ry="2" />
<text text-anchor="" x="61.48" y="104" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >heap_multi_insert</text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>CopyFromInsertBatch (2,983 samples, 15.84%)</title><rect x="58.3" y="119" width="186.9" height="34.0" fill="rgb(214,199,26)" rx="2" ry="2" />
<text text-anchor="" x="61.25" y="139" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >CopyFromInsertBatch</text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>CopyFrom (18,832 samples, 100.00%)</title><rect x="10.0" y="154" width="1180.0" height="34.0" fill="rgb(211,181,34)" rx="2" ry="2" />
<text text-anchor="" x="13.00" y="174" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >CopyFrom</text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>MemoryContextReset (324 samples, 1.72%)</title><rect x="273.1" y="119" width="20.3" height="34.0" fill="rgb(223,187,35)" rx="2" ry="2" />
<text text-anchor="" x="276.07" y="139" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" ></text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>ExecStoreTuple (399 samples, 2.12%)</title><rect x="248.1" y="119" width="25.0" height="34.0" fill="rgb(211,90,9)" rx="2" ry="2" />
<text text-anchor="" x="251.07" y="139" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >E..</text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>RelationPutHeapTuple (728 samples, 3.87%)</title><rect x="147.9" y="49" width="45.6" height="34.0" fill="rgb(211,163,53)" rx="2" ry="2" />
<text text-anchor="" x="150.88" y="69" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >Rela..</text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>heap_fill_tuple (1,349 samples, 7.16%)</title><rect x="1085.7" y="84" width="84.5" height="34.0" fill="rgb(218,99,32)" rx="2" ry="2" />
<text text-anchor="" x="1088.65" y="104" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >heap_fill..</text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>int4in (452 samples, 2.40%)</title><rect x="604.1" y="49" width="28.4" height="34.0" fill="rgb(213,64,51)" rx="2" ry="2" />
<text text-anchor="" x="607.14" y="69" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >i..</text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>heap_prepare_insert (636 samples, 3.38%)</title><rect x="204.1" y="49" width="39.9" height="34.0" fill="rgb(252,72,52)" rx="2" ry="2" />
<text text-anchor="" x="207.11" y="69" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >hea..</text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>MemoryContextResetOnly (320 samples, 1.70%)</title><rect x="273.3" y="84" width="20.1" height="34.0" fill="rgb(213,227,54)" rx="2" ry="2" />
<text text-anchor="" x="276.32" y="104" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" ></text>
</g>
<g class="func_g" onmouseover="s(this)" onmouseout="c()" onclick="zoom(this)">
<title>NextCopyFrom (10,057 samples, 53.40%)</title><rect x="305.8" y="119" width="630.1" height="34.0" fill="rgb(206,183,1)" rx="2" ry="2" />
<text text-anchor="" x="308.80" y="139" font-size="12" font-family="Verdana" fill="rgb(0,0,0)" >NextCopyFrom</text>
</g>
</svg>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment