Showing entries 1 to 10 of 926
10 Older Entries »
Displaying posts with tag: Performance (reset)
MySQL Performance Tuning Tips for the Shopping Season

With Halloween all but a distant memory, the time has come to turn our attention to the upcoming holiday season. First, Thanksgiving, then Black Friday and Cyber Monday, culminating in the Christmas/boxing week shopping bonanza. For business owners, this time of the year marks the long-awaited year’s end profit taking. For some DBA’s, it brings fear, trepidation, and even sleepless nights toiling away to bring systems back online.

Thankfully, this need not be the case. With a little proactive tweaking of MySQL performance variables, you can insulate your database server(s) against the onslaught of increased demand that the shopping season brings.

Tip #1: Determine the Maximum Number of MySQL Connections

A good starting estimate for the maximum number connections on MySQL is one for every five requests to your web server. A few of those five requests to your web server will be for resources like CSS style sheets, …

[Read more]
Fun with Bugs #57 - On MySQL Bug Reports I am Subscribed to, Part I

I've decided to stop reviewing MySQL Release Notes in this series, but it does not mean that I am not interested in MySQL bugs any more. At the moment I am subscribed to 91 active MySQL bugs reported by other MySQL users, and in this blog post I am going to present 15 of them, the most recently reported ones. I'd really want to see them fixed or at least properly processed as soon as possible.

In some cases I am going to add my speculations on how the bug had better be handled, or maybe highlight some important details about it. It is not my job any more to process/"verify" any community bug reports for any kind of MySQL, but I did that for many years and I've spent more than 5 years "on the other side", being a member of Community, so in some cases I let myself to share some strong opinion on what may be done …

[Read more]
MySQL 5.7 InnoDB Temporary Tablespace – but why?

So, recently we had a runaway query eat up all sorts of temporary table space on our machines. Several machines had several terabytes in their ibtmp1 file after this happened. So I set out to find out more about why the InnoDB temporary tablespace is used, why it is better than using regular files, which was what was used prior to MySQL 5.7, and how to make sure that runaway queries do not end up filling up disk space.

Unfortunately, the manual does not go into why ibtmp1 is better than one file per temporary query, which disappears once the query ends. There are a few sections to look into:

Temporary Table Undo Logs – has one paragraph that states that these are the undo logs for temporary tablespaces. Given that these are undo logs, my guess is that this makes MySQL more crash-safe. But that is just a …

[Read more]
Efficient JSON Replication in MySQL 8.0

MySQL is not only a relational database, but can also be used as a schemaless/NOSQL document store, or a mix of both. This is realized by the JSON datatype, and is useful for any data that is hard to fit in the ”tabular” format of a traditional table.…

Lesson 07: Advanced MySQL Querying

Notes/errata/updates for Chapter 7:
See the official book errata at http://tahaghoghi.com/LearningMySQL/errata.php – Chapter 7 includes pages 223 – 275.

Supplemental blog post – ORDER BY NULL – read the blog post and the comments!

GROUP BY and HAVING examples – Supplemental blog post. The example of HAVING in the text shows a use case where HAVING is the same function as WHERE. This blog posts shows examples of HAVING that you cannot do any other way.

In the section called “The GROUP BY clause”, on pages 231-232, the book says:
“you can count any column in a group, and you’ll get the same answer, so COUNT(artist_name) is the same as …

[Read more]
How to Choose the MySQL innodb_log_file_size

In this blog post, I’ll provide some guidance on how to choose the MySQL innodb_log_file_size.

Like many database management systems, MySQL uses logs to achieve data durability (when using the default InnoDB storage engine). This ensures that when a transaction is committed, data is not lost in the event of crash or power loss.

MySQL’s InnoDB storage engine uses a fixed size (circular) Redo log space. The size is controlled by innodb_log_file_size and innodb_log_files_in_group (default 2). You multiply those values and get the Redo log space that available to use. While technically it shouldn’t matter whether you change either the innodb_log_file_size or innodb_log_files_in_group variable to control the Redo space size, most people just work with the innodb_log_file_size and leave innodb_log_files_in_group alone.

Configuring …

[Read more]
Probability perspective on MySQL Group replication and Galera Cluster

Comparing Oracle MySQL Group Replication and Galera Cluster through a probability perpective seems quite interesting.

At commit time both use a group certification process that requires network round trips. The required time for these network roundtrips is what will mainly determined the cost of a transaction. Let us try to compute an estimate of the [...]

MySQL Marinate – So you want to learn MySQL! – START HERE

Want to learn or refresh yourself on MySQL? MySQL Marinate is the FREE virtual self-study group is for you!

MySQL Marinate quick links if you know what it is all about.

This is for beginners – If you have no experience with MySQL, or if you are a developer that wants to learn how to administer MySQL, or an administrator that wants to learn how to query MySQL, this course is what you want. If you are not a beginner, you will likely still learn some nuances, and it will be easy and fast to do. If you have absolutely zero experience with MySQL, this is perfect for you. The first few chapters walk you through getting and installing MySQL, so all you need is a computer and the book.

The format of a virtual self-study group is as follows:
Each participant acquires the same textbook ( …

[Read more]
Cost/Benefit Analysis of a MySQL Index

We all know that if we add a MySQL index to speed up a read, we end up making writes slower. How often do we do the analysis to look at how much more work is done?

Recently, a developer came to me and wanted to add an index to a very large table (hundreds of gigabytes) to speed up a query. We did some testing on a moderately used server:

Set long_query_time to 0 and turn slow query logging on
Turn slow query logging off after 30 minutes.

Add the index (was on a single field)

Repeat the slow query logging for 30 minutes at a similar time frame (in our case, we did middle of the day usage on a Tuesday and Wednesday, when the database is heavily used).

Then I looked at the write analysis – there were no DELETEs, no UPDATEs that updated the indexed field, and no UPDATEs that used the indexed field in the filtering. There were only INSERTs, and with the help of pt-query-digest, here’s what I found: …

[Read more]
MySQL is not using my index – Join with a range condition

This post is based on a true story from one of our team members.

Here at EverSQL, we have high appreciation for the MySQL database.
Said that, every product has its pros and cons and even some bugs here and there.

So let’s dive into how we used some “bad practices” to overcome a potential bug in MySQL’s optimizer.
TL;DR: This case study will describe a scenario where the MySQL optimizer will not choose to use an existing and allegedly good index to optimize a search query, which eventually caused a response time of 80 seconds of a simple page in a web application.

The Problem

The scenario from a business perspective – the development team was trying to implement a web page that presents data about the user actions in the last year.
The scenario from a technical database perspective – The team was trying to join two (same issue will occur with more than two) tables and use a range …

[Read more]
Showing entries 1 to 10 of 926
10 Older Entries »