CPU 100% (actually over 1500% with multiple cores)

[Pierre Fortin]

Build 129.3 (2023-03-25 18:20)

PostgreSQL 15.2

Java Version: 17.0.6 (64bit)

Product Name: PostgreSQL
Product Version: 15.2
Product Info: 15.2
Driver Name: PostgreSQL JDBC Driver
Driver Class: org.postgresql.Driver
Driver Version: 42.5.3
URL: jdbc:postgresql://localhost/ncsbe
Backend PID: 2986440
Isolation Level: READ COMMITTED Username: postgres Schema: public Database (Catalog): Workbench DBID: postgresql Workbench connection: WbWin-1

Host:

Operating System: Mageia 9
KDE Plasma Version: 5.27.4
KDE Frameworks Version: 5.105.0
Qt Version: 5.15.7
Kernel Version: 6.2.12-server-1.mga9 (64-bit)
Graphics Platform: X11
Processors: 20 × 12th Gen Intel® Core™ i7-12700K
Memory: 125.5 GiB of RAM
Graphics Processor: AMD Radeon RX 6600 XT
Manufacturer: Dell Inc.
Product Name: XPS 8950

select * from <table>;

A colleague reported to me that he was getting high CPU and heap exhaustion running this query on his system.  Attached is what I got when trying the same query on my system.  The table has 8,248,674 rows with 67 columns.

Trying the same query from his system (about 70 miles away) only shows "diff" at 100% on one CPU in my system.  Maxing out almost all the CPUs only occurs when WB & PG are on the same system.

[Thomas Kellerer]

Why would you want to load over 6 million rows into a SQL GUI client? Are you going to read the values in each and every row?

The data needs to be read into memory and if you have some large strings in that table, then it's no surprise that this needs a lot of memory.

Note that the data might be kept in memory _twice_ depending on how you configured the JDBC driver:

https://www.sql-workbench.eu/manual/dbms-problems.html#pg-result-buffer
https://jdbc.postgresql.org/documentation/query/#getting-results-based-on-a-cursor

How much memory did you allocate to SQL Workbench? The JVM doesn't automatically use all available memory.
If you really want to look at 6 millions rows, you will need to increase the memory:

https://www.sql-workbench.eu/manual/install.html#install-increase-memory

Note that if you have long strings in the table, then _displaying_ those might indeed have an impact on the CPU that SQL Workbench uses.
This can be caused by displaying wrapped strings in the result table. You can turn that off in "Tools -> Options -> Data display -> Enable display of multiline value"

Regards
Thomas

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/0bc23835-b547-4d1a-ac3e-8a3a21cd30abn%40googlegroups.com <https://groups.google.com/d/msgid/sql-workbench/0bc23835-b547-4d1a-ac3e-8a3a21cd30abn%40googlegroups.com?utm_medium=email&utm_source=footer>.

[Pierre Fortin]

Just reporting observations in case it helps...  SQL-workbench/J is my preferred client; THANKS!!

On Tuesday, May 2, 2023 at 2:35:08 AM UTC-4 Thomas Kellerer wrote:

Why would you want to load over 6 million rows into a SQL GUI client? Are you going to read the values in each and every row?

I asked the same question when it was reported to me.  The response: "pgAdmin4 works fine"...

More below.

Regards
Thomas


Pierre Fortin schrieb am 28.04.2023 um 20:22:

>
> select * from <table>;
>
> A colleague reported to me that he was getting high CPU and heap exhaustion running this query on his system.  Attached is what I got when trying the same query on my system.  The table has 8,248,674 rows with 67 columns.
>
> Trying the same query from his system (about 70 miles away) only shows "diff" at 100% on one CPU in my system.  Maxing out almost all the CPUs only occurs when WB & PG are on the same system.

We expected the same issue to happen on his end when he queried the identical table on my system remotely; but it didn't happen, even after waiting long enough to acquire the entire table. 

Even if the query included WHERE etc; our data often returns huge results.  Changing the query to:

copy (select * from <table>) to '/tmp/test.csv' with csv delimiter ',' header;

gave:

8274872 rows affected
COPY executed successfully
Execution time: 15.98s

with barely a notice on CPU utilization; and output:

$ ls -l /tmp/test.csv  
-rw-r--r-- 1 postgres postgres 3034749918 May  2 06:50 /tmp/test.csv

Finally found SQLWorkbench.cfg.sample...  Set: vmarg.mem=-Xmx20g  which is way more than enough for 6.1GB expected based on previous query.

With Max Rows = 50, I got:

SELECT executed successfully
Execution time: 24ms

 

With Max Rows = 0, I get 100% on most CPUs and according to htop after cancellation, there's still ~55 java processes running, although 'ps aux' shows only the main one...  Cancelled the query since it only took ~16 seconds (above) to output to a file:

Statement cancelled.
Execution time: 1m 3s

Shouldn't 20G on a 128G system be able to handle ~3GB output..?  

I'll suggest he uses Max Rows in case he didn't...

Cheers,

Pierre

[Thomas Kellerer]

> I asked the same question when it was reported to me.  The response: "pgAdmin4 works fine"...

To my knowledge, pgAdmin loads rows in batches, so it doesn't load the whole 6 million rows.
But to support that, it needs to keep the database cursor open which I think is a not a good thing to do (and would be quite difficult to achieve in a DBMS independent way)

> Even if the query included WHERE etc; our data often returns huge results.  Changing the query to:
>
> copy (select * from <table>) to '/tmp/test.csv' with csv delimiter ',' header;
>
> gave:
> 8274872 rows affected
> COPY executed successfully
> Execution time: 15.98s
>
> with barely a notice on CPU utilization; and output:

Well, obviously this doesn't generate any CPU or memory overhead in SQL Workbench as it is processed completely inside the Postgres server itself.
And SQL Workbench does not need to retrieve, process and display 6 million rows.

> $ ls -l /tmp/test.csv  
> -rw-r--r-- 1 postgres postgres 3034749918 May  2 06:50 /tmp/test.csv
>
> Finally found SQLWorkbench.cfg.sample...  Set: vmarg.mem=-Xmx20g  which is way more than enough for 6.1GB expected based on previous query.
> With Max Rows = 50, I got:
> SELECT executed successfully
> Execution time: 24ms
>  
> With Max Rows = 0, I get 100% on most CPUs and according to htop after cancellation, there's still ~55 java processes running, although 'ps aux' shows only the main one...  Cancelled the query since it only took ~16 seconds (above) to output to a file:
> Statement cancelled.
> Execution time: 1m 3s
>
> Shouldn't 20G on a 128G system be able to handle ~3GB output..?  

3GB plain ASCII is typically a lot more when stored inside Java (especially if there are long strings involved).
And as I said: the memory consumption also depends on the configuration of the JDBC driver. It can easily be doubled untile the query is completely finished.

I have no idea why the query _also_ generates CPU load by the Postgres server.
Maybe it switches to a parallel plan which doesn't happen if you limit the number of rows:

https://blog.sql-workbench.eu/post/client-performance-considerations/