Jump to content


Photo

Info Tab


  • Please log in to reply
8 replies to this topic

#1 WilandTech

WilandTech

    Member

  • Members
  • PipPip
  • 14 posts

Posted 23 March 2012 - 04:52 PM

Hi, we are having an issue where when clicking on the Info tab whether we wish to close it or view it, it will launch the query of:
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 9.6.3.0 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.

#2 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 23 March 2012 - 07:37 PM

"SHOW TABLE STATUS FROM `database` WHERE ENGINE IS NOT NULL;" is one of the queries that populate the INFO tab for a table




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.
Computers make your grey hair come off ....

Peter Laursen
Webyog

#3 WilandTech

WilandTech

    Member

  • Members
  • PipPip
  • 14 posts

Posted 23 March 2012 - 10:43 PM

1) We cannot do this. We have to schedule outage windows in order to do any maintenance and this slowness appears on every server.

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.

Attached Files



#4 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 24 March 2012 - 10:23 AM

OK .. that server should definitely be fine.

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


Computers make your grey hair come off ....

Peter Laursen
Webyog

#5 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 24 March 2012 - 10:33 AM

Another way to check if tablespace fragmentation is the basic problem here is to dump the database and import to another database/server. Next compare speed of statement on the two databases.


Computers make your grey hair come off ....

Peter Laursen
Webyog

#6 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 24 March 2012 - 01:50 PM

REPAIR TABLE seems to be overkill. OPTIMIZE TABLE should do. I came across this: http://www.pythian.c...ize-and-repair/

"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. †


Computers make your grey hair come off ....

Peter Laursen
Webyog

#7 WilandTech

WilandTech

    Member

  • Members
  • PipPip
  • 14 posts

Posted 28 March 2012 - 04:50 PM

We already maintain the database and we cannot run OPTIMIZE or REPAIR without major issues. Is there a way to completely disable the INFO tab from opening? We have tried through the .ini file but that doesn't work anymore in 9.6.3 as the INFO tab opens on a user's first connection. It worked in 9.33 to disable the tab though.

#8 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 28 March 2012 - 07:00 PM

There is no way currently. Except for closing it manually for every connection so that it will not open again.

Also I do not understand that it worked in 9.33. What did you do wirh 9.33 to achieve this?


Computers make your grey hair come off ....

Peter Laursen
Webyog

#9 WilandTech

WilandTech

    Member

  • Members
  • PipPip
  • 14 posts

Posted 28 March 2012 - 11:52 PM

In the .ini file we added the line InfoTabOpen=0 under all connections so that it wouldn't open any longer thus removing the issue with the INFO tab that was fixed in 9.61




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users