Thanks for the overwhelming response to SQLyog 8.1 release. We had highlighted the speed improvements in Data Synchronization in that post. As promised here are the benchmarking results. In this post, we will compare SQLyog 8.1’s data sync speeds against previous version of SQLyog (v 8.05) and Maatkit (v 3519).

We have done similar comparisions in the past. By comparing against Maatkit, we are not trying to imply that SQLyog is better than Maatkit or something similar. Both these tools target a different set of audiences. Maatkit has got some incredible tools that we use internally for a variety of tasks. Maatkit is command line, free and open source. SQLyog’s Data Sync Tools have GUI support, are closed source and are not free on Windows. We are comparing against Maatkit because we didn’t find any other 3rd party tool that does data sync at similar speeds. And just comparing the new version of SQLyog with the old version of SQLyog is not that interesting!

Finally, we would like to thank Baron ‘Xaprb’ Schwartz (author of Maatkit) for the Maatkit algorithms. SQLyog data sync algorithms are based on Maatkit algorithms.

Benchmarking Setup did consist of a source and a destination machine connected by a standard 100 Mbps network link.

System Configuration:

Source: AMD Athlon 2.1 GHz Dual Core, 2GB RAM running Win XP Pro SP2

Destination: Intel  Core 2 Duo 2.53 GHz, 2GB RAM running Win XP Pro SP2

To arrive at the benchmarking figures we took the average of 3 readings for each case. Below are the benchmarking results with the time taken to complete data sync for each of the 4 cases:

Case 1:

Source Rows: 3950400, Target Rows: 3950400
Inserted Rows: 49599, Updated Rows: 49500, Deleted Rows: 49599
Primary Key (INT)
Engine: InnoDB

Case 2:

Source Rows: 48025, Target Rows: 48775
Inserted Rows: 1225, Updated Rows: 1225, Deleted Rows: 1975
Primary Key (INT)
Engine: InnoDB

Case 3:

Source Rows:150404 , Target Rows: 152864
Inserted Rows: 12136, Updated Rows: 16236, Deleted Rows: 14596
Primary Key (VARCHAR, CHAR)
Engine: InnoDB

Note: In this case Maatkit could not complete data sync. We aborted the test after 20 minutes of wait.

Case 4:

Source Rows: 18209, Target Rows: 10000
Inserted: 9018, Updated Rows: 1001, Deleted Rows: 809
Primary Key (SMALLINT, BIGINT)
Engine: MyISAM

The performance improvements in Data Sync is mainly due to the optimization that we have done to reduce the number of I/O operations required over the network. As a result, speed improvements will be more visible when the source and destination servers are on different machines.

You can download the four test cases if you want to verify/test on your environment. And if you think it is not fair to compare with Maatkit on Windows, you can do the same on Linux with the SJA (SQLyog Job Agent) for Linux as well.

Download links for zip archives with SQL dumps for target and source:

• Case 1 (~20 MB)
• Case 2 (~3 MB)
• Case 3 (~2 MB)
• Case 4 (~350 KB)

Note that these dumps do not contain a USE statement. So be careful about where you import them!

Download SQLyog and SJA here. Note that data synchronization is included in SQLyog Enterprise and Enterprise Trial versions only - and in SJA for Linux.

Shameless Plug: We are using Visifire - Webyog’s open source charting component to draw these charts. If you want to put some visually stunning animated charts in your applications, check out Visifire!

Please feel free to share your thoughts on the comments below.

Regards,
Team Webyog

Comments(0)

One of most loved feature of SQLyog is the snappy and responsive user interface. Many of our users and customers prefer SQLyog over other tools because of this particular reason.

One reason for the zippy interface could be that SQLyog is entirely developed in C/C++, which tends to be faster than other languages when it comes to raw speed. I don’t want to start a flame war here, but our customers seem to like this fact. Using C/C++ allows us to use the native MySQL C client libraries that gives the best performance as compared to other ways of communicating with MySQL. Using C/C++ also ensures that there are no external dependencies on any bulky frameworks and the download file is relatively small.

Although using a language that complies directly to machine instructions helps a lot, but true speed comes from better algorithms. A similar analogy in the MySQL context would be that you might get some benefits from tuning your mysql.cnf/mysql.ini file, but real benefits come only when you find problematic queries in your application and rewrite them and/or create better indexes.

SQLyog uses a set of smart algorithms for its data synchronization tools. These algorithms ensure that we only transfer checksums using multiple threads and do full row transfer only when required. We have an old blog post with some benchmarking results here.

Although our data sync speeds are quite impressive, we were not happy with the results in certain scenarios. This led us to improve the data sync speeds even further with 8.1. We will publish some new benchmarking results soon in our blog.

With 8.1, we have been able to introduce massive speed improvements in the following areas:

• Data synchronization
• Auto complete
• Export as SQL (including Scheduled Backups), CSV and XML

Wait, we are not finished yet with the improvements in 8.1. This release introduces the following major features:

• Completely revamped Objects tab. The tab has been renamed to “Info”. The contents are now available in nicely formatted HTML in addition to the old text format
• Direct import of Access, Excel and CSV files. Now you don’t need to create ODBC DSN to import these file types. Just point to the files and SQLyog does the rest

Last but not the least, we continue to improve the usability with every release of SQLyog. With 8.1 we have made the following GUI changes:

• Added an option to ‘preview SQL’ in CREATE/ALTER TABLE
• Column width in all GRIDs (not only DATA and RESULT tabs) are now persistent
• New GUI for defining ENUM and SET columns in Create/Alter GUI
• Some reorganisations of menus, improved captions and explanations in dialogues and wizards etc

Needless to say, this release contains many bug fixes, UI improvements and minor features.

We are very excited about this release, and hope that you will like it. We would love to hear from you.

For the complete changelogs, please refer to the following posts:

• SQLyog 8.1 RC3 Release Notes
• SQLyog 8.1 RC2 Release Notes
• SQLyog 8.1 RC Release Notes
• SQLyog 8.1 beta1 Release Notes

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Regards,
Team Webyog

Comments(5)

http://www.pinoytux.com/linux/john-dvorak-the-sun-mysql-deal-stinks

Comments(0)

Hi,

We will be present at MySQL Conference & Expo, 2009. If you are around Silicon Valley, don’t forget to meet the team behind your favorite MySQL tools at Booth #317. We are happy to offer a flat 20% discount on all purchases made during this month. Use the coupon code: uc2009 during the purchase.

Also, we are pleased to announce the release of MONyog 3.06.

Changes (as compared to 3.05) include:

Bug fixes:
* Installation on Windows could fail with a js32.dll-related error. Waiting a few seconds and ‘retry’ would solve this but now the error does not come.
* Fixed a ‘false alert’ related to settings for temporary tables (the error occurred because of comparing an integer with a non-integer).
* With slow query logs generated from servers greater than 5.1 Query Analyzer “Max” column would show a value without decimal point if the query count was greater than 1.
* Chart popups from Monitors/Advisors page would sometimes only populate from next time data was sampled (it was a concurrency issue in code).
* 32 bit builds for Linux failed to populate the Monitors/Advisors page. This bug was introduced in 3.05.
* 3.05 TRIAL build for Windows would expire at a fixed date and not after one month as it should.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Regards,
Team Webyog

Comments(0)

Changes (as compared to 8.03) include:

Features:
* Crash dumps with no information (zero-size) will now be deleted automatically.
* When saving/copying from the editor the LETTERCASE modfications for keywords and functions will now be preserved.
* Selecting a ‘child’ object for a table (columns and indexes) will now refresh the DATA tab if DATA tab is open. Before only selecting the table object itself did. Also the table information is now available in OBJECTS tab when a column or index is selected.
* Now also a ‘key’ icon is used for identifying the Primary Key in an Object Browser ‘Indexes’ folder,
* When a GRID cell is only partly visible, doubleclicking it will move the grid position so that cell/row will become visible (before it worked like that with singleclick - we changed to doubleclick as this is a de facto standard in such grids - in Excel for instance).
* The copy database/table dialogue will now expose detailed information about objects copied.
* In ‘empty database’ added a ‘SELECT ALL’ option in order to avoid multiple confirmation popups.

Bug Fixes:
* An index defined on more than one column displayed as many time in the Object Browser as the number of columns used for defining the index.
* Fixed scrolling issues with the GRIDs.
* The GRID of CREATE/ALTER TABLE dialogue required double-click in a cell before pasting into that cell was possible.
* Query Builder ‘copy query to same query tab’ was broken.
* Query formatter would insert Unix linebreaks (\n) instead of Windows linebreaks (\r\n) between multiple queries what in turn could result in that the tokenizer/editor would fail to recognize exactly where a statement started and ended.
* In the Schema Sync dialogue the ‘compare’ button could grey out. This bug was introduced with the ‘refresh’ option added in 8.03.
* The ‘include column names’ option in CSV-export dialogue (including ’save to clipboard’) was not persistent.
* Fixed some flickering and repainting issues in Schema Designer in particular as well as other similar small issues elsewhere in the program.
* A floating point value displayed in the form “.1234″ (no ZERO before the decimal sign) would be exported as integer “1234″ with “export as Excel XML”. Also NULL values for numerical types could result in a XML file that Excel would not open.
* Adding/dropping an index would collapse the ‘columns’ folder for the table in Object Browser and vice versa.
* F6 keyboard shortcut was disabled if focus was on a ‘columns’  folder in Object Browser.
* When using “copy database” and an error occurred while copying a ’stored program’ the copy proces would not abort on error. Instead next object was copied or attempted copied. This has been changed so that behaviour with ’stored programs’ is the same as with tables.
* When clicking the database dropdown in the connection manager of SJA wizards the mouse pointer did not change to an ‘wait state’ pointer while list of databases was fetched from the server, what could give user an impression of SQLyog ‘hanging’ .
* Progress bar improvement with “Export as SQL dump” and “Restore from SQL dump”.  Also progress information with files larger than 4 GB displayed wrong due to 32 bit integer overflow. Now a 64 bit integer is used.
* Lots of small GUI fixes reported internally and by users.

Functionality changed:
* We will now not UPDATE a row of data from DATA or RESULT tab if any of the columns displayed for the actual row contains data identified as binary data by containing a NULL byte (’\0′ in C-notation) no matter whether this occurs for binary, varbinary, char or varchar types. Instead an error message will print (reason for this: the UPDATE operation would store what was displayed - not the underlying binary data causing the display).
* The arrow icons displaying in column headers of the DATA grid indicating sorting state have been replaced/reverted to conform with de-facto standards.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Comments(0)

Hi,

We are pleased to announce MONyog 3.0 - a major new release. MONyog is rapidly becoming a cost-effective alternative to expensive proprietary tools for MySQL monitoring.

Unlike databases like SQL Server & Oracle, MySQL does not provide powerful APIs to gather extensive profiling information about each and every query. MySQL developers and DBAs have largely used on a combination of the following techniques to find problem SQL:

1. SHOW PROCESSLIST
2. Slow Query Log and General Query Log
3. SHOW PROFILES (available from MySQL Community version 5.0.37)
4. Capturing and filtering tcpdump output

To overcome the shortcomings of profiling features in the MySQL server, MySQL Proxy was introduced which allows developers to route queries via MySQL Proxy and then extract profiling information from MySQL Proxy. (It also provides other features like load balancing, query rewriting, etc.)

MONyog 3.0 can now collect profiling info from MySQL Proxy. All you need to do is to start MySQL Proxy with a lua script provided with the MONyog distribution and then configure MONyog to collect data from MySQL Proxy. With the introduction of MySQL Proxy support, MONyog becomes the only GUI MySQL monitoring tool in the market to support the following features for finding problem SQL:

1. SHOW PROCESSLIST snapshots at regular intervals
2. Slow Log and General Query Log Parser
3. MySQL Proxy support.

Another major change in MONyog is completely revamped monitors and advisors. Most of the monitors and advisors in MONyog 1.x and 2.x were written by Webyog engineers. While we have been developing MySQL tools for the last 5 years, coding MySQL monitors and advisors is not one of our core strengths. Our core competence is in developing slick GUI tools at a fraction of the price of our competitors. So we sought help. Percona, with their huge experience in MySQL internals and MySQL tuning was a perfect choice. With the help of Percona, we have rewritten most of the monitors and advisors in MONyog 3.0.

Embedded below is the Screencast on how MONyog helps you in finding Problem SQL
Screencast on Query Analyzer from Team Webyog.

Last but not the least, you can use all features of MONyog 3.0 without installing any agents or additional software on your production MySQL servers! Download a copy of the trial now from http://www.webyog.com/en/downloads.php. Do send us your valuable feedback.

MONyog is an Enterprise monitoring tool for MySQL servers without the high price tag usually attached with Enterprise tools. Pricing starts at $99/server and $999 for Unlimited servers. Additionally, the licenses are “perpetual” and not “subscription based”, so don’t have to cough up money every year if your installed version of MONyog is good enough for your monitoring needs.

Finally, for a complete list of features and bug fixes in MONyog 3.0, please refer to the following posts.

• MONyog 3.0 Release Notes
• MONyog 3.0 beta2 Release Notes
• MONyog 3.0 beta1 Release Notes

Regards,
Team Webyog

Comments(1)

Hi,

SQLyog 8.0 is a major new version of SQLyog introducing major features like Query Profiler, SQL Formatter and vastly improved look and feel.

Query Profiler:

MySQL has always lacked the sophisticated profiling tools shipped with proprietary databases like SQL Server, etc. MySQL developers have largely depended on EXPLAIN for tuning queries. The SHOW PROFILE patch by Jeremy Cole was introduced in the MySQL Community version 5.0.37 and it provided much more insight into where the query spends its time. However, to take advantage of this feature, MySQL developers were supposed to switch on profiling, run their queries and then filter the profiling data from a table that contained the profiling results of the last few profiled queries. A lot of manual book-keeping is required to take advantage of this  powerful feature. In an ideal situation, the MySQL developer should execute the queries and the profiling info should be available along with the result-set. Unfortunately, none of the MySQL client tools (desktop or web-based) provide intrinsic support for this feature.

Another great tool to profile queries is mk-query-profiler, part of maatkit. It does an incredible job in displaying the changes in STATUS variables due to execution of a query. It also presents the information in a very readable format, making it easy for the developer to tune queries. However, one problem with this tool is the lack of integration with any MySQL GUI client tool. So if an user is using a GUI tool to write queries, she needs to profile queries in a separate step using mk-query-profiler.

SQLyog 8.0 tries to address these problems by integrating all of the above profiling methods in a single intuitive GUI. It takes care of switching on profiling and taking the snapshot of the STATUS variables before and after the execution of query. It then aggregates these data (along with EXPLAIN and EXPLAIN EXTENDED results) and presents the aggregated info in a single window. The user just needs to type the query as usual and all other commands required to capture profiling information are executed behind the scenes by SQLyog. Also, along with the changes in the STATUS variable, the profiler shows a simple description of the STATUS variables that changed due to execution of the query.

In summary, the SQLyog’s Query Profiler helps the user get more insight into the execution of a query by:

• Using SHOW PROFILE information
• Capturing the difference in STATUS variables due to execution of a query
• Using EXPLAIN statement, and
• Using EXPLAIN EXTENDED statement

Embedded below is the screencast on how Query Profiler helps you in optimizing the queries.

SQL Formatter:

There is absolutely no doubt that properly formatted SQL code makes SQL much more maintainable. Most experienced SQL developers make sure that they follow some SQL formatting guidelines to make their code readable and maintainable. However, many times we come across unformatted SQL written by others or sometimes we are too lazy to write properly formatted SQL ourselves! SQLyog 8.0 comes to the rescue! It neatly formats the SQL with the click of a button. This feature is also known as “SQL Beautifier” or “SQL Pretty Printer” in various other software.

SQL Formatter in action

New look and feel, improved icons:

Though SQLyog has added tons of features to its quiver, it looks the same as it did in 2004. So, we thought of overhauling its looks. Result was adding new icons with Vista theme, toolbar containing large icons for frequently used tasks, gradient based tab and grids, etc. Here is a screenshot of SQLyog 8.0.

The All New SQLyog 8.0

Finally, for a complete list of features and bug fixes in SQLyog 8.0, please refer to the following posts. (Note that SQLyog 8.0 was previously named as SQLyog 7.5. Due to the inclusion of many major features it is being released as SQLyog 8.0.)

• SQLyog 7.5 RC2 Release Notes
• SQLyog 7.5 RC Release Notes
• SQLyog 7.5 beta2 Release Notes
• SQLyog 7.5 beta1 Release Notes

Note: For few hours past this announcement you may receive registration code for 7.x when purchasing 8.0. If the code does not work please get the right one form Customer Portal.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Regards,
Team Webyog

Comments(20)

Changes (as compared to 7.14) include:

Features:
* Added an option to use Base64 encoding for communicating XML data stream with the HTTP tunneller. Using this option can be required to work around this XML-related bug in PHP.
* Improved editor performance with large scripts if Wordwrap was enabled.

Bug fixes:
* On Windows Vista Business edition, SQLyog could crash while closing down. No other Windows OS (and also not any other Vista edition) was affected.
*  Also a ‘hang’ could occur when closing down. It could happen with all systems, but Windows Vista was mostly affected.
* Schema Sync failed with identically named Foreign Key CONSTRAINTs on identically named colums of basically same type (that could still differ in LENGTH, SIGNED/UNSIGNED specification etc.)

Miscellaneous:
* When a PK is built on a string column using a case insensitive collation, SJA could try to insert a row where the PK was identical to an existing one as compared “COLLATION-wise” (ie. when only LETTERCASE differed). That would result in ‘duplicate key’ error and the sync job would abort. We have now reverted back to case insensitive comparison with string-based PK’s as before 7.0, but (to avoid data loss/overwriting) we will abort sync of tables when a string-based PK uses a binary or case sensitive collation.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Also SJA for Linux was updated with the fix for ‘duplicate key’ error in Data Sync.

Finally note that due to the added base64-option it was necessary to change the tunneller script. To use this version of the program with HTTP-tunnelling the old tunneller script must be replaced with the version shipped with this build.

Comments(0)

Features:
* The ‘query sniffer’ can now connect to and retrieve data from a running instance of the MySQL-proxy program used by one or more clients to connect to MySQL.  A LUA script for controlling the proxy for use with MONyog is provided with this build.  With this sniffer option the SHOW FULL PROCESSLIST statement will not be sent and thus use of ‘query sniffer’ will not put any load on MySQL.
* SSH connections will now attempt more authentication methods in case the first method attempted failed.  This will solve problems reported with tunneling to MySQL on FreeBSD that as per default does not support the full range of SSH authentication methods (and possible similar problems).

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Also note that there are some bugs with MySQL proxy that you should know.  Most important for use with MONyog are

1) http://bugs.mysql.com/bug.php?id=31018.  Simply do not let the proxy listen on port 4041 and you will not be afffected by this.

2) http://bugs.mysql.com/bug.php?id=41417. This is more serious. If the MySQL server  behind the proxy stops running, the proxy does not return information about this.  As a consequence MONyog will have to be restarted when MySQL is again available.

Comments(0)

Changes (as compared to 2.83) includes:

Features:

* This release adds a ‘base time’ setting in MONyog. This setting (if defined by user) will be used for calculation of uptime-based counters. The reason for this implementation is that if FLUSH STATUS is executed with a MySQL server, specific server status variables will be reset to the same value as would be after a server restart. However the ‘uptime’ status variable itself is not affected by FLUSH STATUS. And as uptime-based counters will relate the value of cumultive status variables with some intial time, using the ‘uptime’ variable as the initial time will result in calculation of misleading values if FLUSH STATUS was executed. So to get true uptime-based counters in MONyog with servers that do not support the ‘uptime_since_flush_status’ variable (and currently only 5.0 COMMUNITY servers from 5.0.37 do - not ENTERPRISE servers and not any other major branch than 5.0 and also not 5.0 before 5.0.37) you will need to define a ‘base time’ in MONyog greater than or equal to the time where FLUSH STATUS was executed last time. Also you can now discard data older than a specific time by using this setting. Refer to documentation for full details.

* Improved the purging logic with the MONyog embedded database. Now also system CPU load is considered and purging operations will be skipped if CPU is high. This is a further improvement to the change in purging logic introduced in version 2.5.

As compared to 2.9 Beta1 this also includes:

Features:

* MONyog can now be running behind an Apache server configured as ‘reverse proxy’. Before this release MONyog’s java script was not accessed due to absolute paths, as a result MONyog pages were not displaying properly.

Bug Fixes:

* Fixed high CPU usage in the ‘query sniffer’ if sniffer “Minimum time taken ” value was set to “0″ (or left blank).

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Comments(1)