distal-attribute
distal-attribute
distal-attribute
distal-attribute

Slow Connection Startup

User, date Message
Written by koter84
7 years ago
Category: Creating a connection
15 posts since Wed, 08 Nov 06
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...
Written by ansgar
7 years ago
4790 posts since Fri, 07 Apr 06
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.
Written by koter84
7 years ago
15 posts since Wed, 08 Nov 06
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.
Written by laurios
7 years ago
1 posts since Wed, 21 Mar 07
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...
Written by rosenfield
7 years ago
127 posts since Wed, 24 Jan 07

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



Recently fixed.

koter84 wrote:
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.
Written by Kirktis
7 years ago
8 posts since Fri, 09 Mar 07
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?
Written by ansgar
7 years ago
4790 posts since Fri, 07 Apr 06
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.
Written by Kirktis
7 years ago
8 posts since Fri, 09 Mar 07
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.
Written by ansgar
7 years ago
4790 posts since Fri, 07 Apr 06
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.
Written by Kirktis
7 years ago
8 posts since Fri, 09 Mar 07
Eh, Crap happens. At least it got caught before the final release :)
Written by rosenfield
7 years ago
127 posts since Wed, 24 Jan 07

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



No, I meant exactly what I said.

Kirktis wrote: 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.

Kirktis wrote:
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.

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



Yeah, that annoys me too.

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



Doesn't sound good, you'll introduce random bugs if you do.
Written by ansgar
7 years ago
4790 posts since Fri, 07 Apr 06

rosenfield wrote:

Kirktis wrote:
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.

rosenfield wrote:

anse wrote:
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.
Written by ansgar
7 years ago
4790 posts since Fri, 07 Apr 06

rosenfield wrote:

Kirktis wrote:
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.
Written by Kirktis
7 years ago
8 posts since Fri, 09 Mar 07
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?
Written by olson
7 years ago
5 posts since Fri, 30 Mar 07
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!!!!
Written by Kirktis
7 years ago
8 posts since Fri, 09 Mar 07
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?
Written by rosenfield
7 years ago
127 posts since Wed, 24 Jan 07
See issue #1654247.
http://sourceforge.net/support/tracker.php?aid=1654247
Written by superspace
7 years ago
22 posts since Mon, 28 Aug 06
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.
Written by ansgar
7 years ago
4790 posts since Fri, 07 Apr 06

superspace wrote: 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.
Written by siMKin
7 years ago
104 posts since Sun, 01 Apr 07

olson wrote: 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 wink

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.
Written by olson
7 years ago
5 posts since Fri, 30 Mar 07
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
Written by andyr
7 years ago
7 posts since Tue, 27 Mar 07
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.
Written by ansgar
7 years ago
4790 posts since Fri, 07 Apr 06
See issuetracker:
https://sourceforge.net/tracker/index.php?func=detail&aid=1536481&group_id=164593&atid=832347
Written by olson
7 years ago
5 posts since Fri, 30 Mar 07
Yes!

Latest download version fixes the connection speed!

Thanks Anse!!!
MySQLfont 2.5 is history for me now!
Written by ansgar
7 years ago
4790 posts since Fri, 07 Apr 06
Great!
Written by andyr
7 years ago
7 posts since Tue, 27 Mar 07
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.
Written by siMKin
7 years ago
104 posts since Sun, 01 Apr 07
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
Written by andyr
7 years ago
7 posts since Tue, 27 Mar 07
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.
Written by ansgar
7 years ago
4790 posts since Fri, 07 Apr 06
See http://sourceforge.net/support/tracker.php?aid=1654247 :

wrote: 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.

Written by siMKin
7 years ago
104 posts since Sun, 01 Apr 07
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 nowsmile(according to the (very small) processlist)
Written by ansgar
7 years ago
4790 posts since Fri, 07 Apr 06
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.
 

Please login to leave a reply, or register at first.