From 29c52d98bf7feb132a1857cce2058ec7134e4b0f Mon Sep 17 00:00:00 2001 From: Simeon Simeonov Date: Wed, 26 Jan 2022 18:46:50 +0100 Subject: Add postgresql_tuning.html python.html timetravel.html and update sqlalchemy.html and demo.html --- reveal.js/demo.html | 62 +++--- reveal.js/postgresql_tuning.html | 152 ++++++++++++++ reveal.js/python.html | 432 +++++++++++++++++++++++++++++++++++++++ reveal.js/sqlalchemy.html | 5 - reveal.js/timetravel.html | 127 ++++++++++++ 5 files changed, 741 insertions(+), 37 deletions(-) create mode 100644 reveal.js/postgresql_tuning.html create mode 100644 reveal.js/python.html create mode 100644 reveal.js/timetravel.html diff --git a/reveal.js/demo.html b/reveal.js/demo.html index 71cac98..ea42adf 100644 --- a/reveal.js/demo.html +++ b/reveal.js/demo.html @@ -19,7 +19,7 @@ - + @@ -186,7 +186,7 @@ Write content using inline or external Markdown. Instructions and more info available in the [docs](https://revealjs.com/markdown/). - ```[] + ```html []
## Markdown support @@ -249,17 +249,17 @@

reveal.js comes with a few themes built in:
- Black (default) - - White - - League - - Sky - - Beige - - Simple
- Serif - - Blood - - Night - - Moon - - Solarized + Black (default) - + White - + League - + Sky - + Beige - + Simple
+ Serif - + Blood - + Night - + Moon - + Solarized

@@ -281,7 +281,7 @@

Tiled Backgrounds

<section data-background="image.png" data-background-repeat="repeat" data-background-size="100px">
-
+

Video Backgrounds

<section data-background-video="video.mp4,video.webm">
@@ -451,25 +451,23 @@ Reveal.on( 'customevent', function() {
- - - - - diff --git a/reveal.js/postgresql_tuning.html b/reveal.js/postgresql_tuning.html new file mode 100644 index 0000000..d7e3466 --- /dev/null +++ b/reveal.js/postgresql_tuning.html @@ -0,0 +1,152 @@ + + + + + SQLAlchemy + + + + + + + + + + + + + + + +
+ + +
+ +
+

PostgreSQL tuning

+

Data Science @ Beryl

+
+

Simeon Simeonov

+
+ +
+ +
+

Agenda

+
+
    +
  • Generic tools for gathering information
  • +
  • Memory settings
  • +
  • Logging and performance reports
  • +
  • Other tools for analysis
  • +
+
+ +
+ +
+

General tools for gathering information

+
+
+            
+              # shell
+              # fetch information from the OS
+              cat /proc/cpuinfo
+              cat /proc/meminfo
+              sysctl -a | grep shm  # get kernel parameters of interest
+            
+            
+              -- SQL
+              -- show the current values of all settings
+              SHOW ALL;
+
+              -- display even more than all...
+              SELECT * FROM pg_settings;
+
+              -- opening postgresql.conf and reading the comments - the old school approach
+            
+            
+              # old school: edit postgresql.conf and read the comments
+            
+          
+
+ +
+

Memory settings

+
+
    +
  • shared_buffers - how much memory is dedicated to PostgreSQL to use for caching data - for a system with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the system memory (128MB -> 1GB)
  • +
  • effective_cache_size - how much memory we expect to be available in the OS and PostgreSQL buffer caches, not an allocation - used only by the PostgreSQL query planner to figure out whether plans it's considering would be expected to fit in RAM or not - 1/2 of total memory would be a normal conservative setting (4GB -> 8GB)
  • +
  • work_mem - the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files - for a complex query, several sort or hash operations might be running in parallel; each operation will generally be allowed to use as much memory as this value specifies (4MB -> 8MB)
  • +
  • maintenance_work_mem - the maximum amount of memory to be used by maintenance operations, such as VACUUM and CREATE INDEX. It's safe to set this value significantly larger than work_mem (64MB -> 256MB)
  • +
+
+ +
+

Logging and performance reports

+
+

pgBadger - a fast PostgreSQL log analysis report can be used for general analysis.

+
    +
  • log_checkpoints - checkpoints and restartpoints are logged in the server log. Some statistics are included in the log messages, including the number of buffers written and the time spent writing them
  • +
  • log_connections - each attempted connection to the server to be logged, as well as successful completion of client authentication
  • +
  • log_disconnections - provides information similar to log_connections, plus the duration of the session
  • +
  • log_line_prefix - set to '%t [%p]: user=%u,db=%d,app=%a,client=%h '
  • +
  • log_lock_waits - log message is produced when a session waits longer than deadlock_timeout to acquire a lock. This is useful in determining if lock waits are causing poor performance
  • +
  • log_temp_files - when set to 0, a log entry is emitted for each temporary file when it is deleted
  • +
  • log_autovacuum_min_duration - set to 0 it logs all autovacuum actions
  • +
+
+ +
+

Other tools for analysis

+
+
    +
  • ANALYZE - collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.
  • +
  • VACUUM - reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. (VACUUM vs. VACUUM FULL)
  • +
+
+ +
+

Sources

+
+

+ https://www.postgresql.org/docs/ - The official documentation +

+

+ https://wiki.postgresql.org - The official Wiki +

+
+ +
+

Q & A

+
+ +
+
+ + + + + + + + + + + diff --git a/reveal.js/python.html b/reveal.js/python.html new file mode 100644 index 0000000..5190dfb --- /dev/null +++ b/reveal.js/python.html @@ -0,0 +1,432 @@ + + + + + Introduction to Python + + + + + + + + + + + + + + + +
+ + +
+ +
+

Introduction to Python

+

Data Engineering @ Statnett

+
+

Simeon Simeonov

+
+ +
+

Goals

+
+
    +
  • Present the Python programming language in a different way than https://docs.python.org

  • +
  • Avoid information overload

  • +
  • Use examples and interaction rather than documents and slides

  • +
+
+ +
+

Preliminary plan

+
+
    +
  • Basics: About the language, the Python eco-system, types, scopes, modules and functions

  • +
  • Control flow: if / for / while / try, iterators, decorators, "tactical programming" tips

  • +
  • Object orientation: How Python "really works"

  • +
  • A brief tour through Python's standard library

  • +
  • Code design and best practices: How to design your code

  • +
+
+ +
+

What is Python?

+
+
    +
  • Python is an interpreted high-level general-purpose programming language - advanced through the Python Enhancement Proposal (PEP) process

  • +
  • CPython is the reference implementation of Python, written in C (alternatives: pypy, jython)

  • +
  • python - interpreter and interpreter shell (alternatives: ipython, bpython)

  • +
  • libpython

  • +
  • Calling C from Python: Cython, CFFI, ctypes

  • +
+
+ +
+

Philosophy

+
+
+            
+              $ python
+            
+          
+
+            
+              import this
+
+              # The Zen of Python, by Tim Peters
+
+              # Beautiful is better than ugly.
+              # Explicit is better than implicit.
+              # Simple is better than complex.
+              # Complex is better than complicated.
+              # Flat is better than nested.
+              # Sparse is better than dense.
+              # Readability counts.
+              # Special cases aren't special enough to break the rules.
+              # Although practicality beats purity.
+              # Errors should never pass silently.
+              # Unless explicitly silenced.
+              # In the face of ambiguity, refuse the temptation to guess.
+              # There should be one-- and preferably only one --obvious way to do it.
+              # Although that way may not be obvious at first unless you're Dutch.
+              # Now is better than never.
+              # Although never is often better than *right* now.
+              # If the implementation is hard to explain, it's a bad idea.
+              # If the implementation is easy to explain, it may be a good idea.
+              # Namespaces are one honking great idea -- let's do more of those!
+            
+          
+
+ +
+

Built-in functions

+
+

Few built-in functions.

+

https://docs.python.org/3/library/functions.html

+
    +
  • dir([obj]) - returns a list of valid attributes for that object
  • +
  • id(obj) - returns the "identity" of an object - an integer which is guaranteed to be unique
  • +
  • print(...) - prints objects to a text stream
  • +
  • str(...) - returns a string version of object
  • +
  • type(obj) - returns the type of an object
  • +
+
+ +
+

Common built-in types

+
+

Python uses duck typing and has typed objects but untyped variable names.

+

Type constraints are not checked at compile time; rather, operations on an object may fail, signifying that the given object is not of a suitable type. Despite being dynamically-typed, Python is strongly-typed, forbidding operations that are not well-defined (for example, adding a number to a string) rather than silently attempting to make sense of them.

+
+            
+              s = 'foo'  # this is a string / str, same as str('foo'), may be encoded, immutable
+              b = b'foo'  # bytes, same as bytes('foo', 'utf-8'), may be decoded, immutable
+              i = 6  # int, same as int('6'), immutable
+              f = 0.1  # float, same as float('0.1'), immutable
+              b = False  # bool, same as bool(0), bool(''), bool(None)... immutable / constant
+              n = None  # NoneType, similar to 'null' in other languages, immutable / constant
+              l = [1, False, 'foo']  # list, same as list((1, False, 'foo'))
+              t = (1, False, 'foo')  # tuple, same as tuple([1, False, 'foo']), immutable
+              d = {'foo': 1, 'bar': 8}  # dict, same as dict(foo=1, bar=8), similar to hash in other languages
+              s = {'foo', 'bar', 1, 1, 4}  # set, same as set(['foo', 'bar', 1, 1, 4]), removes duplicates
+            
+          
+

Classes, functions, modules, .... and even types are simply other types :)

+
+ +
+

Modules

+
+

A module is a file containing Python definitions and statements. The file name is the module name with the suffix .py appended. Within a module, the module's name (as a string) is available as the value of the global variable __name__

+

When a module named foo is imported, the interpreter first searches for a built-in module with that name (sys.builtin_module_names). If not found, it then searches for a file named foo.py in a list of directories given by the variable sys.path. sys.path is initialized from these locations:

+
    +
  • the directory containing the input script (or the current directory when no file is specified)
  • +
  • PYTHONPATH - env. variable - a list of directory names
  • +
  • the installation-dependent default locations
  • +
+ +

The module is then imported only once and "cached" in sys.modules

+
+ +
+

Packages

+
+

Packages are a way of structuring Python's module namespace by using "dotted module names"

+

The import statement combines two operations:

+
    +
  • it searches for the named module
  • +
  • it binds the results of that search to a name in the local scope
  • +
+
+            
+              # bar.py then bar/__init__.py will be considered, the first match executed and bound to 'bar'
+              import bar
+
+              import mymodule.foo  # implicitly executes mymodule.py, mymodule/__init__.py and mymodule/foo/__init__.py
+
+              import numpy as np  # will be bound as 'np' instead of 'numpy'. N.B. __name__ is still 'numpy'
+
+              import some.extremely.deep.path.Animal as Animal  # "sacrifice" the namespace in the name of convinience
+
+              from sys import path  # execute sys and only import the 'path' attribute into local scope as 'path'
+
+              # relative imports must be explicit in Python 3
+              from .othermodule import something  # expects that current module and 'othermodule' are in the same
+                                                  # package (containing __init__.py)
+
+              from sys import *  # NO! Bad programming practice since 1879
+            
+          
+
+ +
+

Creating and maintaining a Python environment

+
+

Python's official package repository is PyPi (https://pypi.org), while Python's official package installer is pip (https://pypi.org/project/pip/)

+

A Python environment is the physical and logical arrangement of Python modules and packages. Several options exist:

+
    +
  • using a proper operating system :) (symlinks, real commercial support etc.)
  • +
  • using venv
  • +
  • using higher level tools like poetry
  • +
  • using a mixture / cocktail of all of the above :)
  • +
+
+ +
+

Creating and maintaining a Python environment (cont...)

+
+

Desired qualities for a flexible Python environment:

+
    +
  • easy to create and (un)load
  • +
  • do not require extra privileges
  • +
  • don't repeat yourself (DRY)
  • +
  • easy to update without breaking the API
  • +
  • easy to debug
  • +
  • play nicely with the VCS (git)
  • +
+
+ +
+

Creating and maintaining a Python environment (cont...)

+
+

Exploting the operating system can be done by:

+
    +
  • (re)defining PYTHONPATH
  • +
  • using symlinks to point at packages placed at different locations
  • +
+ +
+

Creating and maintaining a Python environment (cont...)

+
+

Using venv can be done by directly invoking python:

+
+            
+              # create a virtual environment
+              python -m venv my_virtual_env
+              python -m venv --system-site-packages my_virtual_env
+
+              # load, use and unload the virtual environment
+              source my_virtual_env/bin/activate
+              pip install sqlalchemy
+              deactivate
+
+              # one can alternatively use the python "wrapper" of the virtual env
+              my_virtual_env/bin/python -m pip install sqlalchemy
+            
+          
+

--system-site-packages will keep the original site-packages folders at the end of sys.path

+
+ +
+

Creating and maintaining a Python environment (cont...)

+
+

Poetry (https://python-poetry.org) is the prefered environment and dependency management tool at Statnett.

+
+            
+              # create project and a virtual environment from scratch
+              poetry new my-project
+
+              # ... or use Poetry with an existing one
+              cd my-project
+              poetry init
+
+              # edit pyproject.toml for your needs (f.i. add dependencies, metadata ... etc),
+              # create virtual environment and install dependencies
+              poetry install
+              # finally commit your poetry.lock file to version control
+
+              # update all dependencies
+              poetry update
+            
+          
+

For more info: https://python-poetry.org/docs/basic-usage/

+
+ +
+

Mutables vs. immutables

+

Immutable object is an object with a fixed value. Immutable objects include bool, int, float, str, bytes and tuples. Such an object cannot be altered. A new object has to be created if a different value has to be stored. They play an important role in places where a constant hash value is needed, for example as a key in a dictionary.

+

All objects that are not immutable are... mutable. All hashable objects should be immutable or use id().

+
+            
+              i = 1
+              id(i)  # returns f.i. 9788992
+              i += 1  # same as i = i + 1
+              id(i)  # returns a different value, hence - a brand new object
+
+              s = 'Hello'
+              s += ' World'  # s is now a different object
+              s[0]  # 'H'
+              s[0] = 'h'  # TypeError: 'str' object does not support item assignment
+
+              t = (1, 4)  # tuple
+              l = [1, 4]  # list
+              hash(t)  # returns f.i. -6333845781340707986
+              hash(l)  # TypeError: unhashable type: 'list'
+
+              s = 'the long and winding road'
+              s2 = 'the long and winding road'
+
+              # check if s and s2 are the same object:
+              id(s)  # Out: 139858905258704
+              id(s2)  # Out: 139858926037472
+
+              # the hash should be the same
+              hash(s)  # Out: 7030216208569256362
+              hash(s2)  # Out: 7030216208569256362
+            
+          
+
+ +
+

Functions

+
+

A function is a sequence of program instructions that performs a specific task, packaged as a unit.

+

Functions let you:

+
    +
  • reuse code across several programs / projects
  • +
  • minimize code duplication
  • +
  • devide larger programming tasks
  • +
  • hide implementation details
  • +
  • improve readability
  • +
  • improve traceability
  • +
+

Function calls bring some overhead pushing / popping function-data into / from stack.

+

Important definitions (may have different meanings in different programming languages):

+
    +
  • parameter / formal parameter - variable / data provided as input to the function
  • +
  • argument / actual parameter - local variable / data to the given function
  • +
+

The keyword def introduces a function definition.

+
+ +
+

Functions (cont...)

+
+
+            
+              def add(a, b):  # - function definition / header
+                  """Function for adding integers"""  # - docstring
+                  result = a + b
+                  a = 5
+                  return result  # - function that does not contain return, implicitly returns None
+
+              a_param = 9
+              b_param = -2
+              add(a_param, b_param)  # Out: 7
+
+              # integers are immutable and a_param will remain unchanged
+              print(a_param)  # Out: 9
+
+
+              def addl(a, b):
+                  """Function for adding two lists"""
+                  result = a + b
+                  a += [5]
+                  return result
+
+              a_param = [9]
+              b_param = [-2]
+              addl(a_param, b_param)  # Out: [9, -2]
+              # lists are mutable and a_param will be changed
+              print(a_param)  # Out: [5]
+            
+          
+
+ +
+

Functions

+
+

To be continued in the next episode....

+
+ +
+

Scopes in Python

+
+
    +
  • local - assigned names are local unless declared global
  • +
  • enclosed - the scope of the variable inside a function with a nested function
  • +
  • global - global for the current module
  • +
  • built-in
  • +
+

locals() and globals() return dicts of symbols for their respective scopes

+
+            
+              num1, num2 = 7, 8  # module globals
+
+              def print_numbers():
+                  print(num1, num2)  # OK, these are module globals
+                  num3 = 100
+                  print(num3)  # prints 100, num3 is in the function (local) scope
+                  global num4  # assignes / references num4 to / in the global scope
+                  num4 = 99
+                  id = 200  # new symbol in local scope
+                  # id(num4)  # will not yield the expected result (raises TypeError)
+
+                  def print_numbers2():
+                      print(num3)  # OK, enclosed scope
+
+                  print_numbers2()  # prints 100
+
+              print_numbers()
+              # print(num3)  # Raises NameError - why?
+              print(num4)  # Prints 99 - why?
+              # print_numbers2()  # Raises NameError
+            
+          
+
+ +
+

Q & A

+
+ +
+
+ + + + + + + + + + + diff --git a/reveal.js/sqlalchemy.html b/reveal.js/sqlalchemy.html index 6acd9b7..96f83e7 100755 --- a/reveal.js/sqlalchemy.html +++ b/reveal.js/sqlalchemy.html @@ -62,7 +62,6 @@
  • object-orientation - you work with objects instead of tables and rows

  • performance - exploits the likehood of reusing a particular query

  • flexibility - you can override almost anything

  • -
  • 0.0125 karma points for each MB transferred (2021)

  • @@ -302,10 +301,6 @@
    -
    -

    ... there is more???

    -
    -

    Some nice features

    diff --git a/reveal.js/timetravel.html b/reveal.js/timetravel.html
    new file mode 100644
    index 0000000..e073f92
    --- /dev/null
    +++ b/reveal.js/timetravel.html
    @@ -0,0 +1,127 @@
    +
    +
    +  
    +    
    +    Solving problems using time travel
    +    
    +    
    +    
    +    
    +
    +    
    +    
    +
    +    
    +
    +    
    +    
    +    
    +  
    +  
    +    
    + + +
    + +
    +

    Solving problems using time travel

    +

    Beryl @ Fifty

    +
    +

    Tomas Robertson (team ACE-OL) & Simeon Simeonov (team Forecasts)

    +
    + + +
    +

    What is time travel???

    +
    +

    Time travel - our ability to look at our input data at the state it was at a specific point in time (not only at its last state).

    +

    Time travel is achieved by adding the record_created_time column to our DB tables and storing Kafka's created_time value (converted to UTC).

    +
    +

    The main concepts around how and why were presented by Peter Sandberg.

    +
    + +
    +

    Solving problems

    +
    +

    The following spike was observed 2021-10-06 around 10:15 CET @ NO4

    + +
    + +
    +

    Solving problems (cont)

    +
    +

    No spikes shown in Grafana

    + +
    + +
    +

    Solving problems with time travel

    +
    +
    +            
    +              SELECT record_created_time, start_time, value
    +              FROM misc.app_odin_ace_ol_ba_10s_avro_v01
    +              WHERE bidding_area_name = 'NO4' AND start_time = '2021-10-06 08:14:10'
    +              ORDER BY record_created_time;
    +            
    +          
    + + + + + + + + + + + + + + + + + + + + + + + + + + +
    record_created_timestart_timevalue
    2021-10-06 08:15:52.2582021-10-06 08:14:10-370.37683609008127
    2021-10-06 08:19:06.2222021-10-06 08:14:1025.183745117193457
    2021-10-06 08:21:56.2072021-10-06 08:14:1034.78731922151518
    +
    + +
    +

    Q & A

    +
    + +
    +
    + + + + + + + + + + + -- cgit v1.3