High CPU usage

[mathieurodic]

The /usr/bin/python3 /usr/bin/pgcli postgresql://xxxxx process uses 100% of one of my CPUs, whether I'm typing commands or not.

Maybe all that energy is not required just to maintain a connection to a Postres server?

[j-bennet]

Definitely doesn't sound normal. What's your hardware and OS?

[amjith]

@mathieurodic That's surprising.

Here are the possible reasons that could contribute to this:

1. We collect metadata about a database by running a couple of SQL commands. The results are then used to populate a few data-structures in pgcli. This runs in a background thread when you launch pgcli or when you change to a new database. If the database has a lot of tables and columns (say 1000s of tables), it can be stressful on the CPU and Memory.

2. If you do have an enormous database with 1000s of tables, then when you start typing something the completion engine will have to iterate through the list of table names to narrow down the entries based on what you've typed so far.

3. If a query returns a large output with a ton of rows, then we'll warn you that you have more than 1000 rows and ask if you want to display them all. If you said yes, then we'll try to tabulate the results which can also be CPU intensive since we're iterating through all the rows to find out how to format the columns and pad the columns with spaces.

Other than those scenarios, pgcli doesn't really do much.

Does any of those scenarios match your case? Or does it drive the CPU to 100% all the time?

If you keep pgcli open for say 5 mins, does the CPU usage go down after the first few minutes or is it consistently high?

[mathieurodic]

@j-bennet I'm using Xubuntu 16.04, on an Intel i7 processor with 8GB RAM.

@amjith After more careful examination, the two processes that were each constantly using 100% CPU were both running as background processes, not related in any way to the CLI I was using at that time. As if something was left running after quitting the process.

The databases I'm using are development versions for microservices (i.e., few tables and little data).

I experience frequent disconnections with my network, could that be the origin of the bug?

What would you advise me to do to try and reproduce the bug?

[j-bennet]

One thing I can think of is completion refresher thread. It runs in the background and refreshes tables / column names and query history. I'm not sure how to verify that; I'll poke around.

[kruser]

I am seeing high CPU usage when I up-arrow through history and load/edit a multiline query. With a query that is only 40 lines long it immediately hits 100% CPU for me and I can't edit it further.

[isobit]

Could this be related to https://github.com/dbcli/pgcli/issues/994?

[oblitum]

I am seeing high CPU usage when I up-arrow through history and load/edit a multiline query. With a query that is only 40 lines long it immediately hits 100% CPU for me and I can't edit it further.

Smae here. Going back in history through little long queries makes it quite slow.

[amjith]

This has been addressed with newer version of prompt-toolkit which is a dependency of pgcli.

Can you try upgrading pgcli to the latest version and report back if this is still an issue?

Upgrade:

pip install -U pgcli

[oblitum]

@amjith any idea what version is it?

[amjith]

There were a few improvements to very large text input in prompt-toolkit. I don't know the version number and the chagelog doesn't seem to list it.

Could you try installing pgcli from master branch? There was a PR (#1109) that was merged in last night that made the multi-line query handling significantly faster.

pip install -U https://github.com/dbcli/pgcli/archive/master.zip

[oblitum]

@amjith confirmed. Just tried master and it's fixed.

[amjith]

Well done @owst :champagne: