Analyzing the Inner Works of MySQL SQL Performance Tuning and Oracle Database

When it comes to MySQL, slow performance in large tables is one of the main sources of complaints. It is true that some users face problems as their database fails to sufficiently handle a more than a certain number of rows.

However, there also are many corporations that use MySQL for millions, even billions of rows of data and yet they successfully deliver excellent promise. So, why is there a contradiction between these two cases? The answer lies in understanding the intricacies of table designing in MySQL, and with the help of MySQL SQL performance tuning, how to make them work in your favour.    

What to Consider During MySQL SQL Performance Tuning

There are three major aspects of the database that can have an impact on databases with huge amounts of data. Let’s take a look at two of them now:

Buffers

The first thing to consider with any database management system is that you must have an estimate of the memory, even as you progress in terms of data accumulation. It is important for the memory to be sufficient because performance suffers greatly if it isn’t, so don’t be surprised if a drop in performance is greater than you anticipated because you may have lost track of the growth in data size and subsequently, the need for more memory space. This applies to the other aspects covered in this blog as well. Once data outgrows the memory, everything can be expected to slow down, and MySQL database and SQL becomes a necessity.

One way to ensure the memory remains sufficient for your data is to practice data partitioning. In this process, old data that is no longer required as often as recent data, is separated and stored in other servers. There are various other ways of ensuring sufficient space which we will talk about in another blog.

Indices

Indices, or indexes, are known by most of us to be a useful tool in improving the accessing speed of the database. An important thing to remember is that their usefulness depends a lot on exclusivity, i.e., the ability to select a number of rows that match with specific index ranges or values. Also, the nature of the workload – specifically whether it is cached or not – determines how much it will benefit from the use of an index.

This is actually overlooked by even MySQL optimizer at present and may need to be checked by other MySQL SQL performance tuning tools. Workload indices have a chance of much quicker access even if the size of the data being accessed is as large as fifty percent of the entire number of rows, as long as they are in-memory. On the other hand, for disk IO bound access, you may have greater success in fetching data through a full table scan irrespective of the number of rows you are requesting access to.

Since indices can differ from each other in many ways, they need to be used differently in order to effectively use them. For instance, you can place them either in a well-organized manner or at random spots, resulting in significant changes in their speed. Innodb also includes clustered keys which work by merging data and index access – such keys end up conserving IO that will prove invaluable for workloads that are entirely disk-bound.

In Conclusion

Designing table structures smartly involves taking into consideration all the abilities and disabilities of MySQL. This is especially important if you have to handle different kinds of databases in your organization.

The main reason why your organization has different databases in the first place is because of their different capabilities and shortcomings. So, the same design concepts won’t bring the same results in say, MS SQL or Oracle that they did in MySQL and vice versa. The same is true for their storage engines – each can have a different effect on the performance.

Once you have applied the right application architecture to plan your tables, you will be able to create applications that can easily handle huge data sets on the basis of MySQL.

Proper MySQL SQL performance tuning involves optimizations that can greatly boost the rate at which indices are accessed or scanned. There already are tools by Tosska Technologies Limited for this purpose like Tosska SQL Tuning Expert (TSEM™) for MySQL which you can download and start using today. Contact our team for further information or enquiries.

Improve MySQL Database Performance through Multiple Parameters

MySQL tuning is no trivial task – it takes some work. However, Database Administrators know that there are a few parameters through which they can greatly enhance the speed and output of the database.

If you are in search of ways to improve MySQL database performance, you will find some of the best in this blog. Each of the parameters mentioned here contain important settings that you can make changes to without much effort. Keep in mind that default values may vary according to the version of MySQL on your system. 

Performance Tuning in SQL MySQL: Main Categories

Here are the three major types of performance tuning in SQL MySQL, one of which DBAs usually focus on:

  • Hardware-based performance tuning
  • Tuning through Optimum techniques and practices 
  • Workload-based tuning

Hardware-based Performance Tuning in MySQL

Certain variables can be set according to the hardware specifications of your device. These include:

innodb_flush_log_at_trx_commit

For maximum durability, set it to “1”. If performance is your main concern, adjust this value to either “2” or “0”. However, doing so will result in lesser durability than if the value is set to “1”. 

innodb_flush_method

If you want to improve MySQL database performance by preventing double buffering, make sure this setting is at O_DIRECT.

innodb_buffer_pool_size

This size parameter is typically set within 50 to 70 percent of the overall RAM. You can proceed with tuning by checking on the buffer pool usage from time to time using a monitoring tool. 

innodb_log_file_size

The size of the file log is usually set in the 128M – 2G range. It is supposed to be sufficiently spacious to store approximately sixty minutes of logs and enable MySQL to flush processes, place checkpoints, and reorganize writes for sequential I/O. Again, refer to a tuning tool like Tosska SQL Tuning Expert (TSEM™) for MySQL® for further insight on whether or not the log file size needs to be adjusted. 

Tuning through Optimum Techniques and Practices

This category involves using the best MySQL practices for performance tuning in SQL MySQL: 

innodb_file_per_table

Keep this at “ON” in order to ensure a separate InnoDB table space for each table present in the database. 

innodb_stats_on_metadata

Don’t want database statistics to update constantly, and consequently, slow down read speeds? Ensure this setting is turned off, in that case. 

innodb_buffer_pool_instances

The recommended value for this is “8”. On the other hand, if the buffer pool size is less than 1G, then set it to “1”.

query_cache_type & query_cache_size

Disabling the query cache is considered useful in improving MySQL database performance. You can disable it by setting both query_cache_type and query_cache_size to zero.

Workload-based Performance Tuning for MySQL

This kind of performance tuning in SQL MySQL is relative; it depends on the workload, which is why additional details regarding the specific workload are needed. Thankfully, gathering such information is much more convenient, thanks to reliable MySQL graphing and tuning tools like Tosska SQL Tuning Expert (TSEM™). Tosska’s tools are designed to display an extensive range of metrics and give users insights and allocate resources accordingly. 

Experts suggest making changes to the innodb_buffer_pool_size parameter first. Consider the following metrics to decide whether this setting has to be raised or lowered – 

  • Your device’s RAM
  • Buffer pool size
  • The number of free pages available

Once this is done, you can improve MySQL database performance be observing the InnoDB Log File usage metrics; as mentioned already, the log file settings are generally adjusted in order to store around an hour of log data. If the data written exceeds the originally set capacity, then this setting has to be increased and MySQL rebooted. The query “Show engine innodb status” is useful in assessing what size will be ideal for the InnoDB log file.

If it starts to get burdensome, you can rely on Tosska’s tuning tools for MySQL. Visit our website for our top-of-the-line tools and to get in touch with our experts to know more about them!

Oracle Database and SQL: Tips for MySQL Database Performance Tuning

Until now we have learned a lot about Oracle database and SQL performance tuning. Now, let’s discuss MySQL database performance tuning. Just like any other relational database, MySQL could also be a nightmare to many. It can crawl to a halt at a moment’s notice and in the next moment, you will find it leaving your apps in the lurch and your business on the line. The fact is, regular errors underlie most MySQL performance issues. 

To ensure your MySQL server bustles along at great speed, providing consistent and stable performance, it’s imperative to eradicate such mistakes that are often confused by some subtlety in your configuration trap or workload. Just like any other performance tuning tips for Oracle database and SQL, MySQL too has some of its own performance tuning techniques that we have described in this blog. Let’s get started without wasting more words-

Oracle Database and SQL- Know How to Tune MySQL Database Performance

Fortunately, many MySQL performance problems have similar solutions which make both tuning MySQL and troubleshooting a manageable task. 

Here are a few tips for getting great performance out of MySQL.

Tip 1# Profile Your Workload

The best way you can understand how your server invests its time is to profile its workload. By doing so you could expose the most expensive queries for further tuning. When you issue a query against the server, you don’t have to care much about anything else except how quickly it completes. Therefore, time is the most crucial metric to consider here. 

The most significant way of profiling your workload is using a tool such as MySQL Enterprise Monitor’s query analyzer. Such a tool is meant to capture queries that are executed by the server. Furthermore, it returns a table of tasks sorted by decreasing order of response time, quickly bubbling up the most time-consuming and expensive jobs to the top so that you can find where you need to put your efforts. Workload-profiling tools group identical queries together that allow you to find slow queries as well as the queries that are fast but executed multiple times. 

Tip 2# Recognize the Four Basic Resources

For functioning, a database server requires four basic resources- Network, CPU, Disk, and memory. If any of these resources are weak, overloaded, or erratic, then the database server is most likely to perform badly. Recognizing these fundamental resources is crucial in two specific areas: selecting troubleshooting and hardware issues. 

While you choose hardware for MySQL, you must ensure the components perform well all around. Just as essential, ensure to balance them reasonably well against each other. Often, businesses choose servers having fast CPUs and disks but that are starved for memory. In some cases, adding memory is a cheap way of enhancing performance by order of magnitude, specifically on workloads that are disk-bound. This might seem counterintuitive, but in various cases, disks are overused as there isn’t sufficient memory to hold the server’s working set of data. 

Another good example of this perspective relates to CPUs. In most conditions, MySQL performs well with fast CPUs as every query runs in a single thread and can’t be parallelized across CPUs. 

During troubleshooting, examine the performance and utilization of all the four resources with much care and determine whether they are performing badly or are simply being asked to execute excessive work. This knowledge can help solve issues instantly. 

Tip 3# Don’t use MySQL as a Queue

Queues and queue-like access patterns can sneak into your application without even letting you know. For instance, if you set the status of an item so that a specific work process can claim it before working on it, then you are ignorantly formulating a queue. Marking emails as unsent, sending them and then remarking them as sent in a common example. 

Queues create problems for two big reasons: they serialize your workload which prevents tasks from being done in parallel, and they mostly result in a table that includes work in process as well as earlier data from jobs that were executed long ago. Both add concealment to the application and load to MySQL. 

Tip 4# Firstly Filter Results by the Cheapest 

The best way of optimizing MySQL is to do imprecise, cheap work first, then the precise and hard work on the smaller that will result in a set of data. 

For instance, consider you are searching for something within an assigned radius of a geographical point. The initial tool in many programmers’ toolbox is the great-circle (Haversine) formula. This will compute the distance along the sphere’s surface. The trouble with this technique is that the formula needs a variety of operations related to trigonometry, which are very intensive toward the CPU. Great-circle calculations tend to perform slowly and make the machine’s CPU utilization skyrocket. 

Before you implement the great-circle formula, cut down your records to a small subset of the total, and scrape the resulting set to a precise circle. A square that comprises the circle either precisely or precisely is a simple way of doing this. That way, the world outside the square never gets hit with all those expensive trig functions. 

Tip 5# Understand the Two Scalability Death Traps

Scalability isn’t as fuzzy as you may think. In fact, there are explicit mathematical definitions of scalability that are defined as equations. Such equations emphasize why systems don’t scale and why they should. Consider the Universal Scalability Law, a definition that is convenient in expressing and quantifying a system’s scalability qualities. It explains scaling issues in terms of two elementary costs: crosstalk and serialization. 

Parallel processes that must hold for something serialized to take place are inherently limited in their scalability. Similarly, if such processes are required to chat with each other all the time for coordinating their work, they restrict each other. If you avoid crosstalk and serialization, your application could scale much better.

SQL Plan Management Oracle – All You Need to Know About

While upgrading the Oracle database or making any changes in the system parameters, you might have noticed that some SQLs’ performance or queries get highly regressed. If this happens, then don’t worry it’s quite obvious and will always happen whenever there are any changes in the plan. But you can prevent this regression if you use SQL Plan Management Oracle.

In case you aren’t aware of what it is and how it can help, then this blog is surely meant for you. In this blog, we are going to cover every basic aspect related to the SQL plan management and that how it will help in preventing query or performance regression.

An Overview of SQL Plan Management Oracle

SQL Plan Management is a deterrent tool that allows the optimizer to manage SQL execution plans automatically while ensuring that only verified and known plans are used by the database.

SQL plan management uses a mechanism that lets the optimizer to use it for a SQL statement. This mechanism is known as a SQL Plan baseline. It’s a set of accepted plans. A plan is typically known to hold all plan-related information such as a set of hints, SQL plan identifier, optimizer environment, and bind values.

The optimizer uses this information for reproducing an execution plan. Commonly, the database accepts a plan into the plan baseline only after it verifies and confirms that the plan performs absolutely well.

In short, a SQL Plan management Oracle is a tool used for mitigating the risk of query regression when you upgrade to Oracle Database11g or 12c.

Key Components of SQL Plan Management

Mainly, there are three essential elements of SQL Plan management. They are as follows:

  • SQL Plan Baseline Capture

The component creates a SQL plan baselines that describes the accepted or trusted execution plan for all relevant SQL statements. If you aren’t sure where you can find the SQL plan baselines, then let us tell you that you can find the plan baselines stored in a plan history in the SQL Management Base. The management base will be found in the SYSAUX tablespace.

  • SQL Plan Baseline Selection

SQL Plan baseline selection makes sure that the tool uses only the accepted execution plans for statements that have a SQL plan baseline. Furthermore, it ensures that it tracks every new execution plan in the plan history for a statement. The plan history comprises both unaccepted and accepted plans. An unaccepted plan can either be rejected (verified but not performant) or unverified (newly found but not verified).

  • SQL Plan Baseline Evolution

This component is meant to assess every unverified execution plan for a given statement in the plan history for either to be accepted or rejected.

What’s the Main Purpose of SQL Plan Management?

SQL plan management oracle restricts performance or query regression due to any plan changes in the database. Secondly, this tool aims at gracefully adapting to changes.

It must be noted that if an event has caused any irreversible execution plan changes such as dropping an index, SQL plan baseline cannot help in this case.

Advantages of SQL Plan Management Oracle

SQL plan management can preserve or improve SQL performance in database systems and upgrades and data changes.

Some more specific benefits comprise the following-

  • When a database upgrade causes the installation of a new optimizer version, it usually results in plan changes for a small part of SQL statements. Due to plan changes, either the performance of the system improves or there isn’t any change literally. However, in some cases, plan changes result in performance regression and here’s where SQL plan baselines come into play. It significantly lessens potential regressions that result from an upgrade.
  • Ongoing data and system changes can have an impact on plans for some SQL statements potentially creating performance regressions. Plan baselines assist in reducing this performance regression. Further, it stabilizes the SQL performance of the system as well as the database.
  • Employing new application modules brings in new SQL statements into the database. The application software is likely to use the proper SQL execution plan that’s developed in a standard test configuration for the new statements. In case the system configuration differs from the test configuration, then the database can develop SQL plan baselines over time to produce better performance.

In the Bottom Line

During an automated or manual updating of statistics for some objects or while changing some parameters related to the optimizer, or any changes made in the system cause a drastic change in the execution plan. Even more dramatic change is noticed while a database is upgraded. While most plans lead to improvement as they are made to adapt to the new system environment, there are some that lead to performance regression as well. In such cases, we need a SQL plan management mechanism whose main work is to reduce the regression risk.

If you are stuck with long queries or if your system’s performance has reduced, you can get a SQL query optimization tool online. Tosska Technologies Limited is one such company that provides solutions and tools related to database and SQL related performance optimization and improvements. We use advanced technologies like AI (artificial intelligence) so that our tool can help you solve numerous tasks at a time.

Don’t Overlook Oracle Database and SQL Performance: Here’s why

oracle database and sql

Being a DBA is not always a fun job, thanks to certain time-consuming tasks that it entails. One of these is to ensure optimal Oracle database and SQL performance. Typically, it is done by spending a lot of time tuning the long list of SQL statements and software code in order to improve efficiency and enhance access. However, SQL is just one aspect that is related to the performance of database systems.  

Database Administrators also need to invest their time in enhancing the design, physical structure, and specifications of the database objects. These objects are the tables, indices, and the information stored over several files. In the case of data inefficiency, it becomes necessary to observe and modify the actual construction and composition of database objects on a consistent basis. This is because any amount of SQL performance tuning is bound to fall short in a database that is improperly organized or poorly constructed.  

Optimizing Oracle Database and SQL: 5 Important Techniques 

The DBA has to be aware of all the specifications that the database management systems consist of as this knowledge will enable them to use the right techniques to optimize database constructs.

A majority of the most popular DBMSs are compatible with all the methods we have mentioned below, though they may be used differently depending on the database. Let’s take a look:

  1. Indexing: An essential aspect of the Oracle database and SQL performance tuning process is by selecting the right indices and alternatives in order to enable efficient queries. 
  2. Clustering: This involves implementing the physical pattern of data on the disk so that it is clustered on the same page whenever accessed in a particular order. 
  3. Compressing: Data is compressed by decreasing storage requirements, thereby allowing more of it to be stored in a smaller amount of space. This also reduces storage expenses and enhances access if you can add a larger number of rows per page.
  4. Freeing Up Space: Assigning extra room for data growth allows new data to be added to its table easily without leaving the table disorganized.
  5. Partitioning: This entails the segregation of one database table into various sections that are saved in several files. This can be done in multiple ways; by partitioning one file in the same computer, partitioning using shared-disk clustering or by shared-nothing partitioning, depending on the DBMS in question.
  6. File Organizing and Placement: Allocating data from both – database systems and data files – to the correct places is a big step in organizing data and improving Oracle and SQL database performance.
  7. Checking the Page Size: The size of the block or the page determines how efficiently data can be stored and accessed, which is why it is vital to use the suitable page size. The smaller the size of the page, the fewer rows per page, which increases sequential data access requirements.  
  8. Interleaving: Merging all the data from several tables in a sequence into a file helps enhance join performance. However, this method seems to have become less popular than it used to be.
  9. Reorganizing Database Objects: Eliminating the defects from the database by reorganizing and arranging database objects is a well-used technique in SQL performance tuning. In fact, it enormously increases performance, especially if the data was previously fragmented, disorganized or scattered in some way.
  10. Denormalization: This method is considered as a last resort attempt in case the database is unable to perform optimally with a completely normalized implementation. This is because it differs from the logical design. 

All of these techniques are useful and should be considered when the DBA creates a plan for tuning and monitoring the database. Each aspect may not necessarily be applicable to every database object but it must be analyzed for its applicability all the same. Moreover, techniques that are not applicable during initial implementation may turn out to be useful as the application undergoes changes over time in various aspects like data volume, usage, and database characteristics.

Tosska Technologies announces the availability of their major SQL performance tuning product – Tosska SQL Tuning Expert Pro (TSE Pro™) for Oracle®

Intelligent SQL tuning without touching your source code

May. 15, 2018

Hong Kong, May 15, 2018 – Tosska Technologies Limited (Tosska), an IT company that provides database and SQL performance related tools, today announces the general availability of Tosska SQL Tuning Expert Pro (TSE Pro™), the major product of the Tosska SQL Tuning Expert Family of Products that provides state-of-the-art machine tuning capability to improve SQL performance for Oracle® without touching the source code.

SQL Tuning is a classical problem for every DBAs. Tuning SQL statements is finding the fastest route to execute the SQL statements. There have already been a lot of SQL tuning products in the market providing better query plan visualization, better statistics analysis, high cost query plan steps indication or even rule-of-thumb syntax recommendations. All in all, those tools are not helpful if users don’t have in-depth SQL tuning knowledge and are not willing to spend extra effort to tune a SQL apart from their daily duties.

Users are eager for one-button-solution tool that can tune a SQL statement automatically without the need of users’ intervention. The Tosska SQL Tuning Expert Product family aims to help DBAs to do SQL tuning just by multiple easy points and clicks.

“Tosska SQL Tuning Expert Pro is a tool for users to improve SQL performance without touching their program source code. Users can even deploy different performance query plans for various sizes of production databases without the effort of keeping multiple versions of the program source. It is especially suitable for package application users who don’t own the source code of their applications,” said KaMing Ng, CEO of Tosska. “If you are a packaged application user, how can you tune your SQL if you can’t edit a query directly? That’s why we decided to expand the TSE Product family and developed TSE Pro™ to solve this problem.”

“There are multiple features provided by Oracle such SQL Profiles, SQL Plan Baselines and SQL patch that you can use to tell Oracle to fix a SQL’s query plan. But the use of these features is limited by Hints injection only, you cannot rewrite a SQL with different syntax and ask the original SQL to accept a rewritten SQL’s query plan. So, hints-based SQL tuning is becoming more important than ever before. Tosska SQL Tuning Expert Pro is the only tool that can provide the most advanced Auto-Hints-Injection solution in the market to fully automate the process from SQL tuning to plan deployment,” said Richard To, CTO of Tosska. “TSE Pro™ also provides an intelligent workload-based index advisor that helps users to review their existing database schema if there are any new indexes which can help to improve a given SQL workload. The TSE Pro™ uses our proprietary Artificial Intelligent (AI) engine which can handle up to thousands of SQL statements and give you a reasonable recommendation that even human experts cannot achieve.”

Free Trial of TSE Pro™ is now available for download from Tosska’s website. Please visit Tosska’s website www.tosska.com for details.

   
About Tosska
Tosska Technologies is a company that focuses in providing solutions for database and SQL related performance optimization and improvements. Our mission is to help users to smooth out the hurdle by our new technologies. Furthermore, Tosska is one of the very few companies in the world that focus in using artificial intelligence technology to solve various database performance problems. It is our goal to help our customers to reduce their hardware investment; increase their database applications service level and free up their human resources for more strategic activities with our innovative technologies. For more information visit www.tosska.com or email us at enquire@tosska.com.

Press and Media Inquiries
KaMing Ng
Chief Executive Officer
Tosska Technologies Limited
Phone: +852-28248420
Email: enquire@tosska.com

All Trademarks mentioned on this Site are the property of their respective owners.