Jump to content


Photo

Structure Synchronization Incredibly Slow


  • Please log in to reply
34 replies to this topic

#1 Davidelity

Davidelity

    Member

  • Members
  • PipPip
  • 12 posts

Posted 28 April 2008 - 08:38 AM

Hi
I recently bought the enterprise version, a major driver being the structure synchronization tool.
Unfortunately it's incredibly slow. I have a 700+ table database and it takes approx 45 mins on a dual - dual core zeon (ie 4 cores) machine.
CPU use is pretty low, there are spikes to 25% on one of the cores approximately every 5 secs on one cpu, almost as if someone put a 5 sec sleep between table checks or something.

Hope there is some way to speed this up, or you can fix it, otherwise its incredibly annoying for me and will probably use some other tool for structure synchronization.

David

#2 Davidelity

Davidelity

    Member

  • Members
  • PipPip
  • 12 posts

Posted 28 April 2008 - 08:43 AM

Hi
I recently bought the enterprise version, a major driver being the structure synchronization tool.
Unfortunately it's incredibly slow. I have a 700+ table database and it takes approx 45 mins on a dual - dual core zeon (ie 4 cores) machine.
CPU use is pretty low, there are spikes to 25% on one of the cores approximately every 5 secs on one cpu, almost as if someone put a 5 sec sleep between table checks or something.

Hope there is some way to speed this up, or you can fix it, otherwise its incredibly annoying for me and will probably use some other tool for structure synchronization.

David



I should have said, I am just wanting to impose the structural changes from one database, A, onto another database, B, maintaining as much as possible of the data in B but changing its structure where necessary. I am not at all interested in getting any data from A into B. Seems like it should be a relatively quick task.

Thanks
David

#3 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 April 2008 - 09:20 AM

You noticed yourself that there is very little load on the computer where SQLyog runs.
So slowness may be due to server configurations or due to the connection.
I think SQLyog is waiting for data to 'arrive' most of the time!

Please tell:
*Are one or more servers remote servers?
*Are you in control of server configuarations or are one or more servers at an ISP where configuration is not accessible for users?
*Do you have other type of objects than tables (like Stored Procedures)? (if we need to query Information_Schema some server versions - early 5.0.x in particular - are very slow!)
* What are EXACT server versions?
* What is the SQLyog version you are using now?

We are rewriting Structure Sync for SQLyog version 7.0. If there is something we can do we will do it now. We need a structure-only dump (no data) from both databases to work with. Can you create those, zip into one file and attach? You may create a support ticket if you want privacy or if file size is larger than what this Forums allows for. It is important the you ZIP - both because of the file size but also because .sql files are rejected here (to project other users against malicious users).

Also you can try yourself (if not already the case) to import both the structure dumps to two different databses on a local server and experiment with server configuration (use the various configuration file templates shipped with the server).
Computers make your grey hair come off ....

Peter Laursen
Webyog

#4 Davidelity

Davidelity

    Member

  • Members
  • PipPip
  • 12 posts

Posted 28 April 2008 - 10:53 AM

You noticed yourself that there is very little load on the computer where SQLyog runs.
So slowness may be due to server configurations or due to the connection.
I think SQLyog is waiting for data to 'arrive' most of the time!

Please tell:
*Are one or more servers remote servers?
*Are you in control of server configuarations or are one or more servers at an ISP where configuration is not accessible for users?
*Do you have other type of objects than tables (like Stored Procedures)? (if we need to query Information_Schema some server versions - early 5.0.x in particular - are very slow!)
* What are EXACT server versions?
* What is the SQLyog version you are using now?


1) No, everything local
2) Yes, everything local
3) No, everything plain tables, some with indexes
4) Server version: 5.0.37-community-nt MySQL Community Edition (GPL)
5) Using 6.52 right now, originally tried 6.53

Looking into getting to/from structures - need to check with superiors - but i'd be surprised if there was anything special about the structure, just 700 plain tables.

David

#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 28 April 2008 - 12:38 PM

1) Does *local* mean local machine or local network?
2) Are both databases on the same server?
3) Can you attach the my.ini/my.cnf for that/those server installation(s)?
4) It would be nice if we could have the exact structure as you have. Then we can be 100% sure that our measurements are comparable. For instance it may be important if ENGINE is MyISAM or INNODB.
5) As you have "a dual - dual core zeon (ie 4 cores) machine" I would gues that you have quite a lot of RAM too? (but MySQL does not scale well for multi-cores if there is only the server and a single connection running (and that is also ENGINE dependent).
6) I think I will not have to ask you to test while no other connections add significant load and no otehr programs are disk intensive at test time?


Actually with this server you have query profiling. So you can try as described here:
http://dev.mysql.com...w-profiles.html

First "SET profiling = 1" , next run Structure Sync and finally "SHOW PROFILES" and "SHOW PROFILE FOR QUERY <number>".
Actually we should do this too, now this option is there! I think we will be able to see what statements take time, and we should then try to aviod those as much as possible!





Also you posted this in the MONyog category. I will move you to the SQLyog category!
BTW: Did you try MONyog? Maybe it will point to some server configuration problems ...
Computers make your grey hair come off ....

Peter Laursen
Webyog

#6 Davidelity

Davidelity

    Member

  • Members
  • PipPip
  • 12 posts

Posted 30 April 2008 - 11:50 AM

1) Does *local* mean local machine or local network?
2) Are both databases on the same server?
3) Can you attach the my.ini/my.cnf for that/those server installation(s)?
4) It would be nice if we could have the exact structure as you have. Then we can be 100% sure that our measurements are comparable. For instance it may be important if ENGINE is MyISAM or INNODB.
5) As you have "a dual - dual core zeon (ie 4 cores) machine" I would gues that you have quite a lot of RAM too? (but MySQL does not scale well for multi-cores if there is only the server and a single connection running (and that is also ENGINE dependent).
6) I think I will not have to ask you to test while no other connections add significant load and no otehr programs are disk intensive at test time?


Actually with this server you have query profiling. So you can try as described here:
http://dev.mysql.com...w-profiles.html

First "SET profiling = 1" , next run Structure Sync and finally "SHOW PROFILES" and "SHOW PROFILE FOR QUERY <number>".
Actually we should do this too, now this option is there! I think we will be able to see what statements take time, and we should then try to aviod those as much as possible!

Also you posted this in the MONyog category. I will move you to the SQLyog category!
BTW: Did you try MONyog? Maybe it will point to some server configuration problems ...


Hi
Sorry for delay.
1) Local as in local machine
2) Yes the same local machine
3) attached
4) InnoDB
5) 2 Gig
6) Yes, no other active DB processes

Have not tried monyog.

I ran the profiling - thanks for suggestion - it only captured the last 15 as per default (maybe in future if you suggest getting profiling from a situation where many queries are potentially run you could suggest also adding "set profiling_history_size = 100") but it possibly contains the culprit which is:

show table status from `db1` where engine is NULL
show table status from `db2` where engine is NULL

both these queries take approx 20 secs whereas all the other 12 logged queries are taking under 0.1 secs, other than the trigger check which takes approx 1 sec (there are no triggers)

If its doing the table status thing every time it compares a table, rather than just at the end, that must be the problem.

One other potentially unusual configuration is that there are 50 of these databases all of them having these 700 or so tables in them.

I will rerun the profiling with the increased history later this evening when I am not working on it.

Thanks for your help.

David

Attached Files

  • Attached File  my.txt   964bytes   7 downloads


#7 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 30 April 2008 - 12:03 PM

show table status from `db1` where engine is NULL
show table status from `db2` where engine is NULL

both these queries take approx 20 secs



I actually observed myself yesterday that this query can be very slow (up to one second here with the examples I have available)

Actually we have another option, that is "SELECT .... FROM INFORMATION_SCHEMA.TABLES WHERE .. ". We have de-selected this till now because of bugs in MySQL I_S implementation in the early 5.0 tree. But maybe we should consider it again (probably nobody uses those early 5.0 alpha/beta servers any more!).


With versions up to 4.1 we simple SHOW TABLES .. but on 5.0+ this does not distinguish between TABLES and VIEWS. So not usable for structure sync with recent server versions.


We will check with your dump and if it is reproducable that this statement takes so much time wiht a specific SCHEMA, I think I will also report it to http://bugs.mysql.com. And of course replace the query ASAP if that is the problem here!
Computers make your grey hair come off ....

Peter Laursen
Webyog

#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 30 April 2008 - 12:10 PM

It still would be very usefull if you can provide a structure only dump - at least for a single table where SHOW TABLE STATUS ... is slow
Computers make your grey hair come off ....

Peter Laursen
Webyog

#9 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 30 April 2008 - 12:16 PM

Ok .. I found something!

http://mysqldatabase...records-go.html


InnoDB doesn't keep a row count in the table itself, as MyISAM does. This is because of transactional concurrency requirements. Therefore, count(*) works differently; in MyISAM, it can just ask the table how many rows it has, but in InnoDB, it has to scan some index and find out. This is slow. For that reason, the SHOW TABLE STATUS value is only an estimate, and can be very wrong, in InnoDB.



From the attached configuration file I think that you are using InnoDB extensively (you have rather large InnoDB buffer settings etc)?



I think we did not realize or consider that SHOW TABLE STATUS will need to COUNT before returning result!
Computers make your grey hair come off ....

Peter Laursen
Webyog

#10 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 30 April 2008 - 12:41 PM

I think we can provide a test build in less than 2-3 hours where the query has been replaced!

I will give some more background on this. When SQLyog 5.0 was released we originally used SELECT FROM I_S, but changed to SHOW TABLE STATUS for 2 reasons.

1) the above-mentioned bug in early MySQL 5.0
2) frequently when MySQL5.0 was installed on top of a previous version the "SELECT ... FROM I_S ..." query returned nothing! This was due to incorrect upgrade procedures or privileges settings on the server, but at that time it affected lots of user that were customers at ISP's where this was the case! We have been reluctant to return to "SELECT ... FROM I_S ...", because we can then expect reports here that "SQLyog finds no table in my database"... :-(

Anyway we will create a test build where "SELECT ... FROM I_S ..." is used and let us see the difference on your system!
Computers make your grey hair come off ....

Peter Laursen
Webyog

#11 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 30 April 2008 - 12:59 PM

btw: you really have some server parameters here:

query_cache_size=99M
tmp_table_size=101M
innodb_buffer_pool_size=569M


as regards:
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G


see this:
http://bugs.mysql.com/bug.php?id=10163
http://bugs.mysql.com/bug.php?id=339
and
http://dev.mysql.com..._sort_file_size
myisam_max_sort_file_size: "The maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes."
Computers make your grey hair come off ....

Peter Laursen
Webyog

#12 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 30 April 2008 - 01:29 PM

I am sorry, but I was too optimistic.

We use SHOW TABLE STATUES also to retrieve ENGINE, CHARSET, COLLATION, TABLE-level comments (and possibly more). And as Struct Sync shall work across different MySQL version it is complicated actually.

Due to labor day tomorrow and the weekend we can only return to this discussion on Monday.
Computers make your grey hair come off ....

Peter Laursen
Webyog

#13 Davidelity

Davidelity

    Member

  • Members
  • PipPip
  • 12 posts

Posted 30 April 2008 - 01:35 PM

btw: you really have some server parameters here:

query_cache_size=99M
tmp_table_size=101M
innodb_buffer_pool_size=569M


as regards:
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G


see this:
http://bugs.mysql.com/bug.php?id=10163
http://bugs.mysql.com/bug.php?id=339
and
http://dev.mysql.com..._sort_file_size
myisam_max_sort_file_size: "The maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes."


Thanks, all our tables are innodb though, I think the isam stuff is just long forgotten jetsam.

Very interesting link though:

"InnoDB doesn't keep a row count in the table itself, as MyISAM does. This is because of transactional concurrency requirements."

It's hard to imagine "random dives to btree" as being an optimal strategy to get a quick and dirty row count, rather than some other strategy which may also give an incorrect answer due to transactional issues, but which would at least be nearly instantaneous.

David

#14 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 30 April 2008 - 01:44 PM

I have one more request:

If you use SQLyog 'duplicate table' and only duplicate the STRUCTURES of the tables, will the 'duplicates' (with no data in them) then sync faster?
Computers make your grey hair come off ....

Peter Laursen
Webyog

#15 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 06 May 2008 - 08:06 AM

Hello ..

It is important for us to know if structurally identical but empty tables are equally slow or not.
With SQLyog you can easily create such tables with the 'duplicate table' functionality.

You may also use 'copy to other' and only copy structure for the two databases involved and will have two databases that are structurally identical (but empty) as compared to the original ones (to use 'copy to other' you need two connections to the same server only).
Computers make your grey hair come off ....

Peter Laursen
Webyog




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users