Automatic SQL query optimizations & Optimal indexing   smart Query Optimizer (SQO) feature. The Smart Query Optimizer recommends the best index for a long running/bad query. It can analyze query, find out if there are any bottlenecks and offer needed recommendations. This feature is designed by top database architects and also tested with many databases and their queries. It has shown significant performance improvement after adding new indexes. Many application developers and programmers have gained more than tenfold performance improvement after using this tool. This tool also rewrites and give recommendations to  your query based MySQL and displays it in a report. We can open slow query optimizer from power tools option.

 

This feature is integrated into many modules including slow query analyzer, query locking, deadlocks & long-running queries enabling it to easily fix these issues. Choosing the right indexes is the foundation of the query optimization process. Our proprietary AI algorithm takes dozens of factors under consideration, to eventually recommend the optimal indexes matching your SQL queries. We’re constantly improving the algorithm, while testing it on tens of thousands of real-world queries.

You can open the SmartMySQL’s SQL optimizer from Menu  PowerTools => SQL optimizer

Also, You can create new index/es using “Create Index” button and it can create index after it can analyze new index usage with the query and  show how much performance the DB server gained after created index/es.

 

The tool come with visual explain button. It can show you graphical explain output as below

 

Visual explain

While a developer is writing a query on a query editor, it can identify long-running queries, bad queries and unused index queries.

If you click the warning icon, the tool opens Smart Query Optimizer (SQO).

When developers use this feature, they can easily identify and solve all query performance issues. This option minimizes the chances of producing an illogical query.

The smart query optimizer offers the best index based on your query. It also recommends efficient queries. It can further rewrite your query and display an optimizer-converted query. It yields a better result if you employe to create  an index option. By using this alternative, Smart Query Optimizer (SQO) creates an index and checks how much performance has been improved. You also have the option of inserting your preferred index. You can add this query in your code and MySQL Optimizer would directly use it without modification.

The smartest way to Summarize, Analyze and Optimize MySQL slow queries from the MySQL slow query log file

Click me for Demo

The MySQL slow query log consists of SQL statements that take more than long_query_time seconds to execute and require at least min_examined_row_limit rows to be examined. The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. However, examining a long slow query log can be a time-consuming task. To make this easier, you can use the mysqldumpslow or pt-querydiget command to process a slow query log file and summarize its contents. These tools can help to find out the slowest queries and it needed expert level DB skills to fix them. There is high shortage for MySQL DB expert. for more details

 

The SmartMySQL slow Query logs analyzer analyzers the slow query log and summarize the result and displays them in a rich user interface. Any computer knowledge persons can easily understand and fix the DB performance issues.The feature is available in all smart MySQL tools under power tools => MySQL Slow Query Log Analyser.

The screen will open and provide the following option to upload slow query log.

Import From Logs: You can select slow query log file using this option. It allows uploading multiple files.

Import From Table: If you are saving slow queries into mysql.slow_log the table then you can use this option

Sync From Server: It is an upcoming feature if you add DB server OS details then the tool can pull the recent slow query logs directly from the OS.

After selecting the slow log file/table then the slow query log analyzer analyzes the file and display the summary report.

The summary report details: Top slow queries

• #:S.No of all query fingerprints
• QueryRating: It displays the query rating based on query examined rows and rows sent details from the slow query log. It graphically represents red starts for bad queries, and green starts for optimal questions. If you double-click the rating, then it will connect to visual explain and query optimization for the query. It recommends optimal indexes to fix slow queries. Click me for Demo
• Query: It shows a query for each fingerprint queries (query digest)
• Response Time: response time of the query fingerprint
• PCT(%): Percentage of response time for the query fingerprint
• CAlls: Total number of times the query executed for the query fingerprint.
• AvgRespnseTime: Average response time of the query fingerprint
• QEDI: Query Execution Date interval Graph
• QETI: Query Execution Time interval Graph
• Database: The query’s database
• Table: List of tables which are in the query.
• MinRespnceTime: Minimum response time of the query fingerprint
• MaxRespnseTime: maximum response time of the query fingerprint
• StddevResponseTime: Stded deviation response time of the query fingerprint
• LockTime: Lock time of the query fingerprint
• AvgLockTime: Average Lock time of the query fingerprint
• RowsSent: Total Rows sent for the query fingerprint
• AvgRowsSent: AvgRowsSent for the query fingerprint
• RowsExamined: total Rows examined for the query fingerprint
• AvgRowsExamined: Average rows examined of the query fingerprint
• Explain: If you click the link button, then it will connect to visual explain and query optimization for the query. It recommends optimal indexes to fix slow queries. Click me for Demo

You can sort the summary report grid one any column if you click on the column in the grid.

You this tool any computer knowledge person can automatically Query Tuning. Once identified slow query then click Explain link or Query rating cell then It shows visual explain and Query optimzation report with optimal index/es and query rewritten recommendations. Clik me for demo

 

The SmartMySQL provides detail statistical reports after integrated recommended optimal index/es.

Avails to identify where recommending incipient indexes are utilizable or not. if it is utilized by MySQL then it shows performance difference before and after index creating. It preserves historical optimal index recommendations.

The Top Slow Query Tab is default sorted by Query fingerprint response time, and Top Lock Queries tab default sorted by Query fingerprint Lock time.

 

SQL Optimizer Report: It displays all slow queries along with optimal indexes and query rewritten recommendation. It can help to save time for validating individual details.

Top Lock Queries: It displays all slow queries order by query lock time.

Top Tables: It displays list of tables order by table usage in the queries.

 

Top Host: It displays list of Hosts order by execution time.

Top Users: It displays list of Users order by execution time.

Top Sachems: It displays list of Sachems order by execution time.

 

Export to Excel: exports the report in excel format and you can send it to your managers. Sample one.

The Smart MySQL slow query log analyzer is very useful tool for PHP developers, Programmers, Linux administrators and who are managing MySQL production servers without knowledge in MySQL Optimization. They can regularly monitor the slow queries and fix them if any slow running SQL queries.

 

Monitoring Optimization

Using sophisticated analysis methods and proprietary fault detection, SmartMySQL displays its information in a dynamic interface, with dashboards optimized for organizing and viewing the most relevant information about a database’s activity and work. These dashboards are easy to understand but also highly customizable, offering value for both database novices and experts alike.SmartMySQL Profiler(SMP) is built for collecting and storing information on MySQL database’s servers and the systems that host them. SMP employs synchronous communication with database’s servers and generates  pages divided into small independent objects. It can then independently update itself as required. This reduces bandwidth consumption, given that the user doesn’t need to reload the entire page for updates and allows display of data on as many MySQL servers as possible.

Each of the SMP monitoring tab pages refreshes automatically when new data becomes available.

Agent-less Monitoring

Typically, monitoring and advisory tools use external PHP or Java agents to allow traditional web servers to connect to MySQL. Unlike these tools, SmartMySQL is compiled with the MySQL client code through Java SDK, permitting connection directly to MySQL database servers without needing agents.

This greatly simplifies Monyog deployments since you don’t need to install and maintain agents on each MySQl host. Instead, Monyog uses the MySQL client to connect to and retrieve database information. To retrieve operating system-level metrics, it uses SSH when connecting to Linux hosts.

You install SmartMySQL on one system and that’s it. It can retrieve whatever information it needs using the same sources and methods as a database administrator.

Built-in High Performance Database

Internally, SMS manages the data it collects using a high-performance embedded database. Whatever server parameters it retrieves from MySQL servers are also stored in this database. Various methods utilized in displaying counters or metrics rely on the data it provides.

Query Analyzer

The Query Analyzer feature of SmartMySQL profiler helps you identify

problem SQL. SMP(SmartMySQL)can find problem SQL by one or more of the

following methods:

Taking SHOW PROCESSLIST snapshots at regular intervals (using Monyog Sniffer)

Using MySQL Proxy to collect profiling data (using Monyog Sniffer)

Utilizing Performance schema tables(events_statements_summary_by_digest , events_statements_history_long etc)

Parsing Slow Query Log and General Query Log (using SMP Log Parser)

There are several advantages and disadvantages of each approach.

SHOW PROCESSLIST is available in all MySQL versions and it is the easiest to setup. However, taking a snapshot of SHOW PROCESSLIST does not guarantee that all queries will be captured. Many short-lived queries can be missed between two successive snapshots. It is a quick and easy way to find long running queries.

Log parsing requires some additional setup. Also, switching on the General Query Log puts a significant amount of load on the server. You should always keep the Slow Query Log switched on. Parsing the Slow Query Log is an effective way to find bad queries

.

Performance schema based sniffer makes use of the performance_schema database of the MySQL server. Monyog queries the performance schema database and collects snapshots at regular interval. Since each and every query is logged in the Performance schema database, SMS displays even the short lived queries using this method.

To use the SMS Query Analyzer functionality for a specific server, the server’s slow query log details must be configured.

Using the above tools to find problem in SQL is almost always a post-mortem exercise.

In certain situations you may want real-time notifications for

long-running queries. Monyog can continuously monitor queries in real-time and send notifications (on mail or SNMP) for queries that take more than a specified amount of time to execute. You can also specify an option to kill such queries instantly.

Query profiling and OS monitoring

 

MySQL Profiler:

SmartMySQL profiler can monitor MySQL performance behavior, DB buffer hit ratio, Engine performance, query profiling, OS monitoring with detailed graphs etc. It also recommends solutions.

 

Producing MySQL database servers plays a very sensitive role in your live application. Hence, it is very important to find out root causes & solutions when facing any database production issue. It is for this purpose that SmartMySQL comes with a new module called debugger. We can open debugger from ‘powertools’ option. Autodetect  automatically detect the problems in server.  Demo 

Autodetect  automatically detect the problems in server.

It debugs the following list of issues in MySQL servers which are over 95% of MySQL production issues (Except MySQL code bugs):

Long running queries (Slow queries) On Processlist

Long running Transaction in InnoDB

Deadlocks

SQL queries load on the DB

Replications lagging or braking issues

MySQL configuration issues debugging

DB connections

Error log analyzer

Handling long-running queries:

Long running queries are queries that need to be killed because they seem to be hanging. SmartMySQL makes this task less tedious. When a user selects debugger option and navigates to running queries, a window appears showing all running queries in grids. The Grid comes with detailed visual explanation & SQL Query optimizer(SQO) options. You can simply find out long-running queries and check execution plan using visual explain. The user can also find out right indexes for bad queries using SQO. Long running queries on production DB server are very common problem, Start MySQL provides solutions for it . It is very simple and every developer & DBA can use it and fix the problems.

Handling long-running transactions(InnTOP):

The MySQL default isolation (REPEATABLE-READ) locks other transactions if any thread is running long time or experiencing big delay between start transaction and end transaction(COMMIT/ROLLBACK). SmartMySQL comes with yet another plugin termed Lock Monitor to overcome this hurdle. It helps to fish out any threads that are locking each other. It also shows transaction locking issues between transactions and indicates which thread is running long.

Handling deadlocks: 

A deadlock in MySQL happens when two or more transactions mutually hold and request for locks, creating a cycle of dependencies. InnoDB automatically detects transaction deadlocks, rolls back a transaction immediately and returns an error. It uses a metric to pick the easiest transaction to rollback. When a user selects SmartMySQL debugger’s deadlock monitor tab, a tab opens showing all SQL queries causing deadlock. There is no permanent solution for deadlocks but if  TRANSACTIONS SQL queries slow /full scan then SQL query optimization the query to fix this issue. This tab has been integrated to SQL Query Optimizer.

Replications Debugger:

SmartMySQL also comes with a replication monitor. This option displays all replication related issues. It informs you of any lagging or replication failed issues. Additionally, it can find out slow running queries on DB servers and find out if any bad query is running. This tool is also integrated with SQL tuner and if you discover any bad query, you can tune it with help of SQL tuner.

Configuration Debugger

This feature helps out if the user faces any production issues due to MySQL configuration. It can validate all configured variables, lists out variables which may cause slowness & recommend new ones. This is very good option for those who are running MySQL without DBA resources.

MySQL has 300+ configuration variables which needs to be adjusted based on your workload with the help of MySQL experts/DBAs. The SmartMySQL introduced a new feature called MySQL Config under Power tools menu option. This new plug-in analyses MySQL configured variables and gives recommendations based on your application workload. It is a very handy tool for developers who do not have MySQL experts/DBAs in their team when working on a project.

Once a user selects this option, the SmartMySQL starts analyse your DB server workload and offers needed recommendations if it needs to change your MySQL configuration file. Usually, this operation requires few minutes to achieve this.

The configuration tuner provides various status for all variables. If a variable is in the wrong status, then the user needs to check details and then adjust the variables. It can also provide individual details for the following categories:

1) Performance matrix

2) Security Recommendation

3) Replication matrix

4) Performance schema

5) Storage statistics

6) Innodb matrix

7) MyISAM matrix

8) Recommendations

9) Database matrix

10) Table columns matrix

11) Index matrix

DB connections analyzer

Many MySQL users sometimes face “MAX CONNECTIONS” error. This issue occurs if any application opens many connections unexpectedly and if opened connections reached MySQL maximum configured connections. This option can show you which application/host/user is opening connections. It gives details report. You can find out which application is opening many connections within seconds.

Error log analyzer:(upcoming feature)

Smart MySQL directly connect DB server error log and show analyze log report using another feature Innodb error analyzer. It calculates Innodb work set and suggest better alternative if user decides to change innodb_buffer_pool size.

SQL Speedometer

This tool shows you all running DML & SELECT queries rate at the time of traffic. It also shows QPS speed with detailed user interface.

 

SmartMySQL can identify Query syntax error before execution also and highlight with red color Traiangle.

SmartMySQL  highlights syntax error areas after query execution. When a user executes any query which has a syntax error, SmartMySQL points out syntax error areas in the query.

 

In the above example SmartMySQL has identified where the syntax error is in the query and highlighted it with red background color. You can simply check and correct the query. Further, during such instances SmartMySQL shows google, yahoo and Bing search engines icons. By clicking any of the icons, SmartMySQL automatically opens search engine results relating to the error message.

Then you can do your R&D and fix your error. These features are necessary for reducing development time.

By enabling query cache we can improve performance in read environment. But degrade the performance in heavy read and write environments. In SMARTMySQL you can clear cached profile data by selecting powertools-> Clear cached profile data.

SmartMYsql’s Schema Synchronization Tool, is a visual comparison/synchronization tool designed for developers who work between different MySQL servers or need to keep databases between two MySQL servers in sync. This means reporting the differences between tables, indexes, columns and routines of two databases, and generating scripts to bring them in Sync.

Schema Synchronization Tool enables you to compare and work with different databases. Its color-coded side-by-side comparison makes it easy to pinpoint at a glance, similarities or differences between two databases. Schema Synchronization Tool supports column, index and routines comparison enabling you to quickly insert & drop columns, indexes and routines. If there are any differences in the schema of a column/index/routines, Schema Synchronization Tool generates the correct scripts to apply those changes.

Select  Powertools -> Schema Synchronization Tool or use the keyboard shortcut  Ctrl+Q to start the tool.

Source: You can select database to compare from drop-down list of databases option like connection window.

Destination : You can select destination connection name from Saved connection drop-down And Database for comparison from database drop down having  a list of all created databases in the  destination server.

Options to compare:

You can select different options for comparison.

object filter:You can choose the different object filters to compare by click on check box in front of every object for mysql versions above 5.0.

After selecting options click on ‘compare’ option to compare both source,destination databases.