Hi,
I recently switched from .kmm file to MySQL backend. I followed instructions in official KMM documentation and created database without any problem. Data seems to be OK, KMM is working OK (making changes, adding transactions, editing payees, ...) except saving changes to SQL. I expected that SQL storage will be a little bit slower than file, but there seems to be something wrong... Saving takes about 9 minutes during which KMM freezes and is unusable. (opening database takes about 2 minutes, quite long in comparison with few seconds, but still acceptable)
The amount of data in db is: ~30ooo rowns in kmmSplits and ~13ooo in kmmTransactions.

Only two messages appears in console after saving finishes:

Code: Select all

clearTable(): "kmmSepaOrders" does not exist.
clearTable(): "kmmNationalAccountNumber" does not exist.


KMM doesn't created these tables in DB and CREATE TABLE is missing also in Tools > Generate Database SQL dialog. (but I don't thing this will cause slow saving)

What can I check/try to speed up saving process?

Client:

• Arch Linux
• KMyMoney 5.1.2
• KDE Plasma: 5.24.1
• KDE Frameworks: 5.91.0
• Qt: 5.15.2
• Linux kernel: 5.15.24-1-lts (64-bit)

DB Server:

• Server type: MySQL
• Server version: 5.5.62-0+deb8u1 - (Debian)
• Protocol version: 10

You may be better off by using the file backend. The existing database implementation is not performant enough to deal with your volume.

So this behavior is expected? There is nothing to do about it?

I need to read KMM data by 3rd party software to be able to generate QIF files with correct (existing) Payee and Account names. That was the reason to switch to SQL storage. Local XML file is not very useful in this case

kapitan-iglu wrote:So this behavior is expected? There is nothing to do about it?

I woudn't call it expected, but it is the current situation

kapitan-iglu wrote:I need to read KMM data by 3rd party software to be able to generate QIF files with correct (existing) Payee and Account names. That was the reason to switch to SQL storage. Local XML file is not very useful in this case

Does KMyMoney's QIF exporter does not produce usable results? It might be easier to tweak that than the SQL database stuff.

ipwizard wrote:

kapitan-iglu wrote:I need to read KMM data by 3rd party software to be able to generate QIF files with correct (existing) Payee and Account names. That was the reason to switch to SQL storage. Local XML file is not very useful in this case

Does KMyMoney's QIF exporter does not produce usable results? It might be easier to tweak that than the SQL database stuff.

No, I'm in opposite situation. I need to import transactions from another system into KMyMoney using existing Account and Payee names already present in KMM. So I've build mapping table between payee names in 3rd party system and kmmPayees.id. Moreover I add correct kmmAccount.accountName to each transaction (this info is missing in 3rd party system). Resulting QIF file is manually imported into KMyMoney and everithing is as expected.

Long saving time is annoying during regular work with KMM - automatically pressed Ctrl+S after new transaction leads to another coffee break... (yes, I can remove this shortcut... I just guessed something miss-configured in my KMM or DB setup)

kapitan-iglu wrote:Hi,
I recently switched from .kmm file to MySQL backend. I followed instructions in official KMM documentation and created database without any problem. Data seems to be OK, KMM is working OK (making changes, adding transactions, editing payees, ...) except saving changes to SQL. I expected that SQL storage will be a little bit slower than file, but there seems to be something wrong... Saving takes about 9 minutes during which KMM freezes and is unusable. (opening database takes about 2 minutes, quite long in comparison with few seconds, but still acceptable)
The amount of data in db is: ~30ooo rowns in kmmSplits and ~13ooo in kmmTransactions.

Only two messages appears in console after saving finishes:

Code: Select all

clearTable(): "kmmSepaOrders" does not exist.
clearTable(): "kmmNationalAccountNumber" does not exist.


KMM doesn't created these tables in DB and CREATE TABLE is missing also in Tools > Generate Database SQL dialog. (but I don't thing this will cause slow saving)

What can I check/try to speed up saving process?

Client:

• Arch Linux
• KMyMoney 5.1.2
• KDE Plasma: 5.24.1
• KDE Frameworks: 5.91.0
• Qt: 5.15.2
• Linux kernel: 5.15.24-1-lts (64-bit)

DB Server:

• Server type: MySQL
• Server version: 5.5.62-0+deb8u1 - (Debian)
• Protocol version: 10

May I jump on this post?
How did you manage to use the Mysql driver in conjunction with KMM?
The documentation is not very detailed about this.
Thanks

dbyy wrote:May I jump on this post?
How did you manage to use the Mysql driver in conjunction with KMM?
The documentation is not very detailed about this.
Thanks

Sure, I just followed steps in documentation:

1. In MySQL server I manually created:
   
   • New database ('kmmdb')
   • New user ('kmm')
   • Granted to user 'kmm' access to database 'kmmdb' with privileges listed in chapter Creating the database.
2. Enabled "SQL Storage" plugin in KMyMoney > Settings > Plugins list.
3. Saved my KMM file into database as described in chapter Creating a database:
   
   • Selected File > Save As...
   • Choosed SQL as storage type in Save Storage As... dialog.
   • Accepted warning about Borland Interbase database type clicking on Continue button.
   • Filled Select Database dialog with:
     
     • Database Type = MySQL
     • Database Name = kmmdb
     • Host Name = <server.ip.add.ress>
     • Username = kmm
     • Password = <very secret one>
4. After confirmation, KMM freezes for few minutes (approx. 10 min) while it creates tables and uploads data into it.
5. Now KMyMoney works with MySQL DB and can be used as usually.
6. On each KMyMoney startup database dialog is shown prompting for password.

ipwizard wrote:You may be better off by using the file backend. The existing database implementation is not performant enough to deal with your volume.

Perhaps ipwizard would give an opinion of using MySQL versus SQLite in KMM?

By file backend I am referring to XML based (plain text, gzip-compressed or GPG-encrypted) files. Not the SQLite database (which is also just a file on your disk). The database backend is currently in a somewhat 'unmaintained' state and that is true for any real DB backend (be it SQLite, PostgreSQL, MariaDB or MySQL). Hence I prefer the file backend.