MySQL OPTIMIZE TABLE command

If your application is performing lot of deletes and updates on MySQL database, then there is a high possibility that your MySQL data files are fragmented.

This will result in lot of unused space, and also might affect performance.

So, it is highly recommended that you defrag your MySQL tables on an ongoing basis.

This tutorial explains how to optimize MySQL to defrag tables and reclaim unused space.

1. Identify Tables for Optimization

The first step is to identify whether you have fragmentation on your MySQL database.

Connect to your MySQL database, and execute the following query, which will display how much unused space are available in every table.

mysql> use thegeekstuff;

mysql> select table_name,
round(data_length/1024/1024) as data_length_mb, 
round(data_free/1024/1024) as data_free_mb 
 from information_schema.tables 
 where round(data_free/1024/1024) > 500 
 order by data_free_mb;

+------------+----------------+--------------+
| table_name | data_length_mb | data_free_mb |
+------------+----------------+--------------+
| BENEFITS   |           7743 |         4775 |
| DEPARTMENT |          14295 |        13315 |
| EMPLOYEE   |          21633 |        19834 |
+------------+----------------+--------------+

In the above output:

• This will display list of all tables that has minimum of 500MB of unused space. As we see above, in this example, there are 3 tables that has more than 500MB of unused space.
• data_length_mb column displays the total table size in MB. For example, EMPLOYEE table size is around 21GB.
• data_free_mb column displays the total unused space in that particular table. For example, EMPLOYEE table has around 19GB of unused space in it.
• All these three tables (EMPLOYEE, DEPARTMENT AND BENEFITS) are heavily fragmented and it needs to be optimized to reclaim the unused space.

From the filesystem level, you can see the size of the individual table files as shown below.

The file size will be the same as what you see under “data_length_mb” column in the above output.

# ls -lh /var/lib/mysql/thegeekstuff/
..
-rw-rw----. 1 mysql mysql  7.6G Apr 23 10:55 BENEFITS.MYD
-rw-rw----. 1 mysql mysql   14G Apr 23 12:53 DEPARTMENT.MYD
-rw-rw----. 1 mysql mysql   22G Apr 23 12:03 EMPLOYEE.MYD
..

In this example, the EMPLOYEE.MYD file is taking up around 22GB at the filesystem level, but it has lot of unused space in it. If we optimize this table, the size of this file should go down dramatically.

Lire la suite…

Unless you are a MySQL performance tuning expert, it can be enormously challenging and somewhat overwhelming to locate and eliminate MySQL bottlenecks. While many DBAs focus on improving the performance of the queries themselves, this post will focus on the highest-impact non-query items: MySQL Server Performance and OS Performance for MySQL.

MySQL Performance Tuning

This post is a « best-of » compilation of the tricks and scripts I have found to be the most effective over the past decade. I’d like to write a 50 page article but am limiting this to 1 page.

For anyone serious about High Performance MySQL, I would highly highly recommend the fantastic book: « High Performance MySQL: Optimization, Backups, Replication, and more » – O’Reilly. I have spent many hours poring over it’s wisdom-filled pages and gaining much practical know-how.

MySQL Server Software

Each new MySQL server release contains ENORMOUS performance enhancements over previous versions. That is the absolute very first thing you should do: Upgrade your MySQL Server and Client libraries and keep them updated.

There are several « flavors » of MySQL believe it or not.. Most people use the stock MySQL Server. I, along with WikiPedia, Arch-Linux, and others, use MariaDB. MariaDB is a greatly enhanced 100% compatible replacement for the stock MySQL Server. It is based on the excellent work by the Percona project. The percona flavor of MySQL is the other truly improved version of MySQL to consider. I personally have spent a couple years using Percona, then I upgraded from Percona to MariaDB (which has a lot of Percona juju built in) and am no longer thinking about which version to go with. MariaDB is the bomb-diggity.

MySQL Engine

InnoDB not MyISAM. InnoDB may be surpassed by in-development engines like TokuDB. I ONLY use InnoDB, for everything.

Types of MySQL Servers to optimize

Seriously? Optimize EVERYTHING! The screenshots below are actual from one of my live servers. That server used to be 8GB RAM, but now as you may see in the screenshots, it is now only 2GB of RAM. I was able to save some serious $$$ by optimizing my server, without sacrificing speed… In fact I gained some speed in many instances.

I’ve used these optimization techniques on monster servers with 32GB of ram and many slaves, and also on a machine with 1GB of ram (running arch-linux).

Lire la suite…

MySQL performance tuning

ramCount=`awk 'match($0,/vmguar/) {print $4}' /proc/user_beancounters`
ramBase=-16 && for ((;ramCount>1;ramBase++)); do ramCount=$((ramCount/2)); done

cat <<EOF > /etc/my.cnf
[mysqld]
# Basic settings
user = mysql
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

# Security settings
local-infile = 0
symbolic-links = 0

# Memory and cache settings
query_cache_type = 1
query_cache_size = $((2**($ramBase+2)))M
thread_cache_size = $((2**($ramBase+2)))
table_cache = $((2**($ramBase+7)))
tmp_table_size = $((2**($ramBase+3)))M
max_heap_table_size = $((2**($ramBase+3)))M
join_buffer_size = ${ramBase}M
key_buffer_size = $((2**($ramBase+4)))M
max_connections = $((100 + (($ramBase-1) * 50)))
wait_timeout = 300

# Innodb settings
innodb_buffer_pool_size = $((2**($ramBase+3)))M
innodb_additional_mem_pool_size = ${ramBase}M
innodb_log_buffer_size = ${ramBase}M
innodb_thread_concurrency = $((2**$ramBase))

[mysqld_safe]
# Basic safe settings
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
EOF

Last week during a casual conversation I overheard a colleague saying: « The Linux network stack is slow! You can’t expect it to do more than 50 thousand packets per second per core! »

That got me thinking. While I agree that 50kpps per core is probably the limit for any practical application, what is the Linux networking stack capable of? Let’s rephrase that to make it more fun:

On Linux, how hard is it to write a program that receives 1 million UDP packets per second?

Hopefully, answering this question will be a good lesson about the design of a modern networking stack.

First, let us assume:

• Measuring packets per second (pps) is much more interesting than measuring bytes per second (Bps). You can achieve high Bps by better pipelining and sending longer packets. Improving pps is much harder.
• Since we’re interested in pps, our experiments will use short UDP messages. To be precise: 32 bytes of UDP payload. That means 74 bytes on the Ethernet layer.
• For the experiments we will use two physical servers: « receiver » and « sender ».
• They both have two six core 2GHz Xeon processors. With hyperthreading (HT) enabled that counts to 24 processors on each box. The boxes have a multi-queue 10G network card by Solarflare, with 11 receive queues configured. More on that later.
• The source code of the test programs is available here: udpsender, udpreceiver.

Prerequisites

Let’s use port 4321 for our UDP packets. Before we start we must ensure the traffic won’t be interfered with by the iptables:

receiver$ iptables -I INPUT 1 -p udp --dport 4321 -j ACCEPT  
receiver$ iptables -t raw -I PREROUTING 1 -p udp --dport 4321 -j NOTRACK  

A couple of explicitly defined IP addresses will later become handy:

receiver$ for i in `seq 1 20`; do   
              ip addr add 192.168.254.$i/24 dev eth2; 
          done
sender$ ip addr add 192.168.254.30/24 dev eth3  

1. The naive approach

To start let’s do the simplest experiment. How many packets will be delivered for a naive send and receive?

The sender pseudo code:

fd = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)  
fd.bind(("0.0.0.0", 65400)) # select source port to reduce nondeterminism  
fd.connect(("192.168.254.1", 4321))  
while True:  
    fd.sendmmsg(["x00" * 32] * 1024)

While we could have used the usual send syscall, it wouldn’t be efficient. Context switches to the kernel have a cost and it is be better to avoid it. Fortunately a handy syscall was recently added to Linux: sendmmsg. It allows us to send many packets in one go. Let’s do 1,024 packets at once.

The receiver pseudo code:

fd = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)  
fd.bind(("0.0.0.0", 4321))  
while True:  
    packets = [None] * 1024
    fd.recvmmsg(packets, MSG_WAITFORONE)

Similarly, recvmmsg is a more efficient version of the common recv syscall.

Let’s try it out:

sender$ ./udpsender 192.168.254.1:4321  
receiver$ ./udpreceiver1 0.0.0.0:4321  
  0.352M pps  10.730MiB /  90.010Mb
  0.284M pps   8.655MiB /  72.603Mb
  0.262M pps   7.991MiB /  67.033Mb
  0.199M pps   6.081MiB /  51.013Mb
  0.195M pps   5.956MiB /  49.966Mb
  0.199M pps   6.060MiB /  50.836Mb
  0.200M pps   6.097MiB /  51.147Mb
  0.197M pps   6.021MiB /  50.509Mb

With the naive approach we can do between 197k and 350k pps. Not too bad. Unfortunately there is quite a bit of variability. It is caused by the kernel shuffling our programs between cores. Pinning the processes to CPUs will help:

sender$ taskset -c 1 ./udpsender 192.168.254.1:4321  
receiver$ taskset -c 1 ./udpreceiver1 0.0.0.0:4321  
  0.362M pps  11.058MiB /  92.760Mb
  0.374M pps  11.411MiB /  95.723Mb
  0.369M pps  11.252MiB /  94.389Mb
  0.370M pps  11.289MiB /  94.696Mb
  0.365M pps  11.152MiB /  93.552Mb
  0.360M pps  10.971MiB /  92.033Mb

Now, the kernel scheduler keeps the processes on the defined CPUs. This improves processor cache locality and makes the numbers more consistent, just what we wanted.

Lire la suite…

A pretty common topic in Support tickets is the rather infamous error: ERROR 1040: Too many connections. The issue is pretty self-explanatory: your application/users are trying to create more connections than the server allows, or in other words, the current number of connections exceeds the value of the max_connections variable.

This situation on its own is already a problem for your end-users, but when on top of that you are not able to access the server to diagnose and correct the root cause, then you have a really big problem; most times you will need to terminate the instance and restart it to recover.

Root user can’t connect either! Why!?

In a properly set up environment, a user with SUPER privilege will be able to access the instance and diagnose the error 1040 problem that is causing connection starvation, as explained in the manual:

mysqld actually permits max_connections + 1 client connections. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the privilege to administrators and not to normal users (who should not need it), an administrator who also has the PROCESS privilege can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected.

But we see lots of people who give SUPER privileges to their application or script users, either due to application requirements (dan Lire la suite…