Libre Office Base is extremely slow when sifting through the database table. I have a table with 30000+ entries which can take up to 20 minutes to sift through. Generally I can use the slider to move to the vicinity in the table where I want to be, but this freezes the table for a while. At its worst, the base table crashes and closes LibreOffice entirely, at times at the cost of the entire file. This is Linux specific as Windows does not share this problem. Previous versions (3.x) of both Open and Libre Office also had this problem, but with Linux, I "downgraded" my Java Runtime to 1.6.22 and this would solve the problem. (Actually, I would manually install the 1.6.22 version to my /opt folder and guide Libre Office to it.) Since 3.5, this no longer works either. I previously ran Linux Mint 9 with Libre Office 3.5.4 installed from the command line. I then upgraded to Linux Mint 13 with LibreOffice 3.5.2.2 as the resident office suite. Both have this problem. I ran Open Suse 11.3 on another machine and installed Libre Office 3.5.4... and same problem again. However, Windows XP and 7 do not share this, regardless of the JRE installed. I have registered with rapidshare and will upload a watered down version of the bug as soon as possible. Details to follow.
Please take a look at bug 35023; it looks like it is the same one? I *guess* you are using embedded HSQLDB, right? Then a trick to make small examples: In the Tools Menu > SQL. In the "Command to Execute" area enter "checkpoint defrag". Click the "Execute" button. (else space of deleted data is not reclaimed). Once it is small enough, you can attach the example to the bug instead of external service like RapidShare.
Created attachment 63257 [details] This is the watered down database @ about 5% of size
My apologies, but it does seem that the bug in #35023 is in relation the same as the one they mention here, though how they can say its resolved is beyond me. I can however confirm that no JDK/JRE since 1.6.22 has solved this problem (And I have downloaded 1.7 recently to test) and whatever happened in the LibreOffice 3.5x code, has significantly changed the Base / Java operation that no JRE can help anymore. I have also downloaded Open Office Org 3.4 and it does share the original bug with base, but JRE 1.6.22 still keeps base running faster on OOo. It is also not an Ubuntu specific bug, as Opensuse 12.1 and 11.3 both share it in their versions of LibreOffice/ OpenOffice. (I am the IT guy for a small company running OpenSuse 11.3 as their OS, and 12.1 on the file/ print server and all these laptops share the same problem when testing Base.) Also, the windows (WIN32) versions do not share this problem, so it seems the bug is specific to the combination of Linux / Java / Base. I tested it on a 1Ghz P3 running Windows XP pro with LibreOffice 3.4 and JRE 1.6.31 and it was significantly faster than my Core 2 duo laptop running LibreOffice 3.5.2 on Mint 13... I have done as you advised (and thanks for that tip!), and uploaded the file as an attachment to #51239 (#63259)
My apologies, but it does seem that the bug in #35023 is in relation the same as the one they mention here, though how they can say its resolved is beyond me. I can however confirm that no JDK/JRE since 1.6.22 has solved this problem (And I have downloaded 1.7 recently to test) and whatever happened in the LibreOffice 3.5x code, has significantly changed the Base / Java operation that no JRE can help anymore. I have also downloaded Open Office Org 3.4 and it does share the original bug with base, but JRE 1.6.22 still keeps base running faster on OOo. It is also not an Ubuntu specific bug, as Opensuse 12.1 and 11.3 both share it in their versions of LibreOffice/ OpenOffice. (I am the IT guy for a small company running OpenSuse 11.3 as their OS, and 12.1 on the file/ print server and all these laptops share the same problem when testing Base.) Also, the windows (WIN32) versions do not share this problem, so it seems the bug is specific to the combination of Linux / Java / Base. I tested it on a 1Ghz P3 running Windows XP pro with LibreOffice 3.4 and JRE 1.6.31 and it was significantly faster than my Core 2 duo laptop running LibreOffice 3.5.2 on Mint 13... I have done as you advised (*and thanks for that tip!*), and uploaded the file as an attachment to #51239 (#63259) Regards David On 20/06/2012 12:39, bugzilla-daemon@freedesktop.org wrote: > https://bugs.freedesktop.org/show_bug.cgi?id=51239 > > --- Comment #1 from Lionel Elie Mamane<lionel@mamane.lu> 2012-06-20 03:39:42 PDT --- > Please take a look at bug 35023; it looks like it is the same one? > > I *guess* you are using embedded HSQLDB, right? Then a trick to make small > examples: > > > In the Tools Menu> SQL. > In the "Command to Execute" area enter "checkpoint defrag". > Click the "Execute" button. > > > (else space of deleted data is not reclaimed). Once it is small enough, you can > attach the example to the bug instead of external service like RapidShare. >
Same slowness with 100% CPU loading on Windows with JRE 1.6.26
@sbergman: this a resurgence of bug 35023 (which you fixed)?
Try this with under Linux with openjdk. It works since 1.6.0.0_b24 as fast as JRE 1.6.22. All other versions after JRE 1.6.22 won't work fast enough - exept for you wont to drink a "cup of java" since you search through your data. Status isn't unconfirmed. Its an old problem. Nevertheless I change the Status to "new".
Now I have tested it another time with OpenSuSE and the JRE 1.6.29. Seems that it has been fixed in the last month. I don't have any problem with my test-database with 8000 rows and 11 fields. But there are much problems with the added database. Difference between our databases: I have a primary-key "INTEGER", nearly all other fields are "VARCHAR". You have a VARCHAR-field for primarykey. Robert
Created attachment 63967 [details] Try the queries - between 22500 and 25000 rows it's getting slow I have taken the first attachment and tried some queries. When you get a result of more than 22500 rows the queries open very slow. This problem appears with every JRE, also 1.6.u22 I have used before under Linux rpm 32bit. The CPU shows nearly 100%. The cache isn't touched and there is a lot of memory free. I will try the same with the database and a connection to mysql.
I have tried the same table in MySQL. With Base (LO 3.3.4) and the direct connection the data will be shown at once. With Base (LO 3.5.5.2) and the JDBC-Connection it's a little bit slower. I don't know, if this is a problem of the internal HSQLDB.
Tried to open the table under LO 3.3.4 - its as fast as the opening of the exported table in mysql. You can scroll to the last row at once with JRE 1.6.u22. Same JRE and LO 3.5.5.2 - opening of the table needs over 70 seconds and scrolling to the last record over 200 seconds - unusable. Must be a problem between LO 3.5.* and the internal database. It's not the old Java-problem. It seems to be new since 3.5.*
Created attachment 63990 [details] result of search
I have tested this with LO 3.6.0.0 Beta 3. It's a horror: Opening of the table last about 70 seconds, Scrolling to the last value 400 seconds. Must be a stopper for the new LO 3.6 - nobody will work with base any more with such a slow version of base. Will make a new bug-report for this.
*** Bug 51976 has been marked as a duplicate of this bug. ***
FWIW, confirming also on Mac OSX 10.7.4 Tested the following on LO 3.3.4 and LO 3.5.5 RC2 : 1) Load ODB test file from issue. 2) Select Tables in left hand pane of main window 3) Open table. 4)Wait for table to load, then click on the "go to last record" arrow button on the table grid. LO 3.3.4 : - approximately 25 seconds in all from start to finish LO 3.5.5 : - loading the ODB file takes significantly longer. - after clicking on "go to last record" button, the Mac beachball starts spinning and thereafter takes approximately 2 minutes to complete, i.e. to reach the last record in the data set. Setting platform to All. Alex
(In reply to comment #13) > I have tested this with LO 3.6.0.0 Beta 3. It's a horror: Opening of the table > last about 70 seconds, Scrolling to the last value 400 seconds. Must be a > stopper for the new LO 3.6 - nobody will work with base any more with such a > slow version of base. > Will make a new bug-report for this. FWIW, I gave up trying to scroll down on the Mac in 3.5.5, doing so sends the machine into a continuous loop/spinning beachball cycle. Alex
(In reply to comment #15) > FWIW, confirming also on Mac OSX 10.7.4 > > Tested the following on LO 3.3.4 and LO 3.5.5 RC2 : > More info with 3.5.5, using the Mac graphical hang sampler : Loading table Sales Invoices by double-clicking : 39.62 seconds Goto last record : exceeds 57 seconds, sampler times out, maximum sample time reached. Call graph generated by Mac sampler enclosed. Alex
Created attachment 64101 [details] call graph generated by mac sampler
Hmm, preliminary analysis with Instruments.app seems to indicate that LO leaks memory massively when trying to load the table. Alex
I had reported a bug, which should show a difference between LO 3.5.* and LO 3.6 Beta 3: https://bugs.freedesktop.org/show_bug.cgi?id=51976 This bug has been marked as a duplicate of 51239. When you write here, that it's extremely slow, you will get a shock if you try to open a table like the examples above in 3.6. Beta 3. For everybody who will use base 3.6 Beta 3 is not usable so. But I can't change the Importance of this bug, because it is reported for LO 3.5.*. What should we do?
Hi Alex, the profiler report looks rather interesting (thanks so much for trying all these tools at LibreOffice !). Having said that - its' hard for me to see where the profiler thinks most of the time has gone ... is there any %age estimation / call-count or absolute time measurement in there to use ? I just saw traces. sberg - IIRC Lionel is fairly convinced this is a Java stack-guard slowness, and I guess we're hoping to have that ruled out before digging much further. I believe we fixed that - right ? There is a whole lot of: 1 uno_Environment_invoke (in libuno_cppu.dylib.3) + 37 [0x2d3c7f] + ! : | + : 1 uno_Environment_invoke_v (in libuno_cppu.dylib.3) + 28 [0x2d3c58] + ! : | + : 1 uno_Environment_enter (in libuno_cppu.dylib.3) + 326 [0x2d3c1c] + ! : | + : 1 uno_EnvDcp_getPurpose (in libuno_cppu.dylib.3) + 4215 [0x2d2dee] + ! : | + : 1 uno_Environment_invoke (in libuno_cppu.dylib.3) + 155 [0x2d3cf5] + ! : | + : 1 uno_initEnvironment (in libaffine_uno_uno.dylib) + 903 [0x1ebdd417] (assuming the symbols are remotely reasonable). Is it possible that there is some horrible leak / performance problem / re-marshalling in the inter-apartment code ?
(In reply to comment #21) > sberg - IIRC Lionel is fairly convinced this is a Java stack-guard slowness, At the time of comment 6, this looked like the best guess. Information in later comments seems to suggest that maybe not, or at least a *different* stack-guard problem than bug 35023, but it could also be a *different* Java <-> C++ interface problem. I've a few ideas why it could be slow in general, but none of them explains why this is a regression in 3.5 with respect to 3.4: - HSQLDB I/O is redirected to our ZIP-file structure; unless we cache the whole thing, this must be problematic if HSQLDB tries to read the *end* of the file or write to the *begin* of the file: the whole file has to be (de)compressed. This happens in connectivity/com/sun/star/sdbcx/comp/hsqldb, git log suggests no change since Oct 2010 there! - There seems to be codepaths where information that has *just* been fetched is "refreshed" (and thus fetched again) a fraction of a second later. But even if there were a regression in that in 3.5, that would be a factor 2 or max 5, not the horrible times seen here.
(In reply to comment #21) > Hi Alex, the profiler report looks rather interesting (thanks so much for > trying all these tools at LibreOffice !). Having said that - its' hard for me > to see where the profiler thinks most of the time has gone ... is there any > %age estimation / call-count or absolute time measurement in there to use ? I > just saw traces. > Hi Michael, Errm, probably, but I'm still trying to understand how to use the tool in the first place :-) Apple's Instruments.app is a nice graphical inspection tool, but it seems that you can only save the information as a bundle which can then only be read by someone else if they have the same tool. It does all sorts of wonderful time slice analysis and can even link that to the underlying source code to get to the calls that appear to be causing the problem. I will have to try it out with a version of LO from master and my git sources to see if I can make anything sensible/useful out of it. > (assuming the symbols are remotely reasonable). > > Is it possible that there is some horrible leak / performance problem / > re-marshalling in the inter-apartment code ? In a graphical malloc trace, the loading of the database file causes a significant and constant increase in memory consumption, and this actually caused both the Instruments.app and LO to to crash in the end as all system resources were finally consumed. I will try and post some screen captures to see if the information I see on screen can bring anyone any further. Alex
(In reply to comment #23) > (In reply to comment #21) >> (assuming the symbols are remotely reasonable). >> Is it possible that there is some horrible leak / performance problem / >> re-marshalling in the inter-apartment code ? > In a graphical malloc trace, the loading of the database file causes a > significant and constant increase in memory consumption, and this actually > caused both the Instruments.app and LO to to crash in the end as all system > resources were finally consumed. This looks like it could be some horrible error in my recent work on the in-memory DB cache. If one can pinpoint me to the source of the leak, it could give me a clue. It could be a few weeks before I can actually run valgrind on LibreOffice; I'm currently away from my development machine. FWIW, I've tried running with GDB, it shows similar horrible memory leak; strace shows it constantly reads from some temporary file in /tmp...
Created attachment 64246 [details] valgrind on 3.6 branch On pc Debian x86-64, with 3.6 branch updated today, I reproduced the bug with Timbabase.odb I attached the valgrind logs. I tried to run the query "First_25000" but never had the result with or without Valgrind. gcc version 4.7.1 (Debian 4.7.1-2) java version "1.6.0_24" OpenJDK Runtime Environment (IcedTea6 1.11.3) (6b24-1.11.3-2) OpenJDK 64-Bit Server VM (build 20.0-b12, mixed mode) If needed, I got 3.5 branch and master sources too, both with also symbols enabled.
FWIW on Mac OS X.6 in LibO 3.5.5, I'm able to move to the end a large database table (20,000+ records, 25+ columns) in about 7 seconds when connecting in FILE MODE (using HSQLDB 2.8). But note, it takes only about 4 seconds when using LibO 3.4.6. So the current version of LibO is usable in FM though performance somewhat worse.
(In reply to comment #25) > I attached the valgrind logs. Such a valgrind memcheck log has little to no value here. With an in-process JVM memcheck is known to produce false positives, and I see nothing interesting apart from those in the log.
OK, I think I have a handle on this. I ran this under callgrind, let it run for 24h (yes, one full day and night!) and interrupted it. This got me started that it takes a lot of time in connectivity::java_sql_PreparedStatement::executeQuery(), but the backtrace is unusable because this is a UNO API call executed in a fresh thread. However, I've a good guess at where it is called from because of my knowledge of the code and the shape of the query. Following that trail...
OK, *one* horrible performance problem was introduced by the fix to bug 47520, but this cannot be the bug originally fixed here. Hijacking *this* bug to mean the bug introduced in 3.5.5 by the fix to bug 47520, and cloning for the original bug.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-3-6": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ba20c9942183210e69187d8e29e77360f965aede&g=libreoffice-3-6 fdo#51239 refresh row lazily (when data is requested) It will be available in LibreOffice 3.6.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=1e89c6f3d1edee7d112fc2549d2f6ad16e65659e fdo#51239 refresh row lazily (when data is requested)
Just for comparison with different JVMs : Linux Mint 12 64bit, LO 3.4.4 64bit Jvm 1.6_24 Opens ODB in approx 22 seconds Goes to end of resultset in 6 min, maxing out processor occupation (noted via top) Jvm 1.6_26 Opens ODB in approx 30 seconds Goes to end of resultset in 6 min, maxing out processor occupation (noted via top) Oracle openJDK 1.7.0 Opens ODB in approx 2 min Took more than 16 minutes to move to end of resultset, gave up and killed process, maxed out processor occupation Alex
Hi *, IMHO this thread has become somewhat confusing (which should not be a negative criticism - on the contrary: I appreciate your efforts very). I would like to suggest a summary. 1) What has already been fixed (see commen 30 and 31)? 2) What is currently being processed? 3) What is still to be processed? 4) Still to be tested for something?
(In reply to comment #33) > 1) What has already been fixed (see commen 30 and 31)? A performance regression was introduced in 3.5.5 and 3.6.0.alpha. It has been fixed in 3.7.0.alpha, 3.6.0.rc. It is awaiting backport to 3.5.6.beta. > 2) What is currently being processed? Backport to 3.5.6.beta > 3) What is still to be processed? I don't understand the difference with question 2) > 4) Still to be tested for something? AFAIK, only I have confirmed that my fix makes things faster. Independent confirmation is always nice, but not necessarily a priority to assign scarce resources (QA/tester time). Whether I did or did not introduce a new bug is also an interesting question :)
(In reply to comment #34) > > 2) What is currently being processed? > Backport to 3.5.6.beta > > 3) What is still to be processed? > I don't understand the difference with question 2) Hi Lionel, 2 = past = what have (or somebody else) done 3 = future = what will you (or somebody else) do
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-3-5": http://cgit.freedesktop.org/libreoffice/core/commit/?id=186366967e7d57e1bd539a43c7383405adb8a575&g=libreoffice-3-5 fdo#51239 refresh row lazily (when data is requested) It will be available in LibreOffice 3.5.7.
Not sure if this info is relevant now, but FWIW, moving to the end of a large table (20,000+ records, 25+ columns) on Mac OS X.6 seems to be much worse with the current LO version: 84 seconds - LO 3.6.0.4 7 seconds - LO 3.5.5 4 seconds - LO 3.4.6 Note: I'm connecting to the local DB in FILE MODE (using HSQLDB 2.8)
Marking as fixed since it is now backported to all relevant branches. (In reply to comment #37) > Not sure if this info is relevant now, but FWIW, moving to the end of a large > table (20,000+ records, 25+ columns) on Mac OS X.6 seems to be much worse with > the current LO version: > 84 seconds - LO 3.6.0.4 > 7 seconds - LO 3.5.5 > 4 seconds - LO 3.4.6 > Note: I'm connecting to the local DB in FILE MODE (using HSQLDB 2.8) This is probably bug 51976 (which is not fixed yet).
*** Bug 53126 has been marked as a duplicate of this bug. ***
Hi Guys I have noticed you closed this with answer "resolved"? However, I downloaded Libreoffice 4.0.1 and this bug still seems commonplace. The Timbabase database for sales alone is now in excess of 40,000 entries and I still have a long waiting period. So it has been back to LO 3.4 for me and JRE 6.22. Running MInt 13 32bit (kde) on a Lenovo G570 laptop (B1500 CPU) with 3 GB RAM (DDR1333).
(In reply to comment #40) > I have noticed you closed this with answer "resolved"? > However, I downloaded Libreoffice 4.0.1 and this bug still seems commonplace. > The Timbabase database for sales alone is now in excess of 40,000 entries > and I still have a long waiting period. There were multiple slowdowns. This bug is about the slowdown that appeared in 3.5.5 and 3.6.0.beta, which AFAIK is resolved. That is, 3.5.7 should be as fast/slow as 3.5.4 now. There was another slowdown that also appeared in 3.6.0.beta, which is now also fixed, that is bug 51976. There is yet another slowdown, which appeared in 3.5.2 (or earlier, not sure), that is not resolved, and that is bug 52170. But I'm missing information to work on bug 51239. If you see that recent LibreOffice versions are still slower than e.g. 3.4, please go to bug 52170 and give detailed information *there*. Please compare *exactly*, saying exactly what operation, with what version of LibreOffice and Java, with timings (say e.g. "33s" or "8 minutes and 20s", not "a long waiting time"). I need comparisons of LibreOffice 3.4, 3.5.4, 3.5.7 and latest 4.0: - LibreOffice 3.4 because it is the "reference": it was fast - LibreOffice 3.5.4 to compare *without* the impact of this bug. - LibreOffice 3.5.7 to check if it is the same time as 3.5.4 - LibreOffice 4.0.latest to check if it is the same as 3.5.4, or if a *new* performance problem has been introduced.
Thank you Lionel. My bad, sorry.