All Projects CCdciel EqmodGui IndiStarter libpasastro SkyChart Virtual Moon Atlas Virtual Planets Atlas

View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0001719SkyChart1-Softwarepublic17-05-29 19:0021-02-09 20:26
ReporterSasa Assigned ToPatrick Chevalley  
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionfixed 
PlatformLinux 64-bitOSLubuntuOS Version17.04
Product Version4.1 SVN 
Target Version5.0 
Summary0001719: MPC data update - extremely slow
DescriptionAfter 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.
TagsNo tags attached.

Activities

Patrick Chevalley

17-06-04 11:16

administrator   ~0003834

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 ...
by
cmd:='INSERT INTO ...
This will rise an error if you load two time the same file but maybe it is quicker.

Sasa

17-06-04 16:26

reporter   ~0003844

Last edited: 17-06-04 16:27

 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!
"

Patrick Chevalley

17-06-04 17:11

administrator   ~0003846

Last edited: 17-06-04 17:27

 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.

Sasa

17-06-04 17:31

reporter   ~0003848

Last edited: 17-06-04 17:35

 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.

Patrick Chevalley

17-06-04 18:11

administrator   ~0003849

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.

Sasa

17-06-04 18:52

reporter   ~0003850

Last edited: 17-06-04 18:57

 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.

Sasa

17-06-05 06:54

reporter   ~0003852

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.

Sasa

17-06-07 19:53

reporter   ~0003854

Last edited: 17-06-07 20:02

 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.

Patrick Chevalley

17-06-25 14:50

administrator   ~0003863

Revision 3606 fix the problem with PrepareAsteroid called twice and the progress display.
https://sourceforge.net/p/skychart/code/3606

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.

Sasa

17-07-01 07:16

reporter   ~0003868

Last edited: 17-07-01 07:25

 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).

Sasa

19-02-05 00:33

reporter   ~0005329

Last edited: 19-02-05 00:41

 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.

Patrick Chevalley

21-02-09 20:26

administrator   ~0006916

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.

Issue History

Date Modified Username Field Change
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
17-06-04 17:11 Patrick Chevalley Note Added: 0003846
17-06-04 17:27 Patrick Chevalley Note Edited: 0003846
17-06-04 17:31 Sasa Note Added: 0003848
17-06-04 17:34 Sasa Note Edited: 0003848
17-06-04 17:35 Sasa Note Edited: 0003848
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
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
17-06-07 20:02 Sasa Note Edited: 0003854
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
17-07-01 07:25 Sasa Note Edited: 0003868
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
19-02-05 00:39 Sasa Note Edited: 0005329
19-02-05 00:41 Sasa Note Edited: 0005329
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