begriffs

2018-02-13 22:10:15.407 CST [26887] LOG:  server process (PID 27923) was terminated by signal 11: Segmentation fault
2018-02-13 22:10:15.407 CST [26887] DETAIL:  Failed process was running: select '1/3'::rational;
2018-02-13 22:10:15.407 CST [26887] LOG:  terminating any other active server processes
2018-02-13 22:10:15.408 CST [27416] WARNING:  terminating connection because of crash of another server process
2018-02-13 22:10:15.408 CST [27416] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2018-02-13 22:10:15.408 CST [27416] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2018-02-13 22:10:15.410 CST [27936] FATAL:  the database system is in recovery mode
2018-02-13 22:10:15.411 CST [26887] LOG:  all server processes terminated; reinitializing
2018-02-13 22:10:15.434 CST [27937] LOG:  database system was interrupted; last 

begriffs

Is this a fair benchmark?

• I'm comparing the speed of inserting UUIDs into columns which are indexed with btree vs hash
• Want to ensure that I'm measuring both insert queries at their best performance
• Profiled each of them to check whether there is interference by server misconfiguration

Flame graph results

Both were sampled with perf record -F 99 -u postgres -g --call-graph dwarf -- sleep 180

begriffs

Note:

 max_wal_size
--------------
 1GB

 checkpoint_timeout
--------------------
 5min

begriffs

Benchmark of UUID insert speed for btree vs hash index

AWS instance

• ami-aa2ea6d0
  • Ubuntu Server 16.04 LTS (HVM),EBS General Purpose (SSD) Volume Type
  • c3.large
  • vCPU: 2
  • RAM GiB: 3.75
  • Disk GB: 2 x 16 (SSD)

begriffs

import           Control.Concurrent.MVar
import qualified Data.Map as M
import           Network.HTTP.Types.Status
import           Web.Scotty

import           Protolude hiding (get)

main :: IO ()
main = do
  dbSync <- newMVar M.empty

begriffs

* Hypothesis: takes two builds to trigger css rebuild
  - experiment: push css change commit, wait for build, push another, wait for build, see if fixed
  - result: did not fix the problem
  - observation: the hypothesis was contradicted by stable working

* Hypothesis: css is not rebuilt by the server at all
  - experiment: curl all css files generated before and after change and compare them
    - curl -v https://docs.citusdata.com/en/v7.0/_static/css/theme.css > theme-7-3c64d3cf.css
      * TLS 1.2 connection using TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256
      * Server certificate: citusdata.com

begriffs

-- Be quiet while setting these values
\set QUIET 1

-- Show time of all queries
-- \timing

-- Colors appear entirely broken with command-completion. Leaving commented for now.
/*\set PROMPT1 '%[%033[0;31m%]%n%[%033[0;37m%]@%[%033[0;33m%]%m %[%033[0;34m%]%~ %[%033[1;31%# m%]❯%[%033[1;33m%]❯%[%033[1;32m%]❯ %033[0m'*/
\set PROMPT1 '%n@%m %~ ❯❯❯ '
\set PROMPT2 '%R> '

begriffs

CREATE TYPE prio AS ENUM ('low', 'med', 'high');
SELECT run_command_on_workers ($cmd$
  CREATE TYPE prio AS ENUM ('low', 'med', 'high');
$cmd$);

CREATE TABLE todos (
    id serial,
    project_id int,
    priority prio DEFAULT 'med',
    what text NOT NULL,

begriffs

# Launch new server
#	Ubuntu Server 16.04 LTS (HVM), SSD Volume Type - ami-2afbde4a
#	all the default configuration settings

echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | \
     sudo tee /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
     sudo apt-key add -
sudo apt-get update

begriffs

constraints: apply-refact ==0.3.0.1,
             base ==4.9.0.0,
             ghc-prim ==0.5.0.0,
             rts ==1.0,
             integer-gmp ==1.0.0.1,
             containers ==0.5.7.1,
             array ==0.5.1.1,
             deepseq ==1.4.2.0,
             directory ==1.2.6.2,
             filepath ==1.4.1.0,