In-Development TradeDangerous: power-user trade optimizer

[Tromador]

It is quite annoying to have to wait for 2+ minutes for the listener update over a single transaction to commit especially since you get no feedback that anything is happening.

What exactly takes 2 minutes when running the listener?

It sounds like you mean a single market update. If so, be aware that this takes less than a second, often only 100ths of a second for my server, or 3 different PCs we have feedback from.

Along with taking 40 odd minutes to process listings.csv when the rest of us do it in 5.

Only so much can be done programmatically if you simply have a slow machine.

 

[MarkAusten]

What exactly takes 2 minutes when running the listener?

It sounds like you mean a single market update. If so, be aware that this takes less than a second, often only 100ths of a second for my server, or 3 different PCs we have feedback from.

Along with taking 40 odd minutes to process listings.csv when the rest of us do it in 5.

Only so much can be done programmatically if you simply have a slow machine.

Sorry, I wasn't being clear again. If you import the listings file in a single transaction, the commit can take nearly 3 minutes to complete during which time the system appears to freeze in that nothing appears to happen during that time.

As for a slow machine, I didn't think I had one. I have a i5-4690 running at 3.5GHz with 16GB RAM

Still, I have updated my games PC with the latest code and the import is as follows:

NOTE: Downloading file 'listings.csv'.
NOTE: Requesting http://elite.ripz.org/files/listings.csv
NOTE: Downloaded 151.8MB of gziped data  11.5MB/s
NOTE: Processing market data from listings.csv: Start time = 2018-07-25 09:59:50.560386
NOTE: Finished processing market data. End time = 2018-07-25 10:10:50.699030

So it now takes 11 minutes on my PC instead of the 40 or so previously, a factor of around 4 increase in performance with your recent code improvements. A significant improvement, many thanks.

 

[Tromador]

Sorry, I wasn't being clear again.

Yes, I find I am often really struggling to understand what you are on about. I want to help, but it's hard if you say "listener" but mean something entirely different

For the avoidance of doubt (and I apologise if you are already very clear on this).

The listener processes messages from the EDDN queue, places them in the database (and in server mode creates the listings-live.csv). It checks from time to time to see if there is new master data and if so, calls the plugin.

The plugin downloads the various json files, listings.csv and if available listings-live,csv and does the big bulk data imports, either run directly from command line, or if called by the listener.

I have a i5-4690 running at 3.5GHz with 16GB RAM

I am running I7-7700K @ 4.2Ghz on 16GB RAM (with the mobo timings tuned to take advantage of my fast memory).
Avi posted his above somewhere.

 

[MarkAusten]

Yes, I find I am often really struggling to understand what you are on about. I want to help, but it's hard if you say "listener" but mean something entirely different

For the avoidance of doubt (and I apologise if you are already very clear on this).

The listener processes messages from the EDDN queue, places them in the database (and in server mode creates the listings-live.csv). It checks from time to time to see if there is new master data and if so, calls the plugin.

The plugin downloads the various json files, listings.csv and if available listings-live,csv and does the big bulk data imports, either run directly from command line, or if called by the listener.

I'll try to do better.

I am running I7-7700K @ 4.2Ghz on 16GB RAM (with the mobo timings tuned to take advantage of my fast memory).
Avi posted his above somewhere.

So, not that slow then. Anyway, having lived with 40 minutes plus since we started this, I find that I can deal with just 11 minutes now quite easily.

 

[Tromador]

IMPORTANT MESSAGE

All seems quiet. I've closed a load of tickets. TD & the listener seem to be working and stable.

I have a small amendment to make to the docs to cover those playing in 'solo' mode (or sourcing prices in some wierd unsupported way).

If anyone knows of a cause or just impediment why we should not now go ahead with a formal re-release out of "in-development" and into "released", along with having the various tool listings update their URLs and such, this is the time to speak up.

 

[eyeonus]

Other than the fact that I still haven't figured out how to get the EDDBlink plugin help command to stop breaking and you haven't pushed the optimizations (eg. WAL) you made on the server....

 

[gazelle]

Other than the fact that I still haven't figured out how to get the EDDBlink plugin help command to stop breaking and you haven't pushed the optimizations (eg. WAL) you made on the server....

Line 91 of plugins/__init__.py: remove the trailing "," which makes the subsequent_indent an tuple which will cause textwrap to fail.

right: tw.subsequent_indent=' ' * 16
wrong: tw.subsequent_indent=' ' * 16,

Long standing bug. You are the first one to make such long descriptions that it needs to wrap

Maybe also change the 16 to a 20 to get the text aligned nicely.

 

[eyeonus]

Line 91 of plugins/__init__.py: remove the trailing "," which makes the subsequent_indent an tuple which will cause textwrap to fail.

right: tw.subsequent_indent=' ' * 16
wrong: tw.subsequent_indent=' ' * 16,

Long standing bug. You are the first one to make such long descriptions that it needs to wrap

Maybe also change the 16 to a 20 to get the text aligned nicely.

Yeah, I found the problem already. Just working on making the standalone plugin fix right now.

 

[Tromador]

Other than the fact that I still haven't figured out how to get the EDDBlink plugin help command to stop breaking and you haven't pushed the optimizations (eg. WAL) you made on the server....

I'm not sure I should push them. Honestly, I wouldn't run WAL unless I was running listener in server mode. I guess the other couple of optimizations would be find for TD though. Maybe a documentation note if someone is setting up a server.

WAL is good for managing locks where you have many writes, but still need read (which sums up listener), but might actually slow down access where it's mostly reads - ie, when querying TD under normal client side use.

 

[eyeonus]

I'm not sure I should push them. Honestly, I wouldn't run WAL unless I was running listener in server mode. I guess the other couple of optimizations would be find for TD though. Maybe a documentation note if someone is setting up a server.

WAL is good for managing locks where you have many writes, but still need read (which sums up listener), but might actually slow down access where it's mostly reads - ie, when querying TD under normal client side use.

In that case we can just put the command to turn WAL on in the listener thread, that way it'll only be turned on in server mode. That said, even running in client mode, the listener does a lot of writes, and we do want it to be able to read at any time, so that the client can do trade.py runs without waiting on the listener.

 

[Tromador]

In that case we can just put the command to turn WAL on in the listener thread.

No. As it's not something you turn on temporarily. Once activated, it's persistent and creates additional .wal and .shm files which can double the on disk size of the DB - you have to do some other commands to get it turned off again. It's a fundamental choice of journalling method, not a quick & dirty speedup that can be toggled on and off. Running in WAL mode for any period of time (to be honest, running any SQLITE db long term) really needs regular VACUUM and OPTIMIZE operations running as well to keep things healthy (even if it's not that massive of a DB). That's something I am doing manually atm. I may roll it them into the listener as automated events at some point, but it likely needs another thread and I don't feel that server maintenance issues should hold back a release of the software.

I guess the short and curlies of what I'm saying is that an administrator running a service really should know what they are doing with WAL and have the choice to deploy, or not - rather than have us impose it.

That said, even running in client mode, the listener does a lot of writes, and we do want it to be able to read at any time, so that the client can do trade.py runs without waiting on the listener.

I agree, but people really shouldn't be running the listener in client mode, aside from testing and such, in which case, again - we don't want to stick them with WAL mode if they aren't fully aware of the implications.

 

[eyeonus]

No. As it's not something you turn on temporarily. Once activated, it's persistent and creates additional .wal and .shm files which can double the on disk size of the DB - you have to do some other commands to get it turned off again. It's a fundamental choice of journalling method, not a quick & dirty speedup that can be toggled on and off. Running in WAL mode for any period of time (to be honest, running any SQLITE db long term) really needs regular VACUUM and OPTIMIZE operations running as well to keep things healthy (even if it's not that massive of a DB). That's something I am doing manually atm. I may roll it them into the listener as automated events at some point, but it likely needs another thread and I don't feel that server maintenance issues should hold back a release of the software.

Sorry, I meant to say exporter thread, since that's the one that only runs when the listener is in server mode.

 

[Tromador]

Sorry, I meant to say exporter thread, since that's the one that only runs when the listener is in server mode.

I guess I could get behind that, if you would help me code up a routine/thread that would run the periodic maintenance.

 

[Tromador]

I wonder what is causing the FK exception? I have some time this week so I might try to dig into this a little deeper.

Having come across annoyances with ancient versions of python & sqlite libraries even on the latest Redhat Linux, I wonder (as a stab in the dark) if you might be experiencing the same on MacOS (if that's what you are testing).

Example - checking SQLite version on my PC.

Python 3.6.5 (v3.6.5:f59c0932b4, Mar 28 2018, 17:00:18) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.21.0'

Your minimum version for FK support is 3.6.19

 

[Tromador]

Can a Python person tell me what is the difference between

conn = sqlite3.connect(MySQLiteDB)
conn.execute("SOME SQL WHERE SQL IS GROOVY")

And

conn = sqlite3.connect(MySQLiteDB)
c = conn.cursor()
c.execute("SOME SQL WHERE SQL IS GROOVY")

I've just pushed a minor TD update to add a couple of pragma for a bit of extra speed[1]. Now Oliver in his original uses conn.execute to set up foreign keys, so I've copied his construct for my pragma also - but everything I have read suggests that even for pragma, I should be using a cursor.
Hence I'm unsure if I (and I guess Oliver in this case) did this correctly and so reassurance/explanation would be useful/helpful. Thanks (EDIT: It looks like it's working, as it's importing listings.csv with considerably less disk activity, but still confirmation would be nice).

[1] A power cut may cause corruption if the transaction happens to be in memory rather than committed to disk, but I figure the likelihood of that vs the ease with which any user can just rerun from clean justifies the risk.

 

[Avi0013]

Can a Python person tell me what is the difference between

conn = sqlite3.connect(MySQLiteDB)
conn.execute("SOME SQL WHERE SQL IS GROOVY")

And

conn = sqlite3.connect(MySQLiteDB)
c = conn.cursor()
c.execute("SOME SQL WHERE SQL IS GROOVY")

Ignorant internet search results:

https://stackoverflow.com/questions/10660411/difference-between-cursor-and-connection-objects
https://stackoverflow.com/questions/6318126/why-do-you-need-to-create-a-cursor-when-querying-a-sqlite-database

And from [url]https://docs.python.org/3/library/sqlite3.html#using-sqlite3-efficiently
[/URL][h=2]12.6.8. Using sqlite3 efficiently¶[/h] [h=3]12.6.8.1. Using shortcut methods[/h] Using the nonstandard execute(), executemany() and executescript() methods of the Connection object, your code can be written more concisely because you don’t have to create the (often superfluous) Cursor objects explicitly. Instead, the Cursor objects are created implicitly and these shortcut methods return the cursor objects. This way, you can execute a SELECT statement and iterate over it directly using only a single call on the Connection object.

 

[Tromador]

Thanks, I sort of skimmed quite a lot of those. I was hopeful someone might be able to give a neat concise answer - which I would still be interested in.

However if using conn.execute implies an automagic cursor, then I guess my pragma statements are covered.
(Of couse, pysqlite3 and it's implicit creation of transactions smacked us about the head once, but I think we'll be ok this time).

 

[eyeonus]

Can a Python person tell me what is the difference between

conn = sqlite3.connect(MySQLiteDB)
conn.execute("SOME SQL WHERE SQL IS GROOVY")

And

conn = sqlite3.connect(MySQLiteDB)
c = conn.cursor()
c.execute("SOME SQL WHERE SQL IS GROOVY")

I've just pushed a minor TD update to add a couple of pragma for a bit of extra speed[1]. Now Oliver in his original uses conn.execute to set up foreign keys, so I've copied his construct for my pragma also - but everything I have read suggests that even for pragma, I should be using a cursor.
Hence I'm unsure if I (and I guess Oliver in this case) did this correctly and so reassurance/explanation would be useful/helpful. Thanks (EDIT: It looks like it's working, as it's importing listings.csv with considerably less disk activity, but still confirmation would be nice).

[1] A power cut may cause corruption if the transaction happens to be in memory rather than committed to disk, but I figure the likelihood of that vs the ease with which any user can just rerun from clean justifies the risk.

conn.execute implicitly creates a cursor object to execute the statement. Explicitly creating the cursor allows you to refer to it directly. That's it, AFAIK.

 

[Tromador]

Coriolis/EDDB managed to disagree on the name of the Krait in their respective output which caused the plugin to barf if you run 'clean' (and I suspect if you run 'all') TD will barf.

I've pushed a fix for that and also the DB optimisations. No promises how much (if any) improvement you'll get to speed as systems vary wildly, but hopefully you'll get something.

 

[gazelle]

The conn.execute() is just a shortcut. It will create a cursor for every call thought.

conn = sqlite3.connect(MySQLiteDB)
conn.execute("SOME SQL") # new cursor
conn.execute("SOME SQL") # new cursor
conn.execute("SOME SQL") # new cursor

conn = sqlite3.connect(MySQLiteDB)
curs = conn.cursor()
curs.execute("SOME SQL") # same cursor
curs.execute("SOME SQL") # same cursor
curs.execute("SOME SQL") # same cursor

I don't think it's much of a difference.