pgsysinfo

#!/usr/bin/env python3
"""
sysinfo
Testing harness to make sure machine info etc. parse correctly.

Copyright (c) 2009-2020, Gregory Smith
"""

import os
import platform
import re
import sys
from subprocess import Popen, PIPE, STDOUT

# dateutil not really used.  It's just here so this script will catch
# that it's missing before users run a bad set.
import dateutil.parser

# Windows specific routines
try:
    # ctypes is only available starting in Python 2.5
    from ctypes import *
    # wintypes is only is available on Windows
    from ctypes.wintypes import *

    def Win32Memory():
        class memoryInfo(Structure):
            _fields_ = [
              ('dwLength', c_ulong),
              ('dwMemoryLoad', c_ulong),
              ('dwTotalPhys', c_ulong),
              ('dwAvailPhys', c_ulong),
              ('dwTotalPageFile', c_ulong),
              ('dwAvailPageFile', c_ulong),
              ('dwTotalVirtual', c_ulong),
              ('dwAvailVirtual', c_ulong)
              ]
        
        mi = memoryInfo()
        mi.dwLength = sizeof(memoryInfo)
        windll.kernel32.GlobalMemoryStatus(byref(mi))
        return mi.dwTotalPhys

except:
    # TODO For pre-2.5, and possibly replacing the above in all cases, you
    # can grab this from the registry via _winreg (standard as of 2.0) looking
    # at "HARDWARE\RESOURCEMAP\System Resources\Physical Memory"
    # see http://groups.google.com/groups?hl=en&lr=&client=firefox-a&threadm=b%25B_8.3255%24Dj6.2964%40nwrddc04.gnilink.net&rnum=2&prev=/groups%3Fhl%3Den%26lr%3D%26client%3Dfirefox-a%26q%3DHARDWARE%255CRESOURCEMAP%255CSystem%2BResources%255CPhysical%2BMemory%26btnG%3DSearch
    pass

def spawn_read(cmd):
    """
    Basic wrapper to spawn programs like psql and consume a line of output.
    If there's an error, None is returned.
    """

    from os import path
    from shutil import which

    wn=which("psql")
    if not wn is None:
        psqlbin=path.join(wn,cmd)
    else:
        psqlbin=cmd

    if (False):
        print ("psqlbin=",psqlbin)
        print("Command line ",wn)

    p = Popen(cmd, shell=True, stdin=PIPE, stdout=PIPE, stderr=STDOUT,
              close_fds=True, encoding='utf-8')

    out=p.communicate()[0].rstrip()
    exit_code = p.returncode
    
    if exit_code==127:
        print("Error 127:  your psql is not in the path where this Python script can find it")
        sys.exit(exit_code)
    
    if exit_code > 0:
        print("exit code is '%s'" % exit_code)
        print("bad command:  '%s'" % cmd)
        return None

    return out

def pg_version():
    """
    Try connecting to the database to find out its version.
    If that fails try checking the version of psql.

    Prefer querying because it give a nice clean version number,
    and it can ask a remote database server--assuming the right PG*
    enviroment variables.
    """

    # TODO When bindings like psycopg2 are around, use them for this query
    v=spawn_read('psql -Atc "show server_version"')
    if v is None:
        v=spawn_read('psql --version')
    return v

def check_config(guc):
    """
    Lookup a GUC value and return the value
    """

    # TODO When bindings like psycopg2 are around, use them for this query
    v=spawn_read('psql -Atc "SELECT current_setting(\'%s\')"' % guc)
    return v

def total_mem():
    """
    Determine total memory on Windows, Mac OS Darwin, and UNIX-ish systems
    """
    try:
        if platform.system() == "Windows":
            mem = Win32Memory()
        elif platform.system() == "Darwin":
            # One ugly way to find the amount of RAM on OS X, first tested
            # on 10.6 and stll working on 10.15
            output = spawn_read('sysctl hw.memsize')
            m = re.match(r'^hw.memsize[:=]\s*(\d+)$', output.strip())
            if m and m.groups():
                mem = int(m.groups()[0])
        else:
            # Should work on Linux and other UNIX-ish platforms
            physPages = os.sysconf("SC_PHYS_PAGES")
            pageSize = os.sysconf("SC_PAGE_SIZE")
            mem = physPages * pageSize
        return mem
    except:
        return None

def arch_bits(arch):
    """
    Decode the usual strings for processor architecture, i386 and x86_64,
    into how many bits the platform has.  Iff the input value is not one
    of those, make a guess based on Python's maximum pointer size.
    >>> arch_bits('i386')
    32
    >>> arch_bits('x86_64')
    64
    """
    if arch=='i386':
        return 32
    if arch=='x86_64':
        return 64
    if sys.maxsize > 2**32:
        return 64
    else:
        return 32

def cpu_count():
    """
    Estimate CPU count from various probes
    """
    try:
        # Prefer online processor count sysconf if that's available.
        # Of course (sigh) there are two ways the parameter is commonly spelled.
        if hasattr(os, 'sysconf') and 'SC_NPROCESSORS_ONLN' in os.sysconf_names:
            return int(os.sysconf('SC_NPROCESSORS_ONLN'))
        if hasattr(os, 'sysconf') and '_SC_NPROCESSORS_ONLN' in os.sysconf_names:
            return int(os.sysconf('_SC_NPROCESSORS_ONLN'))

        # TODO Does the above work on FreeBSD, or should we spawn `sysctl hw.ncpu`?

        try:
            # All but ancient <2.6 Python have multiprocessing
            import multiprocessing
            return multiprocessing.cpu_count()
        except:
            # Windows may (should?) have 'NUMBER_OF_PROCESSORS
            #  environment variable
            if 'NUMBER_OF_PROCESSORS' in os.environ:
                return int(os.environ['NUMBER_OF_PROCESSORS'])
    except Exception as e:
        print("Exception detecting CPU count: %s", e)
    return None

def version_parse(version):
    """
    Parse a PostgreSQL version number text into a float with one fractional digit.
    This supports any mix of ".-_" characters as delimiters.
    Typical input will take "V12_5" and return the number 12.5
    Any PG version over 50 is assumed to be junk.
    That avoids problems like "100" turning into 100.0 when it should be 10.0
    Multi-word input is fine, it takes the first word that seems like
    a version number--either starting with a digit or "V".
    """
    if version is None:  return None
    max_pg=50
    v=None

    # Loop over words looking for a digit or "V" then a digit
    for word in str.split(version):
        word=word.lstrip('v')
        word=word.lstrip('V')
        if word[0].isdigit():
            v=word
            break

    if v is None:  return v

    # Replace acceptable delimiters with "." and split
    trans=str.maketrans("_-","..")
    v=v.translate(trans)
    digits=v.split('.')

    # If there's one giant version number, like "96" or "120", assume it's just
    # missing a dot, and they should be 9.6 or 12.0.
    if len(digits)==1:
        # Beta and RC releases have text after the digits, pull out just the version
        relnum=re.search(r'\d+', digits[0]).group(0)
        if float(relnum) > max_pg:
            f=float(v) / 10
            if f>=max_pg:  return None
            return f
        else:
            return float(relnum)

    # Normal major.minor number set
    if len(digits)>=2:
        f=float(digits[0]) + float(digits[1]) / pow(10,len(digits[1]))
        if f > max_pg:  return None
        return f
    # Give up...for now
    return None

def test_parsing():
    """
    Homemade unit testing
    List a bunch of version strings and what they should be parsed as.
    None results mean the version is rejected by the code.
    """
    test_versions = dict([
        ("9.6" , 9.6),
        ("96" , 9.6),
        ("9_6" , 9.6),
        ("9.6.0" , 9.6),
        ("V9.6" , 9.6),
        ("V96" , 9.6),
        ("v9_6" , 9.6),
        ("v9.6.1" , 9.6),
        ("10.0" , 10.0),
        ("10" , 10.0),
        ("100" , 10.0),
        ("10_0" , 10.0),
        ("100" , 10.0),
        ("v10.0" , 10.0),
        ("V100" , 10.0),
        ("v10_15" , 10.15),
        ("V10.0.0" , 10.0),
        ("V10.15.21" , 10.15),
        ("v9.6.0.0" , 9.6),
        ("9_6_0_0" , 9.6),
        ("9600" , None),
        ("9.6_1" , 9.6),
        ("9_6.1" , 9.6),
        ("1000" , None),
        ("10.1.0.0" , 10.1),
        ("10_1_0_0" , 10.1),
        ("1000" , None),
        ("10.0_0" , 10),
        ("10_1.1" , 10.1),
        ("10.12" , 10.12),
        ("V10_12" , 10.12),
        ("15rc1" , 15.0),
        ("15beta4" , 15.0)
        ])

    failed=0
    for v in test_versions.keys():
        out=version_parse(v)
        if out!=test_versions[v]:
            failed=failed+1
            print("# Failure",out==test_versions[v],v,out,test_versions[v])
    if failed>0:
        print("# Failed version parsing tests:",failed)
        print()

def machine_summary():
    """
    Estimate memory on this system via parameter or system lookup.
    """
    total_memory = total_mem()
    if total_memory is None:
        print("Error:  total memory not specified and unable to detect")
        sys.exit(1)
    print("# Memory", total_memory)

    cpus=cpu_count()
    if cpus is None:
        print("Error:  CPU count not specified and unable to detect")
        sys.exit(1)
    print("# CPU Count",cpus)

    arch=platform.machine()
    print("# Arch bits",arch_bits(arch))

def core_sweep(cores):
    """
    Output a list of interesting CPU counts to test, going to at
    least 32 cores, or 4*cores if that's larger.  32 is highlighted
    because many disk devices can queue 32 requests.
    """
    if cores>=6:
        l=[1,2,4,8,16,32]
        custom=[cores,cores*2,cores*4]
        l=list(set(l + custom))
        l.sort()
    else:
        l=[1,2,4,8,16,32]
    return l

def scale_sweep(biggest):
    """
    List scales from 20% to 100% of maximum, plus tiny 100 size
    """
    steps=5
    scales=[100] + [int(biggest * i / steps) for i in range(1, steps + 1)]
    return scales

def test_sizing():
    """
    Provide recommended sizing for performance testing of this system
    """
    total_memory = total_mem()
    if total_memory is None:
        print("Error:  total memory not specified and unable to detect")
        sys.exit(1)

    cpus=cpu_count()
    if cpus is None:
        print("Error:  CPU count not specified and unable to detect")
        sys.exit(1)

    max_memory=total_memory * 4
    max_memory_mb = round(max_memory / 1024 / 1024)

    # Each pgbench scale unit is just over 15MB.
    max_scale = max_memory_mb / 15
    # Round to nearest multiple of 250
    max_scale = 250*int((max_scale + 125.0)/250.0)

    # Turn the system config into recommended pgbench-tools limits.
    test_cpus=' '.join(map(str, core_sweep(cpus)))
    test_scales=' '.join(map(str, scale_sweep(max_scale)))

    print('SETCLIENTS="%s" # Stress %s processors' % (test_cpus,cpus))
    print('SCALES="%s" # 4X RAM=%s MB, 8X RAM scale=%s' % (test_scales,max_memory_mb,max_scale * 2))

    # Standard tuning for osm2pgsql:  3/4 of memory to the node cache,
    # capped at 60GB so far.  16GB set aside for the case where loading
    # happens on the database server.
    osm_cache = 0
    db_server_reserved = 16 * 1024 * 1024 * 1024
    if total_memory > db_server_reserved:
        osm_cache = round((total_memory - db_server_reserved) * 3 / 4 / 1024 / 1024)
        osm_cache = 100 * int((osm_cache + 50)/100)
        if osm_cache > 45000:
            osm_cache = 45000
    # TODO Make this line optional if this program ever grows proper command line switches.
    # TODO Ditto to control the DB server 16GB reservation and change the 45GB upper limit.
    print('OSMNODECACHE="%s" # osm2pgsql node cache ~3/4 RAM=%s MB' % (osm_cache,osm_cache))

def check_tracking():
    # Disabling for now
    if True:  return
    changes=0
    io=check_config('track_io_timing')
    if io=="off":
        print("Fixing track_io_timing")
        o=spawn_read("psql -Atc 'ALTER SYSTEM SET track_io_timing=ON'")
        print(o)
        changes=changes+1

    sstat=check_config('shared_preload_libraries')
    if sstat.find("pg_stat_statements")<0:
        print("Fixing pg_stat_statements")
        changes=changes+1
        # TODO Existing shared_preload_libraries is probably an empty string or None here
        # Should handle the rare case where it's set to another module and that needs
        # to be merged with this case.
        o=spawn_read("psql -Atc 'ALTER SYSTEM SET shared_preload_libraries = \'pg_stat_statements\''")
        print(o)

    o=spawn_read('psql -Atc "SELECT extname FROM pg_extension where extname=\'pg_stat_statements\'"')
    if o is None:  o=""
    o=o.strip()
    if o!='pg_stat_statements':
        o=spawn_read("psql -Atc 'CREATE EXTENSION pg_stat_statements'")
        changes=changes + 1

    if changes>0:
        print("Restart database to activate changes")

def main(program_args):
    test_parsing()
    pg_ver=version_parse(pg_version())
    print("# PostgreSQL version",pg_ver)

    machine_summary()
    print()

    test_sizing()
    check_tracking()

if __name__ == '__main__':
    sys.exit(main(sys.argv))