Product: DbVisualizer Pro 9.1.9
Build: #2154 (2014/06/16 16:42)
Java VM: Java HotSpot(TM) 64-Bit Server VM
Java Version: 1.7.0_65
Java Vendor: Oracle Corporation
OS Name: Windows 7
OS Arch: amd64
OS Version: 6.1
I currently have set -Xmx4096M which translates to 2730M (in the bottom right hand corner of the UI - which doesn't make any sense to me) . Using postgressql to extract from my DB a dataset that is at least 800MB in size I keep running out memory and cannot view anything in the GUI.
So after reading the forums, and user guides there are two options. First is to log to file and the second is to run via command.
Unfortunately the running the stuff via the command interface is beyond my keen (tried but failed miserably).
So that leaves the only, and quite simple, option of log to file. So following the very simple instructions in your userguide I wrote a path, the name of my file and even used the suggested extension (.log, though i tried .csv and .txt as well).
I run my query and saw that it instantly made chugs.log file. Unfortunately after the 40 minutes of running it had stayed at 1KB and the system came back and said it had run out of memory.
So what am I do wrong. What switch, setting, value am I missing that I need to push to get Log to File to actually log my data to file.
I tried different -xmx values but DBvis simply stopped opening requiring me to edit the dbvis-multi.vmoptions file.
Now on the presumption that its using RAM, and I have a 8GB machine with a 11GB virtual disk why can't i set my Dbvis memory to say -Xmx8192?
How much memory can be used for the heap depends on the OS and the processor architecture. For instance, on Windows x86, it is far less than the installed memory. This FAQ describes it in more detail:
Hmmm the SQL has some secret squirrel stuff that i might not be allowed to share.....anyway before I do that can you address one question:
When i type in -Xmx8096 i get a Dbvis error that the heap size is too big despite having 16GB of ram now. I can only get it to work at -Xmx4096 which translates to 2730m in usable memory.
Why can't I use -Xmx8096. Or is 2730m the maximum that the Dbvis UI can show?
Neither the user guide for DBVis or any other documentation/forum/website/clay tablet/manual indicate the different values for -Xmx (even 4096).
Please open Tools->Debug Window and enable debugging for DbVisualizer. The use Help->Contact Support to report the problem, and make sure that Attach Logs is enabled.
Thank you for the quick reply.
I've already tried the @export on option (per advice from the user guides) however for some bizarre reason in the last part of my SQL statement it reports an error:
10:54:07 [@EXPORT - 0 row(s), 0.003 secs] Command processed
10:54:07 [@EXPORT - 0 row(s), 0.000 secs] Command processed
10:54:07 [SELECT - 0 row(s), 0.000 secs] [Error Code: 0, SQL State: 42601] ERROR: syntax error at or near "off"
Position 2265 is the last value in my GROUP BY part of my statement the "itemdescription.name" part.
date_trunc('day',ontime at time zone 'UTC')::date,
date_trunc('seconds' ,(ontime at time zone 'UTC')::time),
extract(epoch from ontime at time zone 'UTC'),
ORDER BY 3,4
When i remove the @export bits my statement works fine (on smaller amounts of data) however when I add it, regardless of the data i'm extracting it shoots back the aforementioned error.
Regarding the suggestion to change version unfortunately that decision is well beyond my pay grade. An option I can't really explore.
I assume you are looking to fetch a data set with 800MB and present it in the DbVisualizer user interface. That is generally a bad idea due to the memory constraints you've already run into. It is not the log that consumes memory, but the data set.
A better option is to export the data set to file using the @export client side command. Here is a basic script to be executed in the SQL Commander:
@export set filename="c:\Backups\Orders.csv";
select * from Orders;
It exports the results from the SELECT to the Orders.csv file. There are many options for the @export set command such as formats in CSV, HTML, XML, SQL, XLS, JSON. Read more in: http://confluence.dbvis.com/display/UG91/Exporting+Query+Results
I see also you are using DbVisualizer 9.1.9 which is quite old. A better option is to upgrade to the current 9.5.x version which is soon to be replaced by DbVisualizer 10.0. Here you find what has happened in DbVisualizer since 9.1.x:
and the upcoming 10.0: