Hi,

After a few years of very satisfactory use of the SQLYog Community Edition, we decided to try out the Enterprise version. The most recent version of Community that we use is 655, the version of Enterprise we are trialling is 656, so we don't think it's a build issue.

Commands such as SQL Backup and Copy Database no longer operate. They produce the normal wait cursor, then we wait, and wait, and wait. It may be that they do eventually result in the expected dialog, but it's easier to go to another machine, fire up the Community Edition (onto the same database server) and get the usual, expected more or less immediate response.

What's going on?

I do not know what is going on! This is not reproducable for us.

Those operatins start a new connection however. Maybe the server 'connections pool' is filled upd and not more connectiosn are allowed. Can you check this, please? or you may restart the MySQL server (if you have privileges to do so).

Alternatively please try to describe in details how to reproduce this.


Important information could be:

1. What type of connection whether direct, http or ssh connection?
2. How large are the databases?


Also if you have both versions installed at the same time, is this probelm reproducable with latest only?

please also tell if your tables are InnoDB!

I think it is coincidental that you notice the change now. There was no/should not be any change since 6.5

My best guess is that this slowness occurs with databaes having large INNODB tables? It is a know INNODB issue/bug that SHOW TABLE STATUS statement is slow then. Can you confirm this?

There are 54 databases in our MySQL 5.0.45 ISAM database (not InnoDB). The total size is 2.2 GB.

Even backing up a single table with 200 rows takes for ages.

Am happy to provide you with any further info!

If you still have the old version could you try both on the same sample data at the same time?

I have both versions, not on the same machine. I am confident that if I remove Entrerprise and downgrade on my machine that these issues will go away.

I think that it's probably something to do with network resolution/mapped drives, that sort of thing. Our network isn't brilliant, but it works fine so long
as we don't 'push our luck'. In Enterprise on my PC, I created a new database on the same MySQL server [as previously reported], no problem. I wanted to restore a very small amount of data backed up from another MySQL server. I mistakenly selected Backup Database As SQL Dump, and Enterprise hangs for several seconds (can be as much as 20-30). Correctly selecting Restore From SQL Dump is instantaneous.

In other words, I don't think the 'core' MySQL performance is an issue, it's a delayed lookup/resolution that is maybe the problem.

If it continues, I'll revert anyway, and confirm that things have returned to normal.

There is no difference in how COMMUNITY and ENTERPRISE use the file system at all.
Could you try interchanging to two installations between the two machine?

No other programs have problems with storing data on mapped drives from the problematic machine?

It's not the storing of data, Peter. It's the resolution. Once the mapped network shows up ok in Explorer, we don't get any further problems. That was only additional information as a hunch, anyway.

I used 656 Enterprise this morning, still slow. So I uninstalled, and installed 655 Community. Slow also (my other hunch yesterday was wrong also, then!). I uninstalled that, and went back to the version I reckon I probably used for a longer time than the others, 527. This one is fine. (I have 604 & 615, too).

So we will have to check whether the routines we use to access file system information from Windows were changed in 6.5.

From 7.0 beta1 we use the query "show full tables" .. before that "show table status". "Show table status" can be very slow in particular with large InnoDB tables.

In 6.5 we introduced the 'tree view' in export dialogue what also required lookup for named routines, triggers etc. That gave more 'overhead' in 6.5 as compared to 6.1.

I think this is the issue - and not related to file system lookup.

Could you try 7.0 beta (or 7.0 RC that will probably be released later today)?

Just the same, Peter. Worse even.

I have attached my my.ini, and a screenshot from MySQLAdmin, if it helps.

Just for my benefit, can I have 527 Community and 656/7RC installed at the same time. It's getting a little tedious uninstalling all the time.

Sorry, didn't notice the my.ini upload failed. It is...

# MySQL Server Instance Configuration File
[client]

port=3306

[mysql]

default-character-set=latin1


[mysqld]

port=3306

basedir="C:/Program Files/MySQL/MySQL Server 5.0/"

datadir="G:/MySQL/Data/"

default-character-set=latin1

default-storage-engine=MYISAM

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=100

query_cache_size=49M

table_cache=256

tmp_table_size=52M


thread_cache_size=8

#*** MyISAM Specific options

myisam_max_sort_file_size=100G

myisam_max_extra_sort_file_size=100G

myisam_sort_buffer_size=103M

key_buffer_size=153M

read_buffer_size=64K
read_rnd_buffer_size=256K

sort_buffer_size=256K

#*** INNODB Specific options ***

skip-innodb

innodb_additional_mem_pool_size=2M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size=8M

innodb_log_file_size=10M

innodb_thread_concurrency=10

yes .. they can be installed at the same time to different folders. Only you will need to create shortcut(s) manually (shortcuts for 2nd install will overwrite shortcuts for 1st install).

and .. for security we have restricted uploads to common compressed image formats (gif, .jpg, .png), .txt and .zip.

one more idea: could you please try to turn off autocomplete from program 'preferences'?

Not sure that my response to the performance of the 7.0RC installation was prominent enough, Peter.

It was as bad as V656, possibly worse.

Well, I've installed them both, and followed your instructions about Autocomplete (I also deselected the two 'show tab' checkboxes). I restarted.

Right-clicking a selected database, and selecting SQL Backup Dump from Community 527 brought up the expected dialog in 2-3 seconds. Doing the same in Enterprise 7RC took just under 20 seconds (I was counting, not using a stopwatch, but... )

I'm back on 527. Brilliant!

Hi,

Thanks for your help, we are able to find the issue here. With latest we are executing some extra queries before populating the dialog. We are trying to optimize this. The following are the queries executing before the dialog is active, Could you please check these queries in your server and see how much time it is taking?

show full tables from `tablename` where Table_type = 'BASE TABLE';
show full tables from `tablename` where Table_type = 'view';
select `SPECIFIC_NAME` from `INFORMATION_SCHEMA`.`ROUTINES` where `ROUTINE_SCHEMA` = 'tablename' and ROUTINE_TYPE = 'PROCEDURE';
select `SPECIFIC_NAME` from `INFORMATION_SCHEMA`.`ROUTINES` where `ROUTINE_SCHEMA` = 'tablename' and ROUTINE_TYPE = 'FUNCTION';
select `TRIGGER_NAME` from `INFORMATION_SCHEMA`.`TRIGGERS` where `TRIGGER_SCHEMA` = 'tablename';

I noticed "select `TRIGGER_NAME` from `INFORMATION_SCHEMA`.`TRIGGERS` where `TRIGGER_SCHEMA` = 'tablename';" is performing very slow in 5.0.x servers when it is executed for the first time.

Yes, it is the last one...

select `TRIGGER_NAME` from `INFORMATION_SCHEMA`.`TRIGGERS` where `TRIGGER_SCHEMA` = 'gpnwl';

that is giving the problem. Running it again immediately after is just as slow, too, so I don't agree with Manoj's 'first time' comment.

Hi,
We will solve this with high priority(Actually we started working on this).

"I don't agree with Manoj's 'first time' comment." -- i am not getting it, what i meant is SQLyog is executing these queries to populate the export dialog, you can see this from the "History" tab.

I think we are saying the same thing. The delay is there when right-clicking to bring up the export dialog every time (we don't 'reuse' the export dialog once it's created, generally).

What will my upgrade/license path be for upgrading 656 Enterprise to a working ? when it's built?

And do I get a T-shirt?!:)

Hi,
We will send you the link once it is ready, it requires another 5-6 hours as this requires some code change that will affect some other modules too.

Thanks once again in helping us.

We have fixed this(backup dump performance ) issue in v 7.0 RC2 (not released) build.

To Download,
http://www.webyog.com/downloads/betas/not_...SQLyog70Ent.exe

Even 'Copy Database' also got same issue, and this haven't done in this special build. Next public release will include that fix too.

Please verify and Let us knnow the details.


Thanks

Please ignore the link above, you can take a new build from here,

http://www.webyog.com/downloads/betas/not_...SQLyog70Ent.exe

Well, you've sorted out the performance issue on options that require the 'database dialog', such as SQL Backup.

In it's place, you've got refresh issues, I think. I've got a table called jobs, with about 46,000 rows. I have a single query, with "select * from jobs" in it. I have manually scrolled down the Result tab to about 7/8 down (according to the scroll bar).

If I Alt-Tab to give another program focus, restoring focus to SQLYog (by clicking on the minimised 'button' in the ToolBar at the bottom, SQLYog come up again with the Result tab blank, then takes a few seconds to re-fill it. Doing absolutely nothing causes the same thing to happen 10 or so seconds later.

File\Exit to leave the program takes a few seconds, unexpectedly.

(I have just tried Community 527. This behaves the same way. So I guess it's acceptable and something you know about).

We can reproduce this in XP (not in Vista).
I also understand you tell that 6.5x and 7.0 has no diffference here for you?

I am not sure really if this was introduced latedly or it always was like that.

We do not find it 'acceptable' but do not consider it a 'showstopper' either.
If the fix is not trivial we will not let it delay 7.0 final release, but will try to fix shortly after.

Dan,

How about extending your free upgrade period by one year?

Thanks once again for your help. We appreciate it!