Description
Paul Hoffman
2019-09-26 18:31:17 UTC
Hi Paul, can you share why you closed INVALID? (In reply to Katrin Fischer from comment #1) > Hi Paul, can you share why you closed INVALID? I tweaked bug #23626 (memory consumption related to the charting feature) to encompass this problem, because they involve the same files and the same underlying problem -- running a report and then doing something with the results (charting or exporting) potentially consumes all available memory. Maybe I should post a comment there with the details from this ticket? (In reply to Paul Hoffman from comment #2) > (In reply to Katrin Fischer from comment #1) > > Hi Paul, can you share why you closed INVALID? > > I tweaked bug #23626 (memory consumption related to the charting feature) to > encompass this problem, because they involve the same files and the same > underlying problem -- running a report and then doing something with the > results (charting or exporting) potentially consumes all available memory. > > Maybe I should post a comment there with the details from this ticket? You could use 'mark as duplicate' or choose 'RESOLVED MOVED' with a comment - that would link the bugs and make this clearer to people researching bugs later. Also if you leave out the # bugzilla will show a link: bug 23626 I'm reopening this as suggested by Nick Clemens in bug 23626. What were the rationals for using a big string rather than writing directly to SDTOUT or a temporary file? Are theses assumptions still valid? (In reply to Didier Gautheron from comment #5) > What were the rationals for using a big string rather than writing directly > to SDTOUT or a temporary file? > > Are theses assumptions still valid? It looks like it used to print out to STDOUT but it was changed in Bug 11679. After reviewing the code, I'd say it was probably a desire to make the code more readable/easier to maintain. However, it does create this performance problem. Fixing the "tab" and "csv" export should be fairly trivial, but the ODS will be harder since it's a more complex file format (ZIP compressed XML). I'll write another comment about that... Wow looking at OpenOffice::OODoc... it hasn't been updated in over 10 years. It's amazing that it still works. Excel::Writer::XLSX has an interesting little write up on the topic of memory usage (see https://metacpan.org/pod/Excel::Writer::XLSX#SPEED-AND-MEMORY-USAGE). It looks like OpenOffice::OODoc uses Archive::Zip and Archive::Zip doesn't seem to be able to stream to output... It looks like Excel::Writer::XLSX manages memory by writing worksheets out as temporary files (https://metacpan.org/pod/Excel::Writer::XLSX#set_tempdir()). Of course Excel::Writer::XLSX still adds every one of those temporary files into memory when it's saving the workbook, so it would still have a memory spike. Rewriting OpenOffice::OODoc is not really an option, but that would be the most "correct" solution I imagine. However, realistically, we could be more optimal in our current use of OpenOffice::OODoc. As Paul mentions, it makes no sense to do a $sth->fetchall_arrayref(), plus building @$ods_content, and then having an in-memory representation in OpenOffice::OODoc. That's 3x more memory usage than we need to use. We should just use something like $sth->fetchrow_hashref or $sth->fetchrow_arrayref and feed OpenOffice::OODoc row by row. -- Note too that the ODS format will have proxy issues for large exports, because guided_reports.pl will write the ZIP to disk, read the ZIP from disk, write to STDOUT, and then Plack::App::CGIBin will buffer STDOUT in a temporary file on disk, and then it will send the whole response all at once to the Apache reverse proxy. An alternative solution might be to write a CSV file to a temporary file and then use LibreOffice's CLI tools to convert from CSV to ODS. Example: soffice --convert-to ods koha.csv --headless I haven't tried that though, so I can't speak to its performance. I seem to recall Indranil saying OpenOffice or LibreOffice had some memory usage issues for large spreadsheets. It might not be any better. Plus, it would add a large dependency to Koha for just 1 thing. I think that's all I have for ideas though. I think the ODS format is just problematic - at least with the tools that we have to hand. Just a note here to say that this is still a problem - perhaps we should only support CSV and TAB for exports? (In reply to Liz Rea from comment #10) > Just a note here to say that this is still a problem - perhaps we should > only support CSV and TAB for exports? The .ods format is what we use most as CSV is not recognized by Excel as utf8 and so umlauts are broken. You have to separately import the data which is a lot of extra steps. I'd really really like to keep it. Just noting that this has caused a bunch of OOMs for our libraries this week, not sure why it's happening all of a sudden but this is a real problem for us. Keen to collaborate on a solution! We've received sponsorship to try and fix the ODS export format. Any ideas are welcome, we're working on this now (In reply to Aleisha Amohia from comment #13) > We've received sponsorship to try and fix the ODS export format. > > Any ideas are welcome, we're working on this now You'll want to go with a streaming response for all formats. Instead of putting everything into $content, you'll want to print to STDOUT line by line to save memory. However, I don't know why I didn't mention it before, but even if you tried to do a streaming response where each row was written out to STDOUT one by one, it won't work as expected because Koha uses Plack::App::CGIBin. You can see fully explanations in bug 8437 and bug 26791 in my comments there. Basically, the whole response gets buffered in a temporary file before it's sent to the Apache proxy to send back to the client. Plus, for ODS, you'll have to wait for the content of generate_ods as well. (If you want to see a possible cheeky solution for that, you can check out bug 31744. Not sure if it'll work for ODS like it works for CSV though.) Anyway, just some thoughts... Created attachment 168116 [details] [review] Bug 23685: Don't store ODS content in a variable to save memory Just attaching this to start. Sponsored-by: Waikato Institute of Technology I don't know the kind of report I need to test crashing the system when I export to ODS. Any ideas? I've just avoided adding to a $content variable as a start and can confirm it works, but doesn't address the Plack problem that David Cook raised. Not sure the best way around that. (In reply to Aleisha Amohia from comment #16) > I don't know the kind of report I need to test crashing the system when I > export to ODS. Any ideas? Try something like this: SELECT biblio.* FROM biblio CROSS JOIN biblio b limit 10000; I think it should make an enormous number of rows, so you can play with the limit however you like. > I've just avoided adding to a $content variable as a start and can confirm > it works, but doesn't address the Plack problem that David Cook raised. Not > sure the best way around that. Yeah it's a hard one. For now, the best bet is probably to avoid using Plack for this. It sucks, but it's the only practical solution I can think of. There just isn't enough community support to move forward on a Mojolicious/Plack-based controller solution yet. I've tried a few times to progress it, but it's too much for just 1 person. Created attachment 168570 [details] [review] Bug 23685: Exclude guided_reports.pl from plack When attempting to export reports in ODS format from Koha, plack can time out Excluding the script from plack is a simple fix until we have a more permanent fix for this issue. To test: 1. Create a report which will generate an enormous number of rows, such as SELECT biblio.* FROM biblio CROSS JOIN biblio b; (add a limit of 10000 or something if you like i.e. SELECT biblio.* FROM biblio CROSS JOIN biblio b limit 10000;) 2. Run the report 3. Apply patch, copy changes to /etc/koha/apache-shared-intranet-plack.conf 4. Restart all the things 5. Download the results in ODS format 6. Confirm the export works as expected Sponsored-by: Waikato Institute of Technology Created attachment 168577 [details] [review] Bug 23685: Exclude guided_reports.pl from plack When attempting to export reports in ODS format from Koha, plack can time out Excluding the script from plack is a simple fix until we have a more permanent fix for this issue. To test: 1. Create a report which will generate an enormous number of rows, such as SELECT biblio.* FROM biblio CROSS JOIN biblio b; (add a limit of 10000 or something if you like i.e. SELECT biblio.* FROM biblio CROSS JOIN biblio b limit 10000;) 2. Run the report 3. Apply patch, copy changes to /etc/koha/apache-shared-intranet-plack.conf 4. Restart all the things 5. Download the results in ODS format 6. Confirm the export works as expected Sponsored-by: Waikato Institute of Technology Signed-off-by: David Nind <david@davidnind.com> diff --git a/debian/templates/apache-shared-intranet-plack.conf b/debian/templates/apache-shared-intranet-plack.conf index bcee399443..6fa4014929 100644 --- a/debian/templates/apache-shared-intranet-plack.conf +++ b/debian/templates/apache-shared-intranet-plack.conf @@ -14,6 +14,7 @@ ProxyPass "/cgi-bin/koha/tools/export.pl" "!" ProxyPass "/cgi-bin/koha/tools/upload-cover-image.pl" "!" ProxyPass "/cgi-bin/koha/svc/cataloguing/metasearch" "!" + ProxyPass "/cgi-bin/koha/reports/guided_reports.pl" "!" Can understand this choice. But it takes years to get them out of this list again? We had quite a bunch when we started with Plack in 2015 ? commit 4adc4ee5f8d3e113b62094552be6c80942289c3e Author: Tomás Cohen Arazi <tomascohen@theke.io> Date: Wed Mar 4 00:19:40 2015 +0100 Bug 13791: Plack out-of-the-box support on packages (In reply to Katrin Fischer from comment #11) > The .ods format is what we use most as CSV is not recognized by Excel as > utf8 and so umlauts are broken. You have to separately import the data which > is a lot of extra steps. I'd really really like to keep it. I guess that it takes a few steps more to import in Excel correctly, but that should really be possible. Do not remember any details, but you should be able to google it :) Thinking about workarounds.. Cant we put some user defined limit on e.g. number of records to report / download ? Removing ODS is a serious option too. We need some more input here. (Moving to In Discussion wont attract many people..) Moving to Major (In reply to Marcel de Rooy from comment #21) > (In reply to Katrin Fischer from comment #11) > > The .ods format is what we use most as CSV is not recognized by Excel as > > utf8 and so umlauts are broken. You have to separately import the data which > > is a lot of extra steps. I'd really really like to keep it. > > I guess that it takes a few steps more to import in Excel correctly, but > that should really be possible. Do not remember any details, but you should > be able to google it :) I can do it - but it's really hard to explain and train, while .ods just works fine with both. I am quite against removing .ods as it was such an improvement for us. Our reports usually contain umlauts and with .cvs and Excel we always got reports about broken umlauts and it's really tedious to import the data correctly (many more clicks). On the other hand we didn't experience the serious issues this bug is hoping to fix, maybe because the exports are usually quite small in comparison. I think hardcoding a limit would be totally acceptable for us if there is no better way to fix. Thanks all for the input. What should the limit be? As an aside, another option would be to rewrite reports/guided_reports.pl to use a Plack/Mojolicious handler. Easier said than done although a lot of the core work needed is already prepared in bug 31380. At some point, surely we need to stop using Plack::App::CGIBin and move into a real Plack/Mojo world. We can do it incrementally, but it needs to be done at some point... We suffered from this bug recently, but please do not remove ODS ! Coming back here, we got some feedback :) Thx for that. Created attachment 171136 [details] [review] Bug 23685: (follow-up) Control ODS exporting The pref ReportingAllowsODS_Export should be true for exporting to ODS in Reporting. Test plan: Set pref to false value. Check if option is no longer available on report's Download menu. Manipulate URL with op=export&format=ods. No data expected. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Created attachment 171137 [details] [review] Bug 23685: (follow-up) Dbrev for new preferences Test plan: Run updatedatabase. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Created attachment 171138 [details] [review] Bug 23685: (follow-up) Add prefs to Administration form Test plan: Edit both prefs. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Created attachment 171139 [details] [review] Bug 23685: (follow-up) Add export limit for guided reports Test plan: Set pref GuidedReportsExportLimit to some positive integer. Test if exporting a report respects that limit. Run t/db_dependent/Koha/Reports.t Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Created attachment 171140 [details] [review] Bug 23685: (follow-up) Control ODS exporting The pref ReportingAllowsODS_Export should be true for exporting to ODS in Reporting. Test plan: Set pref to false value. Check if option is no longer available on report's Download menu. Manipulate URL with op=export&format=ods. No data expected. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Created attachment 171141 [details] [review] Bug 23685: Don't store ODS content in a variable to save memory Just attaching this to start. Sponsored-by: Waikato Institute of Technology Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Created attachment 171142 [details] [review] Bug 23685: (follow-up) Dbrev for new preferences Test plan: Run updatedatabase. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Created attachment 171143 [details] [review] Bug 23685: (follow-up) Add prefs to Administration form Test plan: Edit both prefs. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Created attachment 171144 [details] [review] Bug 23685: (follow-up) Add export limit for guided reports Test plan: Set pref GuidedReportsExportLimit to some positive integer. Test if exporting a report respects that limit. Run t/db_dependent/Koha/Reports.t Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Created attachment 171145 [details] [review] Bug 23685: (follow-up) Control ODS exporting The pref ReportingAllowsODS_Export should be true for exporting to ODS in Reporting. Test plan: Set pref to false value. Check if option is no longer available on report's Download menu. Manipulate URL with op=export&format=ods. No data expected. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Hi Aleisha, since this adds four patches to your initial ones, please have a look if you agree (functionally and QA wise) with this approach. It is meant to allow flexibility as indicated in the feedback received. If you add your signoff line to the follow-ups, I will pass QA if nobody beats me to it. QA note: FAIL installer/data/mysql/mandatory/sysprefs.sql FAIL sysprefs_order Not blocker: Sysprefs IntranetAddMastheadLibraryPulldown is bad placed (see bug 10610) This warning shows that another report did a wrong insert. Not here :) Might be controversial... but our system preferences usually have no underscores, could we remove the one here? ReportingAllowsODS_Export (In reply to Katrin Fischer from comment #42) > Might be controversial... but our system preferences usually have no > underscores, could we remove the one here? ReportingAllowsODS_Export I agree - how about we call preference ExportFormatODS? Also I noticed that patches saying "Reporting" to refer to the module, I think this should just be "Reports" as that's the name of the module button. Finally, there's a typo in the DBrev patches - "Reporing" Created attachment 171171 [details] [review] Bug 23685: Don't store ODS content in a variable to save memory Just attaching this to start. Sponsored-by: Waikato Institute of Technology Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Created attachment 171172 [details] [review] Bug 23685: (follow-up) Dbrev for new preferences Test plan: Run updatedatabase. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Created attachment 171173 [details] [review] Bug 23685: (follow-up) Add prefs to Administration form Test plan: Edit both prefs. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Created attachment 171174 [details] [review] Bug 23685: (follow-up) Add export limit for guided reports Test plan: Set pref ReportsExportLimit to some positive integer. Test if exporting a report respects that limit. Run t/db_dependent/Koha/Reports.t Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Created attachment 171175 [details] [review] Bug 23685: (follow-up) Control ODS exporting The pref ReportsExportFormatODS should be true for exporting to ODS in Reporting. Test plan: Set pref to false value. Check if option is no longer available on report's Download menu. Manipulate URL with op=export&format=ods. No data expected. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> (In reply to Aleisha Amohia from comment #43) > (In reply to Katrin Fischer from comment #42) > > Might be controversial... but our system preferences usually have no > > underscores, could we remove the one here? ReportingAllowsODS_Export > > I agree - how about we call preference ExportFormatODS? OK. Added prefix Reports to add context. > Also I noticed that patches saying "Reporting" to refer to the module, I > think this should just be "Reports" as that's the name of the module button. Fixed > Finally, there's a typo in the DBrev patches - "Reporing" Fixed Created attachment 171571 [details] [review] Bug 23685: Don't store ODS content in a variable to save memory Just attaching this to start. Sponsored-by: Waikato Institute of Technology Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Signed-off-by: Nick Clemens <nick@bywatersolutions.com> Created attachment 171572 [details] [review] Bug 23685: (follow-up) Dbrev for new preferences Test plan: Run updatedatabase. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Signed-off-by: Nick Clemens <nick@bywatersolutions.com> Created attachment 171573 [details] [review] Bug 23685: (follow-up) Add prefs to Administration form Test plan: Edit both prefs. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Signed-off-by: Nick Clemens <nick@bywatersolutions.com> Created attachment 171574 [details] [review] Bug 23685: (follow-up) Add export limit for guided reports Test plan: Set pref ReportsExportLimit to some positive integer. Test if exporting a report respects that limit. Run t/db_dependent/Koha/Reports.t Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Signed-off-by: Nick Clemens <nick@bywatersolutions.com> Created attachment 171575 [details] [review] Bug 23685: (follow-up) Control ODS exporting The pref ReportsExportFormatODS should be true for exporting to ODS in Reporting. Test plan: Set pref to false value. Check if option is no longer available on report's Download menu. Manipulate URL with op=export&format=ods. No data expected. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Signed-off-by: Nick Clemens <nick@bywatersolutions.com> Old links to ODS exports will fail with a 500 if the pref is off, would be nice to have a warning, even just in the logs, of what happened, but not a blocker here. The patch to exclude from plack was dropped, but that seems appropriate - systems can implement it locally if needed This inspired me to work on bug 33635 - CSV export display broken diacritics in Excel I had a feeling we were just missing the BOM segment on our csv's and it looks like that was indeed the case. Well worth looking at that one too.. (In reply to Nick Clemens (kidclamp) from comment #55) > Old links to ODS exports will fail with a 500 if the pref is off, would be > nice to have a warning, even just in the logs, of what happened, but not a > blocker here. > > The patch to exclude from plack was dropped, but that seems appropriate - > systems can implement it locally if needed Could you explain a bit more about when a library would want to implement this? Pushed for 24.11! Well done everyone, thank you! (In reply to Katrin Fischer from comment #57) > (In reply to Nick Clemens (kidclamp) from comment #55) > > Old links to ODS exports will fail with a 500 if the pref is off, would be > > nice to have a warning, even just in the logs, of what happened, but not a > > blocker here. > > > > The patch to exclude from plack was dropped, but that seems appropriate - > > systems can implement it locally if needed > > Could you explain a bit more about when a library would want to implement > this? So a library could exclude from Plack if they want to have a large export of all the rows. Plack buffers the whole response in a temporary file before sending any data back to Apache, which can cause timeouts. Whereas CGI/excluded from Plack will theoretically send the data back immediately to Apache (for non-ODS exports), although I think in practice it's a bit more complicated. See bug 31744 for an inventory.pl example. So it would be addressing a timeout problem rather than a memory problem. Maybe before Kohacon2025 I can keep working on my Mojolicious controller replacement for CGI scripts which solve the timeout problem without excluding from Plack. (The ODS export is a problem regardless because of the compressed nature of the ODS format and how OpenOffice::OODoc works.) Doesn't apply clean to 24.05.x, rebase if needed. Why didn't we make it a dropdown with multi-select for the available formats instead? With a warning about ODS being dangerous? |