我在运行 debian jessie 的上网本上有一个很大的 (~6 GB) postgis-2.1 postgresql-9.4 db，其中包含海拔轮廓。每次启动系统时，我都会得到一个如下所示的过程：
00:00:01 postgres：用户名轮廓::1（33525）选择
这个过程将在我用于卡车导航的上网本上运行约 30-40 分钟，然后它会显示该过程处于空闲状态。在此期间，上网本毫无用处。这个过程占用了太多内存，磁盘 I/O 才是真正的杀手。
我的问题是，如何才能阻止启动此选择？我已尽力将问题隔离到 postgresql 服务器上。没有其他应用程序正在启动此查询。
此时正在运行的 postgres 进程是：
- 检查点进程
- 作家过程
- wal 写入进程
- 自动真空启动器流程
- 统计收集器进程
更新：好的，在 filiprem 的帮助下，我能够找到导致此问题的 select 语句：
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
问题是我仍然不知道这个语句（具体）是从哪里生成的。导致它的命令是：
liteserv.py -p 8034 -c --config=topo.cfg topo.xml
topo.cfg 中没有任何相关内容。我在 topo.xml 上执行 grep 查找 select 语句，结果找到了 (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
以下是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)
我没有看到任何内容会明显导致发出上述选择语句。还有其他人吗？
答案1
PostgreSQL 服务器本身不会启动任何长时间运行的 SELECT 语句。
它必须是启动时运行此查询的客户端连接。
您可以做几件事：
1）启用日志记录Enable Logging看看查询来自哪里。编辑postgresql.conf并添加以下内容：
logging_collector = on
log_line_prefix = '%t %p %r %d %u '
log_connections = on
log_statement = all
2）当你看到实际的查询时，你可以运行EXPLAIN看看为什么需要这么长时间。也许可以优化？
3) 检查应用程序首选项中是否有任何启动设置。也许可以禁用刷新功能？