Database performance issues #2714
Comments
|
I also got a mail from the Mail Bot: "Firefly III has created 2363 transactions for you.". Column #9 in the CSV file is the balance, during Import I can't find a column role that matches 'Balance', this would fix the negative balance, since the current balance could be retrieve from the CSV file. |
|
A quick reply from my phone. Firefly III won’t be able to use the balance column to self correct. If the start balance is correct, after the import the balance should still be correct. If this isn’t the case hiding it by “correcting” it through the balance will only hide the original problem. I’ll do a deep dive when I have more time. |
|
Yea thanks in advance. The balance is easily solved. EDIT: I solved it my entering the start balance with the correct balance from the year 2017. The import problem resulting into 'Gateway timeout' is the more urgent issue I'm facing. |
|
Make sure your front end or web server doesn’t time out at 30 seconds, which is usually the limit. Another option is to use the command line to import your data where there usually is no time limit at all. |
|
Ok in the docker image (firefly_iii_app) I increased the Timeout of apache2 in the I stopped and restart the docker images. On the reverse proxy side (Nginx), which is outside the Docker, I increased the proxy settings in Lets see if this works.. Still I had no clue what Firefly was doing during the import.. since it did took all transactions into account. 😕 |
|
Web interfaces are less ideal for long synchronized operations I’m afraid. |
|
Well I'm hosting Nextcloud and whatever not on the same server. I can upload 20Gb files without issues ;) We are talking about a CSV file of 511kb. Do you know what Firefly is doing for operations, spamming the javascript console? |
|
Yes if only it was just uploading the file. What’s your question exactly? |
|
What could cause this issue? What is Firefly executing during the import? Since it causes this gateway timeout issue, yet the transactions are imported. https://user-images.githubusercontent.com/628926/66571677-fc66bf00-eb6f-11e9-9990-4053b92204a1.png https://user-images.githubusercontent.com/628926/66571145-25d31b00-eb6f-11e9-9520-d3bf6d65adb0.png |
|
As you an see Firefly is taking about 1 second per request. If this is 1 transaction, is it!??. This means every transaction during import will cost 1 second, this will add up very fast.. 😖 .. 400 transactions == 400 seconds. 1500 transactions == 1500 seconds. This is not really scalable. So what is it doing? Logging from docker: |
|
Nevertheless, increasing all these timeouts worked, but I think 475 times (475 is taken from the logging), 475x 1 second is a bit too much. So would it take so long? What is it doing?
Ps. I looked-up the transaction with a zero amount. Apparently these were messages from the bank (that I disabled/enabled mobile payments), No idea why they added this into the bank records, were just informational messages. |
|
Ok let’s take one step back. Importing CSV data doesn’t scale. One second per line isn’t even too bad, depending on the complexity of the data and the speed of your system. Each individual cell has its own pre- and post-processors, database mappings and storage routines. All things considered 1s per line is pretty fast, depending on your hardware. And yes, your Nextcloud is faster dumping data to a drive. As for the import itself, it’s a bad idea regardless of Firefly III’s speed to import 2800 lines at once. Whatever error there is, it’ll be repeated 2800 times. Part of the problem is of course your bank. Adding human readable informative message to a machine readable data format is a terribly stupid idea. Personally I suggest you don’t import data at all. Start fresh, get to know your own finances and act on what you see there. If you feel you must import transactions, do so in batches. Feel free to inspect the code to find out what needs to be done to reliably import data into Firefly III. |
I also read your post: https://www.patreon.com/posts/30012174. I understand what you mean by doing the finance by hand 1 transaction at a time. 😄 I get the point, sure you get a better feeling about your money flow, however in our case it quite timing consuming otherwise.
Yea, maybe a good idea to clean-up this error log pollution. And see why a computer with 3Ghz quad-core needs 1 second to parse a single line of comma separated line, I think there are ways to optimize this.
I will from now one, the first batch is the biggest (from 2017 until now), I won't like importing this by hand.
I will. Is it still relevant though, since you are also working on this other CSV import tool? Will this new project solve all of these issues? |
|
I already prepared some clean-up actions in a PR as a start, so it's not spamming the console too much. I hope you like it! Could you point me into the right direction in the app itself, where the 'magic' happens of processing happens of the CSV import. So, I really mean the parsing, adding transactions to the DB and whatever not. I couldn't find those files were I'm looking for. Thanks in advance James! |
|
Check out these files. Mind you this code will move to a new project.
|
|
Thanks, I think there is also a performance issue in general. Eg. when applying rules. I do not know what the bottleneck is currently, but my current focus would go to:
I know it still sounds a bit vague, but I think I will take a brother look to the performance. EDIT: Pressing the button in the create rule page 'Check which transaction are matching', is rather pretty fast. I know its only on the last 200 records. But apply just 1 category takes very long. Nevertheless, I will dive into it. |
Maybe we should run Xdebug (more info about profiler) and/or Blackfire (does has a free Hack edition). With xdebug can be even more useful with Pump the profile data of Xdebug into webgrind! And hopefully we can find the bottlenecks much easier/faster:
|
|
I appreciate the effort. But before you dive into this code realize I'm building a new import routine over here. Feel free to check it out. |
|
As I said before, I think it's a generic performance issue. Dive into xdebug for tracing measurements. |
|
Finding so far:
# DB Version: 12
# OS Type: linux
# DB Type: oltp
# Total Memory (RAM): 8 GB
# CPUs num: 4
# Data Storage: hdd
max_connections = 300
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 3495kB
min_wal_size = 2GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4More info:
Hint: |
|
Thanks. Those tips are really helpful. |
|
This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. |
stale
bot
added
the
stale
Bug description
I am running Firefly III version 4.8.1.4, and my problem is:
During import I get a time-out (error: Gateway Timeout).
Steps to reproduce
I get the error message. At the same time, Firefly did import some records/transactions. Currently Firefly shows me my balance of -245,68 euros which is incorrect, I have a positive balance on my bank account.
How to increase time-out? If that is the solution...
Extra info
Debug information generated at 2019-10-10 15:05:38 Europe/Amsterdam for Firefly III version 4.8.1.4.
Bonus points
Error message:
Console log:
Got a lot of spamming the in console due to that it thinks some transactions are zero?
The text was updated successfully, but these errors were encountered: