0

I have a large (~6 GB) postgis-2.1 postgresql-9.4 db containing elevation contours on a netbook running debian jessie . Every time I startup the system I get a process that looks like this:

00:00:01 postgres: username contours ::1(33525) SELECT

This process will run for about 30-40 mins on my netbook that I use for navigation in my truck, then it will show the process as idle. During this time, the netbook is useless. This process takes way too much in the way of memory and the disk I/O is really the killer.

My question is, how can I stop this select from being started? I've done my best to isolate the problem to the postgresql server. No other applications are initiating this query.

The postgres processes running at the time this is going on are:

  • checkpointer process
  • writer process
  • wal writer process
  • autovacuum launcher process
  • stats collector process

Update: OK, with the help of filiprem, I was able to track down the select statement that is causing this problem:

SELECT ST_XMin(ext),ST_YMin(ext),ST_XMax(ext),ST_YMax(ext) FROM (SELECT ST_Extent(way) as ext from planet_osm_line) as tmp

The trouble is I still don't know where this statement is (specifically) being generated from. The command that causes it is:

liteserv.py -p 8034 -c --config=topo.cfg topo.xml

topo.cfg has nothing pertinent in it. I did a grep on topo.xml looking for select statements, and found (3):

(select way,contour_ext,ele from &prefix;_line where contour_ext='elevation_minor') as contour_minor
(select way,contour_ext,ele from &prefix;_line where contour_ext='elevation_medium') as contour_medium
(select way,contour_ext,ele from &prefix;_line where contour_ext='elevation_major') as contour_major

Here is the content of liteserv.py:

#!/usr/bin/env python

import os
import sys
import socket
from optparse import OptionParser
from wsgiref.simple_server import make_server, WSGIServer, WSGIRequestHandler
import logging

CONFIG = 'tilelite.cfg'
MAP_FROM_ENV = 'MAPNIK_MAP_FILE'

parser = OptionParser(usage="""
        python liteserv.py <mapfile.xml> [options]
        """)

parser.add_option('-i', '--ip', default='0.0.0.0', dest='host',
        help='Specify a ip to listen on (defaults to 0.0.0.0/localhost)'
        )

parser.add_option('-p', '--port', default=8000, dest='port', type='int',
        help='Specify a custom port to run on: eg. 8080'
        )

parser.add_option('--config', default=None, dest='config',
        help='''Specify the use of a custom TileLite config file to override default settings. By default looks for a file locally called 'tilelite.cfg'.'''
        )

parser.add_option('-s', '--size', default=None, dest='size', type='int',
        help='Specify a custom tile size (defaults to 256)'
        )

parser.add_option('-b', '--buffer-size', default=None, dest='buffer_size', type='int',
        help='Specify a custom map buffer_size (defaults to 128)'
        )

parser.add_option('-z', '--max-zoom', default=None, dest='max_zoom', type='int',
        help='Max zoom level to support (defaults to 22)'
        )

parser.add_option('-f', '--format', default=None, dest='format',
        help='Specify a custom image format (png or jpeg) (defaults to png)'
        )

parser.add_option('--paletted', default=False, dest='paletted', action='store_true',
        help='Use paletted/8bit PNG (defaults to False)'
        )

parser.add_option('-d','--debug', default=True, dest='debug', type="choice", choices=('True','False'),
        help='Run in debug mode (defaults to True)'
        )

parser.add_option('-c','--caching', default=False, dest='caching', action='store_true',
        help='Turn on tile caching mode (defaults to False)'
        )

parser.add_option('--cache-path', default=None, dest='cache_path',
        help='Path to tile cache directory (defaults to "/tmp")'
        )

parser.add_option('--cache-force', default=False, dest='cache_force', action='store_true',
        help='Force regeneration of tiles while in caching mode (defaults to False)'
        )

parser.add_option('--processes', default=1, dest='num_processes', type='int',
        help='If werkzeug is installed, number of rendering processes to allow'
        )

def run(process):
        try:
                process.serve_forever()
        except KeyboardInterrupt:
                process.server_close()
                sys.exit(0)

def strip_opts(options):
        remove = [None,'config','port','host']
        params = {}
        for k,v in options.items():
                if not k in remove and not v is None:
                        params[k] = v
        return params

def print_url(options):
        if not application.debug:
                logging.warning('TileLite debug mode is *off*...')
        logging.warning("Listening on %s:%s...\n" % (options.host,options.port))
        logging.warning("To access locally view: http://localhost:%s\n" % options.port)
        remote = "To access remotely view: http://%s" % socket.getfqdn()
        if not options.port == 80:
                remote += ":%s" % options.port
        remote += "\nor view: http://%s" % socket.gethostbyname(socket.gethostname())
        if not options.port == 80:
                remote += ":%s" % options.port
        logging.warning('%s\n' % remote)

if __name__ == '__main__':
        (options, args) = parser.parse_args()
        logging.basicConfig(filename='/tmp/liteserv%s.log' % options.port, format='%(asctime)s %(message)s', datefmt='%m/%d/%Y %H:%M:%S')

        if len(args) < 1:
                try:
                        mapfile = os.environ[MAP_FROM_ENV]
                except:
                        sys.exit("\nPlease provide either the path to a mapnik xml or\nset an environment setting called '%s'\n" % (MAP_FROM_ENV))
        else:
                mapfile = args[0]
                if not os.path.exists(mapfile):
                        sys.exit('Could not locate mapfile.')

        logging.warning("[TileLite Debug] --> Using mapfile: '%s'" % os.path.abspath(mapfile))
        logging.warning("options.config: %s" % options.config)
        if options.config:
                if not os.path.isfile(options.config):
                        sys.exit('That does not appear to be a valid config file')
                else:
                        CONFIG = options.config

        if not os.path.exists(CONFIG):
                if options.config:
                        sys.exit('Could not locate custom config file')
                else:
                        CONFIG = None

        if CONFIG:
                logging.warning("[TileLite Debug] --> Using config file: '%s'" % os.path.abspath(CONFIG))      

        if options.cache_path and not options.caching:
                options.caching = True

        if options.cache_force and not options.caching:
                options.caching = True

        #parser.error("Caching must be turned on with '--caching' flag for liteserv.py to accept '--cache-path' option")
        #http_setup = options.host, options.port
        #httpd = simple_server.WSGIServer(http_setup, WSGIRequestHandler)
        #httpd.set_app(application)

        from tilelite import Server
        application = Server(mapfile, CONFIG)
        application.absorb_options(strip_opts(options.__dict__))

        try:
                from werkzeug import run_simple
                print_url(options)
                run_simple(options.host, options.port, application, threaded=False, processes=options.num_processes)
        except:
                if options.num_processes > 1:
                        sys.exit('The werkzeug python server must be installed to run multi-process\n')
                logging.warning('Note: werkzeug is not installed so falling back to built-in python wsgiref server.\n')
                logging.warning('Install werkzeug from http://werkzeug.pocoo.org/\n\n')

                from wsgiref import simple_server
                # below code was for testing multi-threaded rendering
                # which only works if we copy a map object per thread
                # so avoid this and only run multiprocess...
                #from SocketServer import ThreadingMixIn
                #class myServer(ThreadingMixIn, simple_server.WSGIServer):
                #    pass 
                #httpd = myServer(('',options.port), simple_server.WSGIRequestHandler,)
                #httpd.set_app(application)
                httpd = make_server(options.host, options.port, application)        
                print_url(options)
                run(httpd)

I don't see anything in there that would obviously lead to that select statement above being issued. Does anyone else?

0

The PostgreSQL server does not initiate any long-running SELECT statements by itself.

It must be a client connection running this query at startup.

You can do several things:

1) Enable logging and see where does the query come from. Edit postgresql.conf and add this:

logging_collector = on
log_line_prefix = '%t %p %r %d %u '
log_connections = on
log_statement = all

2) When you see the actual query you can run EXPLAIN on it to see why it takes so long. Maybe it can be optimized?

3) Check your application preferences for any startup settings. Maybe the refresh feature can be disabled?

1
  • filiprem, thank you for your suggestions. They helped me get a little further along in the process of solving the problem. I've updated the question, with information about the statement causing the problem, but still I'm having trouble finding where this statement is being issued from. – nomadicME Jun 27 '17 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.