Showing entries 1 to 10 of 74
10 Older Entries »
Displaying posts with tag: mysql performance (reset)
Preview: Top MySQL 8 Features

Although there is no official software release for MySQL 8.0 as of yet, most insiders believe that it’s likely to arrive sometime in 2018.  In the meantime, Oracle has officially announced a tantalizing list of over two hundred new features!   We recently covered Replication Performance Enhancements.  Today’s blog will cover some of the other exciting enhancements we can expect when the production release of MySQL 8 hits the market.

New Database Roles

A role is a named collection of privileges that define what a user can and cannot do within a database. Roles play a vital part of database security by limiting who can connect to the server, access the database, or even access individual database objects and data.

Although prior to version 8, MySQL did provide a set of Privileges and Administrative Roles, the up-coming release will also support a set of flexible and properly …

[Read more]
Deleting huge number of records in MySQL

This is a short post about DELETE data from huge table in MySQL. Most of us experienced, deleting huge record from MySQL tables taking long time, sometimes hours to complete deleting millions of records. Also, on production servers it locks the other table operations as well. Recently, I deleted around 70 million record from a production database in less than an hour. There are multiple workarounds to do this, however I am writing about the two methods which are frequently used by me for this operation. 

  • Using intermediate table.
  • Delete data in small chunks.

Before we proceed with using any of these methods, make sure the table has required indexes on where clause and you have a copy of the table as backup.
Using intermediate table: In this method, create a new table with similar data structure and copy only required data. Rename the original table as archive or backup table and Rename the …

[Read more]
Top Performance Metrics to Monitor on MySQL

As tables increase in size and more and more users come online, it becomes increasingly necessary to fine-tune your database server from time to time. The secret to knowing what adjustments need to be made is to perform regular monitoring. Most databases offer dozens – if not hundreds – of performance metrics that you can assess.

In the earlier blog, we showed a number of ways to determine the cause(s) of MySQL database performance issues using a couple of built-in tools. With this two blog series, we will focus on monitoring key performance metrics. In part one, we’ll:

  • examine the benefits of performance monitoring,
  • outline the main performance metric categories
  • list the monitoring tools provided by MySQL, i.e.:
    • server variables
    • the Performance Schema
[Read more]
Troubleshooting MySQL Performance Issues

Despite your very best efforts, as tables increase in size and more and more users come online, sluggish MySQL performance eventually rears its ugly head. When that happens, you may be tempted to shrug it off as part of doing business. Don’t. By understanding the mechanisms behind database slowdowns and by methodically attacking the problem, you can home in on the culprit(s) and remedy the issues in a timely manner, before your business suffers substantial losses as a result.

The purpose of this blog is to assist you in determining the cause(s) of MySQL database performance issues using a couple of built-in tools.

Why Database Performance Slows Down

It’s no secret that database performance tends to degrade over time. While it’s easy to point the finger at the number of concurrent users, table scans, and growing tables, the reality is more complex than that. The most common reason for slow database performance is …

[Read more]
How to store IP (internet protocol) address in MySQL?

Internet Protocol address is a special kind of data. This data does not correspond directly to available MySQL built-in data types. I have seen in many servers, the IP(Internet Protocol) address is stored in CHAR(12), VARCHAR(15), TEXT and so on. 

The dotted-quad notation is just a way of writing for better readability, it is not the original form of raw data. The IP address is a UNSIGNED INTEGERS they are not strings. 

So the question is how we can store the IP addresses with dot in integer columns? The MySQL provides the built-it function to do this operation. The functions are given below:







For IPv4 addresses:

INET_ATON()
mysql> select inet_aton('127.0.0.1');

+------------------------+
| inet_aton('127.0.0.1') |
+------------------------+
| 2130706433 …
[Read more]
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 …

[Read more]
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 …

[Read more]
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.

Troubleshooting hardware resource usage webinar: Q & A

In this blog, I provide answers to the Q & A for the Troubleshooting hardware resource usage webinar.

First, I want to thank everybody who attended the May 26 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: How did you find the memory IO LEAK?

A: Do you mean the replication bug I was talking about in the webinar? I wrote about this bug …

[Read more]
MySQL 5.7 By Default 1/3rd Slower Than 5.6 When Using Binary Logs

Researching a performance issue, we came to a startling discovery:

MySQL 5.7 + binlogs is by default 37-45% slower than MySQL 5.6 + binlogs when otherwise using the default MySQL settings

Test server MySQL versions used:
i7, 8 threads, SSD, Centos 7.2.1511
mysql-5.6.30-linux-glibc2.5-x86_64
mysql-5.7.12-linux-glibc2.5-x86_64

mysqld –options:

--no-defaults --log-bin=mysql-bin --server-id=2

Run details:
Sysbench version 0.5, 4 threads, socket file connection

Sysbench Prepare: 

sysbench --test=/usr/share/doc/sysbench/tests/db/parallel_prepare.lua --oltp-auto-inc=off --mysql-engine-trx=yes --mysql-table-engine=innodb --oltp_table_size=1000000 --oltp_tables_count=1 --mysql-db=test --mysql-user=root --db-driver=mysql --mysql-socket=/path_to_socket_file/your_socket_file.sock prepare …
[Read more]
Showing entries 1 to 10 of 74
10 Older Entries »