[ https://issues.apache.org/jira/browse/MADLIB-1379?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Frank McQuillan updated MADLIB-1379: ------------------------------------ Fix Version/s: (was: v1.17) v2.0 > Fix madpack slowness by using a single session > ---------------------------------------------- > > Key: MADLIB-1379 > URL: https://issues.apache.org/jira/browse/MADLIB-1379 > Project: Apache MADlib > Issue Type: Improvement > Components: Madpack > Reporter: Domino Valdano > Assignee: Domino Valdano > Priority: Minor > Fix For: v2.0 > > > In the current implementation of madpack, the python function for sending > queries to the database, `run_query` is causing extreme slowness on some > platforms, especially during startup and shutdown. > For example, on a MacBookPro with OSX 16.7.0 and a minimal 3-segment > Greenplum demo cluster: > ``` > > time src/bin/madpack -p greenplum -c /madlib install-check -t nosuchmodule > madpack.py: INFO : Detected Greenplum DB version 5.10.0. > real 0m46.365s > user 0m0.207s > sys 0m0.136s > ``` > It takes madpack 46 seconds just to figure out that there is no module named > `nosuchmodule` in madlib, so it can't run install-check on it. > It also takes about 46 seconds to run a single install-check sql file that > *does* exist but only contains the line `SELECT 1`. The general pattern is > that `madpack` will hang for a long time, then run `install-check` or > `dev-check`, report PASS or FAIL's, then hang for a long time again, and then > finally exit. > This makes it almost unusable for rapid iterative development, for example > when writing test files and checking whether they still PASS when minor > changes are made. > I'm not sure how many platforms are affected by this slowdown, but I have > noticed it happens on my MacBook Pro's with greenplum but not postgres. > The main reason for the slowness is that there are a series of setup sql > commands issued one by one to `run_query`, and for each command (`DROP USER`, > `CREATE USER`, GRANT USAGE`, `GRANT ALL`, etc.). Instead of using a single > connection to the db, each call to `run_query` opens a new connection and > then closes it. In particular, it spawns an entirely new child process, > redirects the input and output streams, constructs a `psql` command filled > with all the right command-line options, just to execute the single line of > sql. > On `postgres`, may be a little inefficient, but it doesn't matter too much > because there is just a single machine with a single instance running. > Probably slows it down a little, but hardly noticeable. > However, on `greenplum` the situation is far worse. Each time `run_query` > creates a new `psql` client session, it first connects to the master postgres > instance. The master spawns a new child postgres process to handle that > particular client. It then signals all of the segments to create new child > postgres processes on all the remote hosts to also handle that specific > client. This involves sending encrypted network traffic back and forth > between every host in the cluster over an ssh connection, followed by each of > those hosts spawning as many new postgres processes as there are segments per > host. > The 46-second delay I've seen is for only a single machine with 1 master and > 5 segments, so I think that means we could expect delays of several minutes > or more on multi-host clusters. > I have verified that with greenplum on OSX, each time I open `psql` there is > a 5 second delay during the first sql command issued that involves segments, > but no delay for any commands that come after that since all of the extra > postgres processes have already been created. Because of how `run_query` is > written, that necessitates a `5-second` delay for every SQL command issued, > and it issues at least a dozen or so, only one of them being the actual > execution of the test script. > An easy solution to this is to rewrite `run_query` so that it uses a single > client connection instead of spawning a new client in a new child process for > each query. This is only a few lines using `psycopg`. But if we don't want > to have that as a requirement, we could have it start running `psql` the > first time, but save the input and output file descriptors so that with > subsequent queries it can send data back and forth to psql. -- This message was sent by Atlassian Jira (v8.3.4#803005)