Posted 23 March 2012 - 04:52 PM
SHOW TABLE STATUS FROM `database` WHERE ENGINE IS NOT NULL;
On some of our smaller databases this hangs SqlYog for 10-15 seconds, however some of our larger databases it will hang the application for minutes.
Is there any way to have this tab not display when opening a connection? We have tried editing the .ini file but it has to be done on a per user basis.
Using Enterprise 18.104.22.168 mysql 5.1-
Issue only occurs when a user connects to the database server, then chooses a specific database from the structure tree, then clicks the info tab.
Posted 23 March 2012 - 07:37 PM
1) Please try to CHECK TABLE or REPAIR TABLE for tables where this slowness appears. The slowness is a server issue. SQLyog simply sends a few trivial SHOW queries.
2) What is the exact MySQL 5.1 version you are using? A Mysql version has three digits x.y.x (like 5.1.62 - the latest 5.1). Execute "SELECT VERSION();" if you are not sure. If it is not at least 5.1.60 or close to it please upgrade the server. Please refer: http://www.webyog.co...-supported.html (and the recommendation 5.1.35 or higher should probably be raised now!). You may also need to dump data and import if this is an issue with an early 5.1 server.
3) Are affected tables MyISAM tables or InnoDB tables? If InnodDB do you use the 'built-in' InnoDB or the 'InnoDB plugin'?
4) How many rows are in affected tables?
5) Are you sure the query executes when closing the INFO tab? It does not for me - so probably I don to understand what you are trying to tell here.
6) Do you see any change in behavior with 9.63 as compared to previous versions? If so what was your previous version?
If you close the INFO tab and next close SQLyog, the INFO tab will not open next time you open SQLyog.
Posted 23 March 2012 - 10:43 PM
2) We are running 5.1.49sp1
3) MyISAM tables
4) We are working with a ~4tb database with hundreds of thousands of tables and hundreds of millions of rows.
5) Load a connection to your server and open the History tab and the Info Tab, Select a database to connect to from the drop down above, then click the close on the INFO tab, then check the history. I will attach a screen of what happens (partial view due to security).
6) We are using 9.33 GA company wide and currently enforce INFO tab to be off due to the Issue found and fixed in 9.6.1 - This issue with the info tab is what is preventing us from upgrading. We previously used 9.3 and it had an option within the settings to disable the INFO Tab. 9.33 would allows us to make sure the INFO tab was turned off by configuring it through the .ini file.
"If you close the INFO tab and next close SQLyog, the INFO tab will not open next time you open SQLyog."
Yes, but this has to be done for every user, on every connection they open. We need this to be already disabled by use of a .ini file that we distribute.
**For the image this was done on our development server, but is present on all other servers, Development and production**
For the image all blank spots are the database name. I connected to the server, I selected a database, then i clicked on the red X for Info tab and it launched all queries after the Use ' ' command.
Posted 24 March 2012 - 10:23 AM
With MyISAM tabels I have seen recommendataion many tiems that a REPAIR TABLE should be scheduled regularly (some recommedation I've seen say weekly). But I cannot judge myself. If there are (almost) only INSERTs to the table (so that the tablespaces grow 'at the end' I don't think so), but if there are DELETES and INSERTS randomly I believe the tablespace could easily fragment. REPAIR TABLE will fix that - but if will also LOCK the table and it will effectively go offline for as long as the statement runs.
I think you should try (when it is possible) to execute SHOW TABLE STATUS LIKE `table` FROM `database` WHERE ENGINE IS NOT NULL; .. next REPAIR all tablles in the database, next run the SOW statement again and see if speed has improved. It could alos have good efffect on the applications.
But I agree that SQLyog should not depend on that. Hiding the INFO tab is one option. Optimizing queries could be another. We will discuss after the weekend
Posted 24 March 2012 - 10:33 AM
Posted 24 March 2012 - 01:50 PM
"OPTIMIZE TABLE is used to remove overhead, sort indexes for better access, and generally keep your tables in good order ó but is often overrated and used more often than necessary. Iíve seen a lot of people using OPTIMIZE TABLE daily or even hourly, but that is generally not needed, unless you do massive amount of INSERT and/or DELETE on a few tables, and even then daily or weekly will be enough ó just remember to run it only on the affected tables."
But I have to admit that I am not really able to evaluate such information myself. †
Posted 28 March 2012 - 04:50 PM
Posted 28 March 2012 - 07:00 PM
Also I do not understand that it worked in 9.33. What did you do wirh 9.33 to achieve this?
Posted 28 March 2012 - 11:52 PM
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users