Author: Webyog

Monitoring MySQL Problematic Queries

This blog describes how to identify queries that cause a sudden spike in system resources as well as the user and host who executed the culprit query using the Monyog MySQL Monitor and Advisor.
How many times have you seen a system go live and perform much worse than it did in testing? There could be several reasons behind bad performance. For instance, a slow running query in MySQL can be caused by a poor database design or may be due to higher-than-normal latency in network communication. Other issues such as using too few or too many indexes may also be a factor. This blog will identify the types of poorly performing queries and outline some concrete strategies for identifying them using monitoring. Finally, some tips for improving performance will be presented.
The Effects of Misbehaving Queries
Typically, misbehaving queries will result in two possible outcomes: high CPU usage and/or slow execution. The two issues tend to be related to some degree, because one will lead to or exacerbate the other. Depending on the root cause, the problem may be a database configuration or query issue. For instance, as MySQL databases grow in size, tables get fragmented over time. This contributes to MySQL load spikes. Protecting a server from MySQL high CPU issues requires close monitoring and periodic optimization of the database. Meanwhile, a query that requires a high degree of type conversion will also place a burden on the CPU.
Identifying the culprit requires a different approach based on the dominant outcome: high CPU usage and/or slow execution. In the next sections, we will examine how to track down both causes.
Some Well-known Causes of Slow-running Queries
Without knowing the root cause of a slow running query, it’s difficult for a DBA to troubleshoot the problem. Therefore, the first step should be to check efficiency of all the database components before going to use a query monitor or optimizer. This check will help to understand whether the root cause of the problem is related to a query or something else. Here are a few potential causes to consider:

Network latency: Is the slowness limited to a specific query, batch process, database, or are other network resources suffering as well?
Another reason behind SQL performance issue could be a bad index creation or accessing a bad index from the specified queries.
Choosing a slow execution plan may degrade performance.
Running a single query at a time may go smoothly, but check if running multiple queries at the same time hampers server performance.
If someone is experiencing a bad performance issue with database components, then a System Monitor can be quite helpful. By employing a System Monitor, performance of both database and non-database components can be monitored.
Ad hoc SQL queries that are run outside of a stored procedure: stored procedures almost always offer better performance because MySQL can cache their execution plans; ad hoc queries should, whenever feasible, be converted to stored procedures.
Long-running or CPU-heavy queries in execution plans. Table scan operations indicate the lack of a suitable index, and putting an index in place to eliminate the table scan can have an immediate and positive effect on performance.
Queries that include a large number of joins. Joins take time, and while MySQL Server is obviously designed to handle them, a large number of joins can really slow things down. A good general rule of thumb is to limit the number of joins to seven; if you have more than that, you may have to start looking at ways to cut back.
A slow-running query that always runs slowly. This is a query that could perhaps be rewritten to perform better. A query that runs slowly some of the time is one that’s likely being affected by outside factors, such as locks or resource contention.

Employing Monyog Tools
Using a Monitoring and Profiling tool such as Monyog will help in improving the performance issues that are related to queries. Monyog can display long running queries (Queries that holds a large number of resources) in MySQL, as well as a host of other potential issues, such as hanging threads and improper index usage – i.e. over or under utilization.
The Overview Page
As soon as you’ve logged into Monyog, the Overview page provides a high level picture of all the selected servers registered with Monyog. Below the count of the total servers registered with Monyog, total number of disconnected servers, and servers having critical alerts and warnings, you’ll find the top 10 queries across the selected servers in Monyog, based on total execution time:

Monyog Overview – Top 10 MySQL queries

Queries with the longest execution times are positioned at the top of the list, giving you an immediate place to start looking at bottlenecks. You can click on a query to get more details. These give the list of server names on which the particular query was executed. Clicking on the server names will open the sniffer for that server with the time-range selected as the first and last seen of the query.
Index Usage
Beyond the Overview page, the Index Usage monitor group is good starting point for identifying the presence of table scans. You should always try to keep these as low as possible by building indexes on searchable fields.
Although this screen does not relay information about specific queries, the monitor groups in Monyog read the MySQL Slow Query log do provide that information (covered in the next section). Once you have identified the problematic queries, you can create the appropriate indexes or rewrite the queries to use indexes.

Monitors tab – Index usage

Examining the MySQL Slow Query Log
The MySQL slow query log is a log that MySQL sends slow, potentially problematic queries to. Generally the queries that are logged are those that take longer than a specified amount of time to execute or queries that do not properly hit indexes.  Queries that do not use an index may not be slow if there are only a few hundred or few thousand records in the table(s) involved. But they are ‘potentially slow’ and should be identified if they access tables, which will continue to grow.
This logging functionality comes with MySQL but is turned off by default.  You can check whether or not it’s turned on from the MySQL Logs screen.

Monitors tab – MySQL logs

The No. of Slow Queries relays how many queries are taking longer than the Min. execution time for a query to be considered slow threshold (based on the MySQL long_query_time variable).  On the right, there is a chart icon that, when clicked, opens the TREND VALUES chart.
Selecting “History” from the TIMEFRAME dropdown at the top of the Monitors page allows us to group trend data by minutes, hours, days, weeks, months, or years.  More precise timeframes help to more accurately tie the offending query to the slowdown event:

Monitors tab – No. of Slow Queries

Thread Monitors
Accessible from the left-hand button bar (refer to the highlighted icon below), the Threads page shows you the number of threads currently being executed by MySQL fetched using the query SHOW FULL PROCESSLIST. Each query sent to MySQL is executed in a thread. The Threads feature should be used to check which queries are being executed presently. It gives you a general sense of what is keeping your server busy at that moment.
While not the best feature for monitoring queries executed over a period of time (that would be the Query Analyzer presented in the next section), it can nonetheless be employed to locate runaway processes.  Under the Actions heading on the far-right, there are buttons to kill each thread:


Real-time Query Monitoring
Monyog also gives us the ability to monitor MySQL servers via Real-time monitoring.  It may also provide invaluable information on poorly performing queries. To show performance metrics:

Click the (Real-time) Clock icon on the left-hand side of the screen.

On the next screen:

Select a server to monitor.
You may then choose to start a new session or load a saved one. 

Realtime MySQL Monitor

Like the Overview page, Monyog’s Query Analyzer screen also displays The Average Latency and the Total Time taken by each query to execute.  In addition, you’ll find the user and host who executed the query.
You can delve deeper into a query’s mechanics via the EXPLAIN command by clicking on the query and selecting the Explain tab on the Query Details screen:

Query Analyzer

The Explain Result’s Type column describes how tables are joined.  In the above Explain Result, we see a type of “ALL” in combination with an absence of keys.  That indicates that a full table scan is being done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked “const”, and usually very bad in all other cases. You can usually avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

MySQL queries details screen

“Performance Schema” Mode for Data Collection
The latest update of Monyog brings new easier ways to find problem SQL in Real-Time, including “sniffer” based Query Analyser using Performance Schema.  It allows you to view the success/failure status of every query executed and also the number of ‘full table scans’ for queries in a single glance.
Monyog’s query sniffer is a functionality that records a ‘pseudo server log’ and stores it in the Monyog embedded database.
The Sniffing Mode is set on the ADVANCED tab of the server properties dialog.

Server properties dialog

With ‘Performance Schema Sniffer’ enabled on the Query Analyzer screen, you can include many additional columns to the analysis, including a count of Full Table Scans.

Query Analyzer screen with manage columns panel

Here is the Query Analyzer screen in Sniffer mode with the Full Table Scan column added:

Query analyzer – sniffer

A Few More Tips…
Some other ideas that may help fix CPU problems:

Run SHOW FULL PROCESSLIST; while the CPU load is high.  This will show you any queries that are currently running or in the queue to run and what it’s doing.

Keep an eye on things like your buffer sizes, table cache, query cache and innodb_buffer_pool_size (if you’re using innodb tables) as all of these memory allocations can adversely affect query performance which can cause MySQL to consume CPU cycles.
If you are using WordPress, its plugins are notorious for doing monstrous queries.
Try modifying the innodb_buffer_pool_size parameter.  It should be set to at least the size of the file ibdata1, which is located in /var/lib/mysql.  InnoDB works much more efficiently when it is able to be resident in memory. This may be impractical in some situations because the ibdata1 can be quite large.  The innodb_log_buffer_size parameter should be 25% of the size of innodb_buffer_pool_size.
Give MySQL at least half of available server memory if possible.

By using the Monyog features described in this blog, you should be able to identify the queries and/or processes that are causing system bottlenecks.   You can then make the changes to improve the performance by modifying the query, Indexes and database design, based on Monyog’s Advice text.
SQL query-performance tuning is as much art as science, and is thought by some to belong to the realm of application development rather than Database Administration.  Under that assumption, the goal of DBAs would be to identify those slow-running or CPU-intensive queries, gather evidence and then work with developers to find ways of improving them.
Monyog is an agentless MySQL monitoring tool that can uncover key MySQL performance insights. You can download a 14-day free trial here.
The post Monitoring MySQL Problematic Queries appeared first on Webyog Blog.

A complete MySQL monitoring system with early-warning mechanisms: Liam Terblanche, CTO, Airvantage

Airvantage is a Value Added Service Provider for mobile telecommunication networks. Airvantage offers various gaming platforms, and subscriber retention initiatives like emergency airtime, mobile money integration, etc.
A simple solution for staying compliant
Airvantage host each customer’s database on its server (virtual) as they are bound to do so from a legislative perspective. They are presently monitoring several production servers that are spread over various countries in Africa and the Caribbean. Looking at many tools, including ManageEngine and some open source alternatives, Airvantage opted for Monyog for its simplicity, ease of config, and relatively affordable price-tag.
Liam described what he needed was “a complete MySQL monitoring system with early-warning mechanisms which allow him to sleep much better”.  With Monyog, Liam instantly started relying on the 600+ monitors and alerts to become better at proactively monitoring MySQL.
The straightforward and intuitive interface of Monyog along with the default set of alerts made the user experience truly delightful for Liam and his team. From all the benefits Monyog offers, he reckons comparing Server Config feature is the most useful ensuring all the servers are similarly configured for their particular system requirements.
Agentless Monitoring
With the agentless architecture of Monyog, Airvantage could further stay compliant, and Liam is looking forward to considering the similar approach with other solutions as well. He mentioned that “to simply connect to any remote OS/ MySQL and start monitoring, is the way it should always have been.”
Customer Satisfaction
Liam had a particular use-case where he needed an alert if the checksum of a specific table changed.  When asked about the sales and support interaction experience, he said that “the guys sat with me and assisted me until it was working to my satisfaction. The salesperson and subsequent technical support were first-class.”
You can download Monyog free trial here.
The post A complete MySQL monitoring system with early-warning mechanisms: Liam Terblanche, CTO, Airvantage appeared first on Webyog Blog.

RDS / Aurora OS monitoring with Monyog v8.1.0

With this Monyog release, we will provide monitoring capabilities for RDS / Aurora OS metrics along with an emphasis on the User Experience in several respects including a number of GUI design details. Additionally, the release adds a number of bug fixes and implements a number of user requests.
Changes as compared to Monyog MySQL Monitor 8.0.4 include:

It is now possible to get OS metrics from Amazon RDS/Aurora (but not Azure, where interface for same is disabled).
Added an option to generate a token in Monyog to be used with the MONyog API as an alternative to Monyog user and password.
Added an option to define a “seconds_behind_master” setting in Replication page determining if the slave should be considered in sync or not. On some environments, slave will rarely be fully in sync and in such cases, the alerting was not really useful before. location on Linux can now be changed from MONyog.ini file.
Reintroduced the tabular view for Replication overview page. Now, the user can choose between tabular and graphical view.
Added an option to select the way the server selector should work in Monyog, i.e: same servers across all pages or different across pages

Bug Fixes:

Seconds behind master could show wrong value in the chart.
Export as CSV didn’t work for “Locked and Locking Queries” in Real-Time.
On HDMI and higher resolution monitors, overview page could display distorted.
Extra columns added from manage columns in Real-Time were getting reset to default set of columns on moving away from the page.
In Dashboard the value of min/max/avg displayed as 0 if number of points was larger than 10,000, Also, if the chart was exploded, the chart would display empty.
Some counters did not show values formatted properly.
In mail alerts for counters in multi-replication monitor group a newline character in subject could cause the mail to display unformatted in some mail clients – including gmail web interface.
In “long running query” alert there was a bare line feed character (\n) which could cause reading failures in some environments.
History trend reports will show date as well in the timestamp if report spans more days.
Collection and Purging interval was not setting properly while editing a UDO.


Redesigned the interface for adding new servers.
It is now possible to display the “Replication Overview” page and set auto-refresh interval similar to Monyog before version 7.o. Different users have different preferences here.
When installing Monyog on a Linux machine with kernel version less than 2.6.32, an error message will now inform that Monyog needs a kernel version or 2.6.32 higher.

You can download Monyog free trial here.
The post RDS / Aurora OS monitoring with Monyog v8.1.0 appeared first on Webyog Blog.

Webinar series – A step-by-step process to optimize MySQL database performance

Hope you have been following our recent webinars on MySQL, Galera Cluster, AWS monitoring and more.
We are coming up with a webinar series in association with Eric Vanier, a leading MySQL expert consultant. The series will provide a step-by-step process to optimize MySQL database performance.
This webinar series is for everyone who is looking for ways to monitor their MySQL databases, simplify the process to manually analyze queries and achieve faster issue resolution time.
Eric Vanier will focus on key problem areas that are faced by DBAs and Shree will provide a solution-driven demonstration to overcome issues while monitoring the database performance. To make it simple, the series will comprise of three parts:
Part – 1: MySQL Performance Tuning
26 June; 10:00 am Eastern time
Troubleshooting a MySQL server performance problem is often a tedious activity as it’s difficult to figure out where to start and how to cope up with it. In the case of an increase in the volume of data and load on the server, the queries tend to run slower and deadlocks become a frequent occurrence.
In this webinar, you will learn the following:
– 3 different ways to identify slow queries.
– Spot query performance pattern over a particular period.
– Proactive monitoring by setting up server/ tag specific alert.
– Fetching deadlock information.
– Monitor MySQL log files on a remote machine.
– Product comparison between Monyog and MySQL Enterprise Monitor.
Register here
Part – 2: Real-time monitoring and RDS file-based log monitoring
03 July; 10:00 am Eastern time
Many of our customers use Monyog to help monitor data in real-time and find the problematic queries. Moreover, Monyog is the only tool that provides file-based log monitoring for Amazon RDS for MySQL & Aurora on RDS.
In the 2nd part of our webinar series, you will learn the following:
– Analyze queries without slow query file.
– Monitoring queries in real-time.
– Monitor log files for RDS instances.
– Performance overhead caused by Monyog.
– Hardware requirement for Monyog.
– Monyog architecture.
Register here
Part – 3: Achieve faster issue resolution time
10 July; 10:00 am Eastern time
In this final part of the webinar series, we will focus on effective practices of monitoring MySQL databases using Monyog. This will provide as in-depth learning session for making the best of the monitoring tool.
Learn the following in this webinar:
– Using explain plan in Monyog.
– Quickly monitoring database and tables size.
– Create Monyog users with restricted access and privileges.
– Minimize the data generated by Monyog.
– Create aggregated report of the data collected in Monyog.
Register here
If you are not able to attend the webinar live, register anyway and we’ll send you a link to the recording once the webinar session has ended.
The post Webinar series – A step-by-step process to optimize MySQL database performance appeared first on Webyog Blog.

Getting Started with MySQL Replication for High-Availability

Many organizations have MySQL or MariaDB databases at the core of their business—for processing and storing product sales, collecting information related to services offered, or just providing essential information to customers. As a result, keeping these databases running continuously can be critical for the success of an organization.
There are many components of a database system that a database administrator will need to consider for maintaining high availability. We considered server equipment (e.g., memory) in a previous introductory article. Now let’s look at using multiple servers for your MySQL and MariaDB databases—let’s look at replication.
Replication Overview
One common and effective way to structure a highly available database system is through some form of database replication. There are a few reasons for using replication. One reason is for load balancing: you can split user traffic between servers, sending write traffic (e.g., UPDATE statements) to the master and read traffic (e.g., SELECT statements) to a slave—or distributed to multiple slaves. If this is new to you, it may seem complicated. So let’s keep it simple to start. We’ll look at how to set up a second server to replicate an existing database server: we’ll set up a slave. We’ll cover more complex replication systems in another article.
Basically, you designate one server as the master and another server as a slave. All changes that are made to the databases on the master are also made automatically on the slave. As for load balancing, we’ll cover it in another article. To start, you can set up replication to have an extra machine that you can use as a spare in case your main server fails, and for making back-ups.
Preparations on the Master
There are only a few steps to configuring replication. First, there’s no software to purchase or install—replication is built into MySQL and MariaDB. On the master, you will need to enable binary logging. To do this, add log-bin on a separate line to the database configuration file (e.g., my.cnf). Also, add a line with server-id to give the server a unique identifier—the number 1 is fine. Below is an excerpt from the configuration file showing these two variables. Be sure to restart MySQL when finished adding them.
Next, you will need to create a user account for the slave to use when communicating with the master. The slave doesn’t query the databases on the master. Instead, it requests new entries to the master’s binary log. This log records all changes to the server (e.g., SET statements), database structure (e.g., ALTER TABLE), and data (e.g., INSERT). The changes to the server, schema and data are all that’s needed for replication. The binary log doesn’t include SELECT statements. The slave doesn’t need them.
So, the replication user needs only the REPLICATION SLAVE privilege, which allows it to get updates to the master’s binary log. You can execute this SQL statement on the master to create such a user:
TO ‘replicator’@’12.345.0.2’
IDENTIFIED BY ‘its_pwd’;
You would replace the name replicator with a username you prefer. Use the IP address of the slave server—not 12.345.0.2, as shown here. And provide a better password.
Now you need to make a complete back-up of the master’s databases to transfer to the slave. You can use the mysqldump utility to do this:
mysqldump -p –user backup_user \
–master-data –flush-logs \
–all-databases > full-backup.sql
You will have to change the username from backup_user to whatever user has the privileges needed to make a full back-up of all databases. The –master-data option tells mysqldump to include information on the master in the dump file. The –flush-log option tells mysqldump to flush the binary logs so you have a fresh start.
Configuring the Slave
On the server which is to be the slave, install MySQL or MariaDB. You should use the same software and version and release as you’re using on the master. In its configuration file, set the server-id equal to 2 or some other unique number. Also add the option, read-only so that no one will change the data directly on the slave. You’ll need to restart MySQL on the slave for these options to take effect.
If you haven’t already, copy the back-up file from the master to the slave. You could use FTP or a similar method. Here’s how you might do this with scp from your home directory:
scp -i ./.ssh/my_key.pem \
ec2-user@12.345.0.1:/home/ec2-user/full-backup.sql .
This line is set for copying between two AWS instances, which is fast. For other systems, you may have to authenticate differently, without a key. Once you have the database copied, you can use the mysql client to load the databases contained in the back-up file:
mysql -p -u root < full-backup.sql
When that’s done, you need to provide the slave with the information it needs to authenticate with the master. To do this, log into MySQL on the slave as root and execute the following SQL statement:
The host here should be set to the master’s IP address, not the slave’s IP address. When you’ve done this, you’re ready to start the slave. Just execute the following on the slave, from within MySQL:
At this point, the slave should be replicating the master. Try changing or adding some data on the master to see if it’s immediately replicated on the slave. You can create a table in the test database or insert a row into a table on the master. It should immediately reproduce whatever you do on the slave—but not SELECT statements. If it didn’t work, you’ll have to check the server’s status to troubleshoot the problem.
Initial Troubleshooting
MySQL and MariaDB provide a SHOW statement for monitoring and troubleshooting replication. Execute the following statement on the slave to check its status:
Slave_IO_State: Waiting for master to send event

Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Last_Errno: 0

The results will tell you plenty, but we’ve listed just a few variables here for you to consider initially. In particular, look to see if the fields Slave_IO_Running and Slave_SQL_Running each say, Yes. If they do, that means it’s replicating. If the IO and SQL threads on the slave aren’t running, check for error messages (i.e., Last_Errno and Last_Error). This may give you a clue as to what is the problem.
If you’re still having problems, execute SHOW MASTER STATUS on the master. It will show you the name of the current binary log file in use by the master, and the position number of the last entry in that binary log. Compare this to the results from SHOW SLAVE STATUS on the slave. The values for the master should be the same as on the slave. Also, check that the user name for the slave, and the host address for the master are correct, as well as the server identification number.
Setting up replication the first time should go smoothly, if you did all of the steps given above, set the user names and hosts correctly, and started with a fresh install on the slave. If troubleshooting becomes too difficult, though, you can start over: you can uninstall MySQL and delete the data directory for MySQL on the slave and then try again. Be care you delete the data directory on the slave, and not on the master.
Monitoring Replication
Once you have replication installed and it’s been running for a while, you’ll discover two things: it works well and it will stop unexpectedly—without notifying you. This means you will have to execute regularly the SHOW SLAVE STATUS statement on the slave to see if replication is running. Besides checking the IO and SQL threads mentioned above, you should also check the Seconds_Behind_Master field. If it’s too far behind the master, there may be a problem with your network.
Besides what’s provided with SHOW SLAVE STATUS, you might want to check SHOW STATUS for slave related information:

| Variable_name | Value |
| Slave_connections | 0 |
| Slave_heartbeat_period | 1800.000 |
| Slave_open_temp_tables | 0 |
| Slave_received_heartbeats | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | ON |
| Slave_skipped_errors | 0 |
| Slaves_connected | 0 |
| Slaves_running | 1 |
If everything is going well, this won’t show much. But you may want to check this information, regularly. For instance, if Slave_retried_transactions shows a high number, that might indicate a problem. In which case, check the error logs for more information.

Monyog – Add a Slave Server

If you have Monyog installed and monitoring your server, you can add a second server, the slave easily”.
Monitoring a slave and replication is much easier if you have Monyog installed already and are monitoring the master—if not, download it and try it. You can use Monyog to monitor both the master and the slave. It will provide you with information on all of the variables from the SHOW statements mentioned. You can also create alerts to notify you when the slave stops replicating or has other problems. Let’s go through how to add a slave to Monyog.
First, there a few things to do to prepare the slave. Create a user for Monyog on the slave. This user will need only REPLICATION CLIENT and SUPER privileges to monitor replication:
ON *.* TO ‘monyog’@’12.345.0.1’
IDENTIFIED BY ‘its_pwd’;
Now open Monyog in your web browser and click on Servers in the left margin. You’ll see the one server you already added—assuming you’re already using Monyog. You probably called it Localhost. For clarity, you might want to rename it. Just click on the ellipses for the box for the server and you’ll see a list of choices. Choose Edit Server. The first box is labeled, Name. Change the name from Localhost to Master, then Save.
Back at the Servers page, click on ADD NEW SERVER at the top. A panel will open on the right (see screenshot). Name the new server Slave or whatever seems appropriate for you. Enter the IP address of the slave in the MySQL Host box. Enter the Username and Password for Monyog on the slave. Everything else is fine for now with the default settings. On Amazon’s AWS, you may have to edit the Inbound Rules for the to allow MySQL traffic from the master. Once you’re done, click the blue button, Test MySQL Connection.
When the new server tests without a problem, click on the Advanced tab. Look for Replication in the list of monitor groups. Click its plus-sign to add it. You’ll see a line that reads, Is this replication slave? This is necessary to gather information about replication on the slave. Enable it. By the way, on that same screen you may notice that there is an option to Auto-register All Slaves. If you enable this on the master, you won’t need to add the slave‐it will add automatically a new slave when it detects it interacting with the master.

Monyog – Monitoring Replication

Monyog is now monitoring the slave. Let’s see how that looks. Click on Monitors in the left margin, the select Replication from the list of monitor groups (see screenshot). Here you’ll see the results of SHOW SLAVE STATUS and the replication related fields from SHOW STATUS. Besides being a nicer display, if you click on the flag icon for a monitor, you can have Monyog send you an email or an SNMP trap to alert you when it exceeds parameters you set. At a minimum, Monyog will be monitoring Slave_IO_Running and Slave_SQL_Running and will alert you if those values switch from Yes to No, indicating that replication has stopped. You may just need to edit the server again and make sure you have the email address you want in the Notification section.
Replication can be useful for many things related to high-availability. At a minimum, as we said, you can use it as a hot spare or for making back-ups of the databases. We’ll look at back-ups related to high-availability, as well as other methods related to replication to improve high availability of databases, in upcoming articles in this series on high-availability.
You can download a 14-day free trial of Monyog MySQL monitor here.
The post Getting Started with MySQL Replication for High-Availability appeared first on Webyog Blog.

Getting Started with MySQL High-Availability

Keeping databases running consistently and continuously is crucial to many organizations. When your site or application fails to load because of problems with your databases, you risk losing revenues—especially a business with a high traffic site which is the main source of revenues. If it happens often enough, you’ll lose not only transactions but customers.
There are many reasons why a database system may be unavailable, or at least not consistently available. It could be straightforward problems with your databases, or it could be hardware limitations. There are several potentially weak components of a database system. It’s important to know where are the potential weak points and to have a clear sense of what’s required to maintain a highly available database system.
If this concept is moderately new to you, it may be overwhelming. However, please understand that it’s achievable and learnable. You can start by focusing on one component, one area of potential weakness and then move on to strengthening the next. Start by determining what you have, how it’s configured, how it can be improved, and then try making some changes. For many changes, the results will show nothing but will prevent server problems later.
Checking Memory
Let’s go through some of the hardware vulnerabilities that can potentially cause problems for your databases. A common problem that will disable MySQL is not enough memory (i.e., RAM). You can see how much memory you have, how much is used and how much is free with the Linux command, free:
free -wh

total used free shared buffers cache available
Mem: 1.8G 783M 120M 54M 0B 933M 793M
Swap: 1.0G 185M 838M
This command will tell you how much RAM is on the server, but also how much swap space has been allocated. You may discover you don’t have enough memory available. In which case, you may need to add more RAM. You might discover you haven’t enabled swap space. If so, enable it. Incidentally, the options -wh are not available for all versions. As an alternative to using free, you could read
the contents of the /proc/meminfo file.
At a minimum, MySQL will need RAM for storing the grants tables, as well as caching the information_schema for the server and for every session. It needs memory for many things. If there’s no RAM available for any of this, it will lock MySQL.
Checking Hard-Drives
The next potential hardware problem is the hard-drive. To check how much hard-drive space you have available, you can use the df command like so:
df -h

Filesystem Size Used Avail Use% Mounted on
/dev/xvda2 500G 169G 332G 34% /
A full hard-drive can cause problems for MySQL. For instance, when MySQL executes SELECT statements that use ORDER BY or GROUP BY clauses, it will retrieve all of the rows from the storage engine and then create a temporary table to store the results before sorting or grouping the data. It will store that temporary table in RAM—if there’s enough memory available to hold it. Otherwise, it will store the temporary table on the hard-drive in a temporary directory (see tmp_dir variable for the location). This is slower but necessary for large amounts of data. If there is very little RAM available, it may have to write to the hard-drive for every query. That will greatly reduce performance. However, if the hard-drive is full, it will not be able to create temporary tables. This will also cause MySQL to lock.
There are other hardware considerations for maintaining MySQL high-availability of a database system, such as using better quality hard-drives, checking your CPU usage (try the top utility for this) and network equipment and configuration. Making sure you have good equipment and that MySQL has enough room in which to work is the first step in ensuring MySQL high-availability.

There is one thing with which you should be aware when using the command-line utilities mentioned: they provide static results—they only tell you the state of the server at the time they’re executed. If you run out of memory or hard-drive space during the night while you’re sleeping, your database system will be down. The problem might be resolved by the time you wake, but you will be unaware that it occurred, although your customers in other time zones may be very aware that there was a problem. Unfortunately, you can’t count on customers to tell you when there is a problem. You need a method to monitor these key hardware components.
You could write a set of shell scripts to check regularly the server using several command-line utilities and record in a log file when values exceed certain levels. That might work well, but it’s a lot of trouble to create such scripts to check everything. If you want to have alerts sent to you when there are problems, and if you want to track usage so that you can watch for trends, that’s, even more, programming work. All of this, though, is easy to do with Monyog. If you don’t have it already, consider downloading it.

Monyog – Edit Server
If you have Monyog installed on your server, open it in your web browser and click on Servers in the left margin. Look for the box for your server—probably labeled, localhost. There will be ellipses on that box, indicating more options. Click on the ellipses and you’ll see a list of choices. Choose Edit Server (see screenshot). If this isn’t a new installation, you probably have the server configured already for MySQL. You just need to enable SSH to be able to use the Linux monitor group. So click on the SSH tab and then the switch to enable it. Choose the OS system used—we’re assuming Linux for this article. If Monyog is installed on the server which is running MySQL, try using for the host. Port will probably be 22. Next, enter the username for ssh. If you’re using Amazon’s AWS, it might be something like ec2-user. Monyog can authenticate with a key, but it has to be an OpenSSH key. Monyog can also authenticate with a password, but you may have to edit /etc/ssh/sshd_config and set PasswordAuthentication equal to yes. When you’re finished, click on Test SSH Connection. If it works successfully, click Save. For this to work, you will need to have a user named, monyog on the Linux filesystem and that user will have to have access to the /proc directory and should be in the same group as the mysql user. If you already have Monyog running and monitoring MySQL, all of this is probably already done.
Now you’re ready to enable the Linux monitor. Click on Monitors in the left margin. Then click on the icon at the top right for managing monitor groups—it looks like a list of bullet items. There will be a list of group choices for what Monyog will monitor. Scroll down and enable Linux, and then Save. Now Monyog is monitoring several things on your Linux server.

Monyog – Linux Monitor Group
While still on the Monitors page, click on the Linux monitor group—it’s probably at the bottom of the list of monitor groups (see screenshot). You’ll then be able to see plenty of information on the server: CPU usage, total memory, memory used and available, swap memory, and hard-disk space. You can also see how much memory MySQL is using. If something isn’t being monitored on the server, you can click the plus-sign next to the heading, Monitors to add a monitor. You will need to enter the command and settings for Monyog to collect the information.
Looking again at the Linux monitor group, if you click on the small bar-graph icon for a particular monitor, you can see a graphical representation of the information over time. This will help you to spot trends. If you click on the flag icon for a monitor, you can have Monyog send you an email or an SNMP trap to alert you when it exceeds parameters you set. This will allow you to detect a threat to the high-availability of the database servers so you can have time to resolve a problem before there can be a loss of service.
Achieving high-availability for database servers can require some work and vigilance for a DBA. There are many tools built into Linux and other operating systems that can help. You can monitor the common culprits that threaten high-availability, either manually or by creating your own shell scripts or programs. Monyog can make all of this easy—and you can implement it today, with very little effort and no programming.
Once you’ve made sure you have good equipment, that’s it’s configured properly, and you’re monitoring system usage, including alerts for when parameters you set are exceeded, you’re ready to consider the next component in maintaining high-availability: multiple servers. We’ll look at that option in the next article in this series on high-availability.
The post Getting Started with MySQL High-Availability appeared first on Webyog Blog.

Joint Webinar May 17th: High-Availability & Scalability with Galera Cluster for MySQL

We will demonstrate the ease of monitoring Galera Cluster using Monyog MySQL monitor. By the end of the webinar, you will have a better understanding of how to use Monyog for monitoring Galera Cluster for MySQL to achieve a required balance of high availability and scalability.
Galera Cluster for MySQL is a multi-master active-active cluster. It is an easy-to-use, high-availability solution, which provides high system uptime, no data loss, and scalability for future growth. In this webinar, we will give an overview how does Galera Cluster work and what are the benefits of using Galera Cluster.
Join USA timezone webinar Wed, May 17, 10:00 AM – 11:00 AM PDT
Join EMEA timezone webinar Wed, May 17, 11:00 AM – 12:00 AM CET
This webinar will be co-presented by Shree Nair, Product Manager, Monyog and Sakari Keskitalo, COO, Codership.
The post Joint Webinar May 17th: High-Availability & Scalability with Galera Cluster for MySQL appeared first on Webyog Blog.

Highlights: Monyog v7.04 demonstration & Roadmap Update

Thank you everyone who attended our Webinar on “Monyog v7.04 demonstration & Roadmap Update”.
During the webinar, Shree gave a complete walkthrough of the all new Monyog v7.04. He also shared the product roadmap along with the performance improvements for bigger deployments.
Here’s the complete video for all those who couldn’t attend the webinar.
We hope you found the webinar useful. We will be conducting more webinars in upcoming weeks. To keep yourself updated, subscribe to our blogs.
Download a free trial of Monyog here.
The post Highlights: Monyog v7.04 demonstration & Roadmap Update appeared first on Webyog Blog.

A day after Thanksgiving | Black Friday is here

If you’ve missed out on our Thanksgiving special prices, don’t worry – Black Friday sale is here.
We thought we’d kickstart your Black Friday savings by offering a 30% discount on SQLyog & Monyog.
Use coupon code: BF30
Hurry up, this offer is valid until 30th November, 23:59 PST.
Upgrade your SQLyog community edition to the commercial version to save tons of time on a daily basis. Shop here.
You can optimize your MySQL database performance with Monyog – The most secure & scalable MySQL monitoring tool. Know what’s in store for you.
If you’re an existing customer and want to extend your license – Head right away to Customer Area.
Team Webyog
The post A day after Thanksgiving | Black Friday is here appeared first on Webyog Blog.

A look at Unicode with bash on Windows

When I wrote this blog about “bash on Windows” a few days ago I omitted one issue, that I already knew about. This is because it needs some elaboration that did not ‘fit in’ the previous blog. So I will do it here.
It is about Unicode. Unicode always was a pain in “cmd” and with the arrival of “bash” in Windows, this has become more significant and important. Actually on any recent *nix platform user will not do anything to make Unicode work ‘out of the box’ in the console and display all or almost all scripts (though I have noticed that the completeness of the ‘monospace’ font mostly used in the Linux console varies between Linux distros – with some distros you will not get all scripts shown here in the console).
But not so in “cmd” and thus also not in bash on Windows. I will illustrate this using the ‘mysql’ command-line client in “bash” with a result set that requires Unicode for display. You may download an SQL-dump of the simple MySQL table I use here.
See this MySQL result set in SQLyog:

In a Linux console it works fine – and without any special setting other than “SET NAMES UTF8;” in the ‘mysql’ client in case the MySQL/MariaDB server runs with another default character set. This is the XFCE terminal in OpenSuSE 42.1 (LEAP) with MariaDB’s ‘mysql’ flavor connecting to the same server. Only one imperfection is seen here: Arabic is not written from right to left as it should (you may check with Wikipedia or Google Translate if you are in doubt who is right here: SQLyog or the Linux console). But OK – we are talking about a console and not a word processor.

In “cmd” (whether using “bash” or not) it does not:

Now, this is expected, actually. You will need to specify “cp 65001” for “cmd” to make it use UTF8. You will also need to replace the default “Consolas” font with a Unicode font (from the settings of the “cmd” window). “Lucida console” is normally recommended. But it does not work with non-latin/cyrillic scripts. The font is incomplete and/or “cmd” does not understand how to use it with non-latin/cyrillic scripts:

Now, you may actually use a TrueType font in “cmd”. Monospace TrueType fonts installed on the system are available from the console settings. Let’s try with “Courier New” (what I used with SQLyog and where it worked perfectly). It makes little difference – only Arabic now comes to the console (and also here characters are also printed incorrectly from left to right).

There is no solution because no font will display non-latin/cyrillc scripts properly even when “cmd” uses “cp 65001” and understands (should at least) the characters as UTF8-encoded Unicode characters. Actually, it surprises me that accented latin strings from different ANSI codepages (Portuguese, Latvian, Czech and Turkish all belong to different ANSI codepages) and also Russian are printed correctly without specifying “cp 65001”. I think this may be a recent improvement in “cmd”. Now, “cmd” understands the characters correctly (in ‘UTF8-mode’). It just does not display them. This becomes clear if you copy from the console into any program/interface that handles UTF8 properly – a text editor, a browser form or whatever. See below in Notepad. And here I actually used the “Lucida console” font too, so the font is basically OK, it seems.

It very much looks like “cmd” was designed for ANSI and “Windows Unicode”/UTF16LE (where a single character max. is 2 bytes long) only and fails with UTF8 characters 3 (or more) bytes long, because of some internal truncation taking place. But even if so, this still does not explain that Arabic does not display with “Lucida console” and does with “Courier New”. So there is more to it with right-to-left writing systems.
I think it would be nice if Microsoft:
1) made UTF8 work in “cmd” with all (or almost all) scripts.
2) made “cmd” switch to  “Lucida console” font and also switch to “cp 65001” automatically when “bash” is invoked. You may easily forget to specify “cp 65001” (because you don’t need on other environments where bash runs) and you will have to ‘exit’ from bash and start what you were doing all over again.
The post A look at Unicode with bash on Windows appeared first on Webyog Blog.

TEL/電話+86 13764045638
QQ 47079569