August 30, 2014

Prevent MySQL downtime: Set max_user_connections

One of the common causes of downtime with MySQL is running out of connections. Have you ever seen this error? “ERROR 1040 (00000): Too many connections.” If you’re working with MySQL long enough you surely have. This is quite a nasty error as it might cause complete downtime… transient errors with successful transactions mixed with […]

MySQL server memory usage troubleshooting tips

There are many blog posts already written on topics related to “MySQL server memory usage,” but nevertheless there are some who still get confused when troubleshooting issues associated with memory usage for MySQL. As a Percona support engineer, I’m seeing many issues regularly related to heavy server loads – OR OOM killer got invoked and […]

Limited disk space? Compact backups with Percona Xtrabackup 2.1

One very interesting feature, “Compact Backup,” is introduced in Percona XtraBackup 2.1. You can run “compact backups” with the  –compact option, which is very useful for those who have limited disk space to keep the MySQL database backup. Now let’s first understand how it works. When we are using –compact option with Innobackupex, it will omit the […]

Can’t Create Thread: Errno 11 (A Tale of Two Ulimits)

Recently some of my fellow Perconians and I have noticed a bit of an uptick in customer cases featuring the following error message:

The canonical solution to this issue, if you do a bit of Googling, is to increase the number of processes / threads available to the MySQL user, typically by adding a […]

MySQL Wish for 2013 – Better Memory Accounting

With Performance Schema improvements in MySQL 5.6 I think we’re in the good shape with insight on what is causing performance bottlenecks as well as where CPU resources are spent. (Performance Schema does not accounts CPU usage directly but it is something which can be relatively easily derived from wait and stage information). Where we’re […]

Troubleshooting MySQL Memory Usage

One of the most painful troubleshooting tasks with MySQL is troubleshooting memory usage. The problem usually starts like this – you have configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet MySQL takes much more memory than you would expect, causing swapping or other […]

InnoDB Flushing: a lot of memory and slow disk

You may have seen in the last couple of weekly news posts that Baron mentioned we are working on a new adaptive flushing algorithm in InnoDB. In fact, we already have three such algorithms in Percona Server (reflex, estimate, keep_average). Why do we need one more? Okay, first let me start by showing the current […]

InnoDB memory allocation, ulimit, and OpenSUSE

I recently encountered an interesting case. A customer reported that mysqld crashed on start on OpenSUSE 11.2 kernel 2.6.31.12-0.2-desktop x86_64   with 96 GB RAM when the innodb_buffer_pool_size was set to anything more than 62 GB. I decided to try it with 76 GB. The error message was an assert due to a failed malloc() […]

How much memory can MySQL use in the worst case?

I vaguely recall a couple of blog posts recently asking something like “what’s the formula to compute mysqld’s worst-case maximum memory usage?” Various formulas are in wide use, but none of them is fully correct. Here’s why: you can’t write an equation for it.

TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE

We all know disk based temporary tables are bad and you should try to have implicit temporary tables created in memory where possible, do to it you should increase tmp_table_size to appropriate value and avoid using blob/text columns which force table creation on the disk because MEMORY storage engine does not support them Right ? […]