We will continue our discussion from where we had left in part 1.
If you haven’t looked into previous article, you can navigate to and go through first two tips served in that discussion at this URL:
https://www.blisswebsolution.com/mysql-database-performance-improvement-tips-part-1/ query parameters
Presented below is more discovery on remaining optimization techniques to take your website or mobile application data management to the next level.
- Use Percona server instead MySQL
As we know, Percona RDBMS is a drop-in replacement for MySQL and provides superior performance, scalability, and instrumentation.
One of many emphatic functionalities built in Percona is – its precious XtraDB storage engine.
XtraDB is fork of InnoDB.
It is designed for better scalability on modern hardware and to manage memory efficiently on high load environments.
Unfortunately, Percona server is available only for UNIX-like systems, so this implementation will come in handy only if your setup meets operating system requirements.
Below is step-by-step procedure to install Percona database server on any Debian or Debian-derived system.
Elaborate Process:
You should be administrator of system or you should have ROOT username and password in first place.
- Open sources.list file which should be pre-given in downloaded sources. Then add following 2 lines at its end.
deb http://repo.percona.com/apt jessie main
deb-src http://repo.percona.com/apt jessie main
Save and close the file.
- Update entire system:
sudo apt-get update
- Install the server with this command.
sudo apt-get install percona-server-server-5.5
- Edit options to fit to your infrastructure and company environment.
You are done for integrating Percona with operating system software.
- Use MySQL performance monitoring tools
There is always need to have powerful monitoring tools to check the performances of database and network servers.
Many tools are available in the market to handle MySQL database and its derivative servers. These tools comes in all sizes and shapes with wide range of free or paid features.
Few are closed sourced, owned by private companies and may be providing better support to solve issues in real time.
But most tools are free and open source.
For benefits of all users, we will look into top free and equally capable tools as that of premium products in terms of functionalities delivered by them.
Although GUI based softwares are insisted these days, the command-line tools are more powerful and the best to use, they take little time to understand but once you get used to with them, you will appreciate flexibilities offered by them.
4.1 phpMyAdmin
Being the most famous, widely used and web-based open source product, it provides some versatile tools in it to monitor MySQL server.
We can see many things graphically as well as in table format, like
- Connections/Processes
- System CPU Usage
- Traffic
- System Memory
- System swap
etc.
and much more we can add.
It also possess Advisor component and related screens, which can suggest performance and memory related recommendations on itself.
4.2 MySQL workbench
Also being fully equipped with elite tools as that of phpMyAdmin, difference is that it is desktop application.
It provides us with a clean, un-cluttered dashboard and other diagnosis data related to the server in an elegant manner with all the details.
4.3 Percona Toolkit
This one is command based tool alike both tools mentioned above which are mostly GUI.
Now, suppose we have to do analysis for
- Slow queries
- Old archive
- Index optimization
and want to delve with gamut of information that is interlinked with each other.
For these kinds of specific purposes, Percona toolkit is excellent and very reactive to answer through its result-sets.
We shall understand this by example of one of its actual command-line tool.
pt-query-digest
It analyzes all including slow queries from binary log files.
We get sophisticated report after running preceding command.
Take for example, we issued following as its argument:
pt-query-digest /var/log/mysql/mysql-slow.log
After entering the preceding command in the terminal, we will see a long report.
It will show following things regarding each SQL statement
- Total time for execution of all queries
- Percentage (pct) of time for individual SQL
- Min, max, and average time of complete execution
- Bytes sent to and received from server
etc.
Similarly, we also have facility to establish high performance cluster to achieve high availability with Percona XtraDB Cluster (PXC), it is already adopted by many businesses rather being in R&D level phase.
With robust genre, PXC manages a database on multiple servers and provides safety or redundancy in case main server fails.
Cluster environment delivered by PXC effectively divides application load between nodes.
The topic reserves entire textbook on it and can not be covered here.
Please look into next blog or part 3 of MySQL database performance improvement tips to understand remaining two techniques and related suggestions.
Continue exploring more at.. https://www.blisswebsolution.com
Simply to read more articles on Web application development areas or related to open source databases and technologies, navigate to https://www.blisswebsolution.com/blog/
If you are a tech person or MySQL update related news interests you, you can attend our discussion forum without any obligation, just drop an e-mail at enquiry@blisswebsolution.com.