Very slow import process on standalone MariaDB
lhooge opened this issue · 8 comments
Hi,
thanks for this great plugin!
Unfortunately I'm having troubles importing the Filmliste using a MariaDB database. It is taking a very long time (~2 days). The sqlite import took 20 seconds.
I'm using the python 3 version (mvupdate3) latest version v0.6.6.
mysql Ver 15.1 Distrib 10.3.25-MariaDB
Name: mysql-connector-python
Version: 8.0.22
Summary: MySQL driver written in Python
In slow logs I can see that the DROP TEMPORARY TABLE IF EXISTS t_film after every 1000 rows (if I understand correctly) might be the performance issue:
201217 14:56:36 38 Query SET @_ftInsertShow_arg1=2
...
...
DROP TEMPORARY TABLE IF EXISTS t_film
201217 14:57:19 38 Query SET @_ftInsertShow_arg1=2
For the /tmp directory I'm using a tmpfs
tmpfs 2.0G 800K 2.0G 1% /tmp
Strace:
14:37:40.027956 recvfrom(3, "\0\374\350\3\0)\0\0\0.Rows matched: 1000 Ch"..., 56, 0, NULL, NULL) = 56 <0.000024>
14:37:40.028068 recvfrom(3,
"0\0\0\2", 4, 0, NULL, NULL) = 4 <24.163619>
14:38:04.191844 recvfrom(3, "\0\0\0)\10\0\0(Rows matched: 0 Changed"..., 48, 0, NULL, NULL) = 48 <0.000011>
Can I optimize the slow import using the MariaDB standalone instance?
The initial load takes a while...afterwards updates work better...
You can try to disable bin log which helps on slow devices (if you do not need bin log) and you can do vacuum (optimize table) on a regular basis...
Thanks for the information.
Next version will be much faster but still in alpha
You can apply for beta testing on the new version....it more or less ready
Thank you! Will test it soon!
Its not in the repo yet....just let me know and i will attache a version here (19 or 18?)
Ah, ok. I'm using version 18 yet. But if you want I can test it for version 19.
Have fun...the SQLITE native option is not working yet (since it wasnt deployed to the server yet)...but mysql doesnt require this...
plugin.video.mediathekview-1.0.0+matrix.1.zip
plugin.video.mediathekview-1.0.0.zip