On Mar 19, 2010, at 2:27 PM, Russell Valentine wrote:
So if you did the exact same statements your program is doing with
psql
to the remote server, they take a similar time as the local?
No, the statements to the remote server take 20x longer!
2.5 (remote) vs. 0.12 (local) seconds.
You say similar dataset,
Identical. I created a new database locally and on the remote server,
then imported the same schema and test data sql files to both.
just wondering if maybe the remote was missing
a index or hasn't been vacuumed in a while. Can you do a vacuum
yourself
just to double check?
I follow the same steps for my local db and the remote server:
- dropdb
- createdb
- import schema.sql
- import testData.sql
- launch app ...
Furthermore, monitoring usage on the remote machine shows minuscule
load during my app's launch and operations.
I'm going to switch gears and look into multi-threading my data model
loads and select() statements.
Thanks for the suggestions! Much appreciated.
Scott
Russell Valentine
On 03/19/10 15:29, Scott Frankel wrote:
On Mar 19, 2010, at 12:24 PM, Russell Valentine wrote:
Perhaps time each part to see what exactly is taking up the time?
Thanks for the suggestion. I was hoping to find a global parameter
(buffer_size, or similar) that could be tweaked to improve overall
performance.
My app loads a dozen QSqlRelationalTableModel objects, in addition to
various support data. Remotely, each table model requires roughly
2 to
3 seconds to load. Locally, each requires 0.12 seconds to load!
I'm already printing timing statements to help with my optimization
efforts to date. Complete output is long. Here are some highlights:
#-------------------------------------------------------------------------------
# remote data launch
#-------------------------------------------------------------------------------
2010-03-19 12:56:02.580540 Application launch
2010-03-19 12:56:05.538501 connecting...
2010-03-19 12:56:06.564915 database connection opened
[ ~1 second to open the connection ]
2010-03-19 12:56:11.091056 loading application support data...
2010-03-19 12:56:16.912604 user data loaded...
[ ~6 seconds to load QSqlRelationalTableModel objects, using
unfiltered
select() methods ]
...
2010-03-19 12:56:24.943313 form 3/12 set
2010-03-19 12:56:27.320105 form 4/12 set
...
[ ~2.5 seconds for each of 12 forms to load additional data model
objects and set widgets accordingly ]
2010-03-19 12:56:45.861150 ready...
[ ~43 seconds to launch ]
#-------------------------------------------------------------------------------
# local data launch
#-------------------------------------------------------------------------------
2010-03-19 13:13:02.606677 Application launch
...
2010-03-19 13:13:05.733303 form 3/12 set
2010-03-19 13:13:05.854210 form 4/12 set
[ ~0.12 seconds for each of 12 forms to load additional data model
objects and set widgets accordingly ]
...
2010-03-19 13:13:06.710319 ready...
[ ~4 seconds to launch, start to finish ]
One
way to do so is below:
t=time.time()
dostuff()
print "dostuff() time = "+str(time.time()-t)+" s"
Is it really the initial connection that takes a long time to get,
or is
it something else on that remote machine. work_mem has nothing to do
with making a connection for example.
On 03/19/10 14:06, Scott Frankel wrote:
Hi all,
Would anyone have any suggestions for improving QSqlDatabase PSQL
connection performance?
The Qt docs refer to PSQL connection options and demonstrate the
"requiressl=1" example. Are there other options that can be
set? (eg:
the docs' PostgreSQL "options" bullet point) Google searches are
coming
up short.
I note that my local postgresql.conf file has a "work_mem"
statement.
(Usage: work_mem = 1MB) That could be germane, but including
the term
in my setConnectOptions() method (as part of a semi-colon separated
list) yields PSQL errors.
I've optimized my working code further. Running from a local
data set,
launch times are down to 4 seconds. Running from an identical
data set
hosted remotely, my application launches in 40+ seconds!
Thanks in advance!
Scott
On Mar 16, 2010, at 9:09 PM, Scott Frankel wrote:
Hi all,
Is there a buffer size or similar optimization parameter that
can be
set for QSqlDatabase PSQL connections? The docs refer to
PostgreSQL
"options" without specifying what they may be.
I have a PG database cluster of about 7MB, spread over a couple
dozen
tables. Locally, it takes about 6 seconds to launch my app,
loading
the model data into my forms.
When I pull an identical data-set from a remote location, my
application launch time increases to over 60 seconds. Yet
resource
utilization on the remote server is miniscule.
For comparison, I'm able to download the results of a 21MB SELECT
statement in less than 8 seconds via cmd-line psql. That's
orders of
magnitude more data in an order of magnitude less time!
I'm creating my db connection as follows. (Sample code attached
also.)
db = QtSql.QSqlDatabase.addDatabase("QPSQL")
db.setDatabaseName("fubar")
db.setHostName("localhost")
db.setUserName("admin")
db.setPassword("abc123")
Thanks in advance!
Scott
_______________________________________________
PyQt mailing list [email protected]
http://www.riverbankcomputing.com/mailman/listinfo/pyqt
_______________________________________________
PyQt mailing list [email protected]
http://www.riverbankcomputing.com/mailman/listinfo/pyqt
