View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0001719||SkyChart||1-Software||public||17-05-29 19:00||21-02-09 20:26|
|Reporter||Sasa||Assigned To||Patrick Chevalley|
|Platform||Linux 64-bit||OS||Lubuntu||OS Version||17.04|
|Product Version||4.1 SVN|
|Summary||0001719: MPC data update - extremely slow|
|Description||After downloading NEO from MPC site, either 5000 limited or complete 42MB entire archive, update last from up to dozen to several dozen minute. Default database engine is SQLite.|
This is indeed very slow on slower PC. Even latest SQLite in some extreme cases is very slow on Linux even to read data (with some of my app. older SQLite contains BLOBs, properly indexed it reads data under second, the same with new SQLite version read it after 30 seconds!), I believe this could be much faster.
Briefly looking into the code, I can see transactions are used. However there is quite a lot VACUUM commands which I will suggest to be avoided with large database.
|Tags||No tags attached.|
For me, on Kubuntu 17.04, loading the full MPC file (734000 asteroids) take about 30 seconds, and then 1m30s to pre-compute the orbits for two month.
There is no Vacuum command during the load process, only after deleting old data to recover disk space.
You can try to use another version of sqlite if this one make specific problem on your computer.
Or change to mysql to see if it is any better.
You can also try to change the following line in cu_database.pas:
in function LoadAsteroidFile, row 738, replace:
cmd:='REPLACE INTO ...
cmd:='INSERT INTO ...
This will rise an error if you load two time the same file but maybe it is quicker.
I will try with that older version worked much faster with my own app, as it is is override all the time when update/upgrade and make it quite a problem with user as well, as I'm failing to find a way to use specific lib path (root) of sqlite on linux without another basic synapse change... For windows that is trivial.
SQLite support constructions as 'REPLACE OR INSERT INTO ...' as well. However, I'm not certain that will help much...
Complete cycle for MCP update is around 35 minutes on a bit old computer (10 years ago, with 1GB of RAM). The database is around 370MB (DSS picture and full MCP data included). That is: update(+vacuum?) around 20 minutes and re-computation around 15 minutes. I can see temp journal file exceed 160MB during process, which is more than MPC actually is (before using it, database was about 307MB), probably using swap partition during process... And that is very slow even with Lubuntu.
I have just installed mySQL 5.7 server and notice CDC refuse to connect with.
Server is running and can be worked with console app fine (mysql -u root -p). I have never worked with mySQL (mainly FB and sometimes PostgreSQL only with Delphi), especially with Lazarus in order to locate what is actually problem.
Password and root user saved with skychart.ini, double checked server is working, however alwas is refused. Only what I though may be the case is that cannot resolve localhost, however, even with 127.0.0.1 is the same:
SQL database not available.
SQL database software is probably not installed!
Switching between sqlite and mysql is a bit rough.
When you change the setting the program restart and show this error.
Then re-open the database setting and click the "create database" button.
For me this work with Ubuntu mysql server 5.7.18
edit: for me it work with mysql but much slower than sqlite3.
I have tried all pointed in help file (with your latest beta installed from .deb). Respond is only error:
Connection to localhost, 3306 failed! 0
Verify if the MySQL server is running, and control the userid/password.
Sever is started and confirmed it rans by:
sudo /etc/init.d/mysql start
As well no changes tried with:
sudo service mysql start
Is there anything specific needed for used Lazarus mySQL lib than simply server installation (including automatic installation of dependencies)?
sudo apt-get install mysql-server
I will actually try to test with elementary Lazarus demo in order to locate what is the problem with connection.
Mysql can be configured to use only unix socket but no networking.
You can check that networking is active with telnet:
telnet localhost 3306
to exit press enter two time.
Thank you very much!
Telnet command show 5.7.18-0ubuntu0.17.04.1...
The command "service mysql status" as well shows server is running.
In any event, after debugging, I have located cause: proper libmysqlclient.so lib missing.
After installing proper package, all started to work as intended:
sudo apt-get install libmysqlclient-dev
This would be very nice to be shown in some message and documentation.
I will now test speed, etc related to the mySQL and report results later.
Yes, mySQL is even slower. When elapsed time exceed 35 minutes, I have stop further testings with.
Faster work with SQLite will include following:
1. Periodically commit during update (on each 5000 rows or so)
2. Binding approach. Each insert/replace query require long procedure to proceed, while binding approach will require parsing once. However, If use only one approach for both DB engines, that will require first to separate procedures, which involve some moderate level of code complexity.
I'm willing to do upper, if that is fine with you as daily update of full MPC on slow PCs is indeed very slow. As well, I will first test all with some older version of SQLite3.
It seems that main problem is re-computation of asteroids (and probably comets) is called twice from main menu item "Updates". The PrepareAsteroid() is the function called twice.
Once when call DownloadAsteroid.Click and secondly when call RecomputeAsteroid(). The problem also is that first time it is called, there is no progress, when I actually though it is called VACUUM.
Second main problem is speed of update. The callback function Planet.PrepareAsteroidCallback().
And the last problem seems to be somewhere with locking/unlocking tables. The database rise all the time auto-update from menu is called, each time some 200MB for download full MPCORB file. Several times from "Updates" main menu and database rise to the 1.8GB. Of course, sometimes I terminated app during update tests and probably some tables/records as well remain locked or corrupted.
With latest SQLite 3.16.2 manually compiled and installed, update from "Solar System" form last around 15 minutes (update 4 min + re-computation 11min). From "Updates" main menu item is from 25-30 minutes.
Revision 3606 fix the problem with PrepareAsteroid called twice and the progress display.
If you kill the application during an update you must reset all the asteroid table by selecting Solar system/Asteroid/Data maintenance/Quickly delete all data
Deleting all the table before a new test is a good habit. You probably have a lot of obsolete data in the database to get 1.8GB as my db size after deleting the asteroid is 76MB and after loading the full MPC it is 337MB.
There is another problem involved here, and that is evident now - left mouse button malfunction. On one press it is always sent at least two signals. In that case, it seems that multiple calls for update are preformed as well. In any way, mouse is replaced and with upper fix seems that update from menu and form is the same.
With SQLite, when transaction begin, it should use temp journal file and when finish, to flush data to main database, in which case termination cannot affect on main data consistency before transaction ends. However, when use lock/unlock mechanism it may behaves a bit differently. With SQLite, I prefer to avoid lock/unlock mechanism and instead use simple flags (update is in progress or not).
I may try to fix this issue for SQLite in the future. However, changes may not be compatible with MySQL functions/procedures and current order, as with SQLite may require completely different approach in order to speed up process maximally.
Since MySQL will be in parallel active until revision 4.4, currently is not the right moment for such corrections.
This is fixed by the current beta version by removing the use of sqlite to store the asteroid elements.
Now it use a binary file that is loaded on startup, current full MPCorb take only 85 MB of memory. The asteroid magnitude table use a similar binary file.
Speedup is more than 10x on my computer.
At the moment I keep the sql table for the daily position because the search by area is efficient.
Maybe in the future I can remove this table to use a file structure similar to the star catalog.
|17-05-29 19:00||Sasa||New Issue|
|17-06-04 11:16||Patrick Chevalley||Assigned To||=> Patrick Chevalley|
|17-06-04 11:16||Patrick Chevalley||Status||new => feedback|
|17-06-04 11:16||Patrick Chevalley||Note Added: 0003834|
|17-06-04 16:26||Sasa||Note Added: 0003844|
|17-06-04 16:26||Sasa||Status||feedback => assigned|
|17-06-04 16:27||Sasa||Note Edited: 0003844||View Revisions|
|17-06-04 17:11||Patrick Chevalley||Note Added: 0003846|
|17-06-04 17:27||Patrick Chevalley||Note Edited: 0003846||View Revisions|
|17-06-04 17:31||Sasa||Note Added: 0003848|
|17-06-04 17:34||Sasa||Note Edited: 0003848||View Revisions|
|17-06-04 17:35||Sasa||Note Edited: 0003848||View Revisions|
|17-06-04 18:11||Patrick Chevalley||Note Added: 0003849|
|17-06-04 18:52||Sasa||Note Added: 0003850|
|17-06-04 18:57||Sasa||Note Edited: 0003850||View Revisions|
|17-06-05 06:54||Sasa||Note Added: 0003852|
|17-06-07 19:53||Sasa||Note Added: 0003854|
|17-06-07 20:01||Sasa||Note Edited: 0003854||View Revisions|
|17-06-07 20:02||Sasa||Note Edited: 0003854||View Revisions|
|17-06-25 14:50||Patrick Chevalley||Note Added: 0003863|
|17-07-01 07:16||Sasa||Note Added: 0003868|
|17-07-01 07:23||Sasa||Note Edited: 0003868||View Revisions|
|17-07-01 07:25||Sasa||Note Edited: 0003868||View Revisions|
|18-10-09 17:42||Patrick Chevalley||Target Version||=> 5.0|
|19-02-05 00:33||Sasa||Note Added: 0005329|
|19-02-05 00:35||Sasa||Note Edited: 0005329||View Revisions|
|19-02-05 00:39||Sasa||Note Edited: 0005329||View Revisions|
|19-02-05 00:41||Sasa||Note Edited: 0005329||View Revisions|
|21-02-09 20:26||Patrick Chevalley||Status||assigned => resolved|
|21-02-09 20:26||Patrick Chevalley||Resolution||open => fixed|
|21-02-09 20:26||Patrick Chevalley||Note Added: 0006916|