Slow Connection Startup

Hi All,

I installed HeidiSQL 3 RC4 and i find that the connection time has really slowed down a lot. Starting up the program is just as quick as always, but when connection to a server it gets slow.
The first couple of arguments go rather quick, but when these commands come around waiting time starts:

SHOW FULL PROCESSLIST
SHOW DATABASES
SHOW TABLES FROM table1
SHOW TABLES FROM table2
SHOW TABLES FROM table etc

It's probably this slow because every table gets checked out and added to some temp-file for quicker access. But when i connect to a server to find some data i don't need all tables to be added to the temp-file. Since there are 50+ DB's and more than just a couple of GB's on the server this takes quite some time. Would it be possible to change this back to the old update when needed style?

P.S. Perhaps it's also possible to save the 'Show All' choice when there are more then 50 DB's since i now have an extra click every time...

Hmm. I cannot reproduce that slowness effect, both versions RC3 and RC4 need the same couple of seconds for the first statements. When I start RC3 I see these log-entries:

/* CONNECT TO "" AS USER "root" */
/* Connection established with host "localhost" on port 3306 */
SELECT VERSION()
SHOW VARIABLES
SHOW STATUS
SHOW PROCESSLIST
SHOW DATABASES
SHOW TABLES FROM `information_schema`
SHOW TABLES FROM `,.#|test`
SHOW TABLES FROM `ada`
SHOW TABLES FROM `anse_de`
SHOW TABLES FROM `cluster`
SHOW TABLES FROM `mysql`
SHOW TABLES FROM `test`

With RC4 I see these lines

/* Connection established with host "localhost" on port 3306 */
SELECT VERSION()
SHOW VARIABLES
SHOW /*!50002 GLOBAL */ STATUS
SHOW FULL PROCESSLIST
SHOW DATABASES
SHOW TABLES FROM information_schema
SHOW TABLES FROM `,.#|test`
SHOW TABLES FROM ada
SHOW TABLES FROM anse_de
SHOW TABLES FROM cluster
SHOW TABLES FROM mysql
SHOW TABLES FROM test
SHOW ENGINES
SHOW DATABASES

The most notable difference are the both last 2 additional queries. But these shouldn't take more than a second on a server which is located in your LAN.

How long does HeidiSQL RC4 take roughly for startup on your server?

The amount of gigabytes which is held by the tables doesn't matter for performance in HeidiSQL. Indeed a large number of single tables does matter here, because the SHOW TABLE statements will surely take longer if they get more tables.

Simple workaround for you: Choose one or some more of the databases in the initial screen where you are warned about the large number.

This is my full output in RC4

/* Connection established with host "192.168.xxx.xxx" on port 3306 */
SELECT VERSION()
SHOW VARIABLES
SHOW /*!50002 GLOBAL */ STATUS
SHOW FULL PROCESSLIST
SHOW DATABASES

The part above takes about 6 sec. after which i get the question if i would like to show all tables.

SHOW TABLES FROM 01
SHOW TABLES FROM t/m
SHOW TABLES FROM 82
SHOW DATABASES

The code just above this text takes about 15 sec. which seems really much longer than it took under RC3. (from my perspective, i have never timed the same part in RC3).

If it doesn't give any problem i guess i will install RC3 over RC4 and time it again. I will then post my findings here.
But if there aren't any changes in the code for that function i don't know why it seems to take longer.

Hi! We had the same problem, that RC4 takes much longer than RC3 for the startup. This ist because RC4 uses the command "SHOW FULL PROCESSLIST whereas RC3 only uses "SHOW PROCESSLIST".

We found out,that some of our servers acted that slowly whereas others didn't. The difference was that the variables "key_buffer_size" as well as the "max_allowd_packege" were set to small, so somehow the query took very long. Increading the size to 100 MB helped!
And the great HeidiSQL was fast again...

I installed HeidiSQL 3 RC4 and i find that the connection time has really slowed down a lot.

Recently fixed.

P.S. Perhaps it's also possible to save the 'Show All' choice when there are more then 50 DB's since i now have an extra click every time...

Sounds reasonable, popups are annoying.

It's actually quite fast in the next version, so perhaps the 'select databases' dialog should just be turned into a filter feature accessible via right-click on the db tree.

By 'recently fixed', I think you mean the opposite.

The startup time slower by a factor of at least 3 or 4 in RC5, add in the fact that you no longer see the status messages scrolling by (instead the whole window turns kind of grey and you start thinking the program has crashed), and it feels like an eternity.

Previous startup time: 4.29 secs
Current startup time (average of three): 14.68 secs

I also find the fact that the new version 'remembers' the last database I was using highly undesirable. Is there a way to disable this?

It's obvious that you count the time HeidiSQL needs for selecting the last used database to the startup-time, otherwise I couldn't imagine how this performance-slowdown should take place.

The remembering of the last used DB was intended as a comfort-feature. We could make it an option which is by default on, how does that sound?

About the window turning grey on startup: could be that we need to add a Application.Processmessages somewhere. I will check that.

I am counting the time from when you click 'Connect' until the database list in the left pane is displayed. The delay for it to select the database 'appears to be' about 3 seconds. I say 'appears to be' because it is possible that it starts this before the db list is displayed and part of it is included in the connection time I am counting.

Regardless of that though, 14 seconds is a long time for the user to stare at a window that looks like it may have crashed. Many people are low on patience and will just kill the app.

Making the last-used-db a feature that is on by default seems fine to me (I'm sure many users will like this), but I do recommend an option to disable it as some users work with many db's on the same server.

I just see that I had selected the Filter-tab instead of the SQL-log tab while compiling the RC5-binary. So I didn't have a clean copy of all sources at this point. Damn. If I activate the SQL-log at design-time, the log-lines are visible during startup-time and HeidiSQL doesn't look like it has crashed.

I will definitely avoid making such crap in any future-release.

Eh, Crap happens. At least it got caught before the final release :)

By 'recently fixed', I think you mean the opposite.

No, I meant exactly what I said.

The startup time slower by a factor of at least 3 or 4 in RC5

Whether the fix got in RC5 by means of 'svn update' or not, I cannot really tell, since I didn't produce the release.

Previous startup time: 4.29 secs
Current startup time (average of three): 14.68 secs

Is this random numbers, or is it something you can repeat?
If it's repeatable, post an issue in the tracker (as instructed in the email you received) along with a reproduction recipe, and it'll be fixed ASAP.

I also find the fact that the new version 'remembers' the last database I was using highly undesirable.

Yeah, that annoys me too.

could be that we need to add a Application.Processmessages somewhere

Doesn't sound good, you'll introduce random bugs if you do.

I also find the fact that the new version 'remembers' the last database I was using highly undesirable.

Yeah, that annoys me too.

OK so I'll make it an option for the 3.0 final.

could be that we need to add a Application.Processmessages somewhere

Doesn't sound good, you'll introduce random bugs if you do.

I already nuked that idea. The SQL-log is repainted everytime a new line comes in and the error was that the wrong tabsheet is active at startup so if I clean the sources next time when compiling we won't see this error again.

I also find the fact that the new version 'remembers' the last database I was using highly undesirable.

Yeah, that annoys me too.

Done: Option is available in revision 542.

Ok, I know it's probably obvious, but I can't find the link to the tracker.
:oops:

I'm also curious if anyone other then me is able to reproduce these long startup times. I have tried it in a wide variety of cases, but maybe there is something specific to ie: my windows config that is causing this, if other people are unable to reproduce?

Anse,
Why the show tables cmd at connect.

Normally you are only interested in DB names

so: show databases

then I select the requested DB.
then show tables for onley selected DB.

I LOVE the speed in mysqlfront 2.5. Please change this behavior!!!!

Olson makes a very good point. The 'Show Tables From *' is especially redundant since as soon as you select a DB, it is doing a SHOW TABLE STATUS with no LIKE. Basically, it uses a bunch of connection time and the data serves no purpose (from the end user perspective). Is there a reason for this that we are missing?

See issue #1654247.
http://sourceforge.net/support/tracker.php?aid=1654247

This is one of my pet-annoyance with Heidi... while i love some of the new features... search/filters, sometime I open up mysql front using the same connection and mysqlfront2.5 just feels so snappy. Heidi on the other hand seems to be 'thinking' too much :)

Its good to hear this issue being worked on.

anse, not sure what you mean by the filter-tab... I haven't seen rc5 yet. Does that allow you to open database tables say starting with "heidi_" by using a wildcard chararcter like this "heidi_*"? If thats the case, that sounds awesome.

anse, not sure what you mean by the filter-tab...

The filter tab can be found at the very bottom when you're browsing a table's data. It can contain WHERE-filters.

Anse,
Why the show tables cmd at connect.

[...]

I LOVE the speed in mysqlfront 2.5. Please change this behavior!!!!

MySQLFront 2.5 is also performs the SHOW TABLES at the beginning

Personally i don't have any performance issues with that (but see how it could be with many databases)
For me, only the SHOW FULL PROCESSLIST seems to take a huge amount of time.

oke
my database test:

connecting with mysql front 2.5: 35 dbs loading in 2.8 sec.
connecting with heidqsql relrc4: 35 dbs loading in 24 sec.

that's too much!

*ps each db has approx. 130-180 tables, and the show table status is also very slow on each db when requesting the selected db

Hi, bit more info:

SHOW FULL PROCESSLIST seems to use a large amount of memory on my machine:

1) Open up windows task manager and look @ heidisql, memory approx 4MB
2) In query pane, run "show processlist" - press F9 as many times as you like, no/little mem increase
3) Run "show full processlist", mem usage jumps to 200MB on my box. Press F9, it decrease to ~4MB before jumping upto 529MB!

If you watch task manager as connecting to server, you see the jump up to ~80MB, then back down again to ~4MB. So slow start time looks to be down to time it's taking to allocate memory.

Seems to be problem with amount of memory used with "show full processlist". Anybody else get same symptoms? I'm using RC4.

See issuetracker:
https://sourceforge.net/tracker/index.php?func=detail&aid=1536481&group_id=164593&atid=832347

Yes!

Latest download version fixes the connection speed!

Thanks Anse!!!
MySQLfont 2.5 is history for me now!

Great!

Unfortunately it doesn't seem to have resolved the problem for me. I am using heidi sql 3.0 final (r572) but I still get the large mem usage when running 'show full processlist'. Connection startup doesn't seem too bad though.

same here
it seems to depend on the server (to which i'm connecting) though

- Linux, Apache 1.3.27, mysql 4.0.13 : fast
- Linux, Apache 1.3.37, mysql 4.0.23 slow
- Linux, Apache 2.0.54, mysql 4.1.11 : slow
- OSX, Apache 2.2.3, mysql 5.0.27 : fast

I agree, I have same results with similar versions:

mysql 5.0.17, max mem usage ~8MB
mysql 4.0.18, max mem usage ~800MB

Server is running on physically seperate machine to the heidisql client.

Server: Linux, Client: Vista x64 (tho XP 32bit has same problem, tho it uses less mem!)

I have added this info to the tracker.

See http://sourceforge.net/support/tracker.php?aid=1654247 :

Done in revision 588: "SHOW TABLES FROM xyz" are now fired when you expand a database node in the treeview. Means: on demand, not in advance, which is mostly a good strategy for performance optimisations.

Next optimisation for startup will be to move the SHOW FULL PROCESSLIST to a place where it's only called on demand.

It's still strange though, that SHOW FULL PROCESSLIST seems to be causing the big delay with some servers.
It's not such a big deal for mysql and indeed: when i open an ssh connection and do the query from the mysql command line on the (slow) server, it takes less then a second. So the command itself can't be the cause and neither the amount of data that needs to be sent, since i'm the only one making use of the database right now(according to the (very small) processlist)

Yes it has definitely nothing to do with the server directly. See https://sourceforge.net/tracker/?func=detail&atid=832347&aid=1536481&group_id=164593

Seems that andyr1979 found a new bug in Zeos which does some different memory reserving on TEXT-columns and BLOB-columns. We will have to dive deeper in the callstack to find a fix for that (and we definitely will do so!). The official Zeos-bugtracker at http://zeosbugs.firmos.at/ doesn't contain this bug yet. We should report it there, as soon as we can be sure that it's located in Zeos.