Twitter this

Powered by MariaDB Powered by nginx ...

Tuning MariaDB 5.2 with Partitioned tables

If you want to set up a decent MariaDB machine with enough tables to seriously keep you awake at night thinking about backup/restore plans, you need to perform some additional setup to the stock MariaDB configuration file. I just started to load up a backup of a large database and once it had to create partitioned tables I was met with a mysqld crash.

1
2
3
4
5
6
7
8
9
ERROR 1005 (HY000) at line 19 IN file: 'Mobility_dakendeur_schema.sql': Can't create table 'Mobility.dakendeur' (errno: 1)
Query OK, 0 rows affected (0.00 sec)
MariaDB [Mobility]> show errors;
+-------+------+------------------------------------------------------------+
| Level | Code | Message                                                    |
+-------+------+------------------------------------------------------------+
| Error | 1005 | Can'
t CREATE TABLE 'Mobility.dakendeur' (errno: 1)         |
| Error |    6 | Error ON DELETE of './Mobility/dakendeur.par' (Errcode: 2) |
+-------+------+------------------------------------------------------------+

In order to get MariaDB 5.2 to work with restoring partitioned files you will need a whole lot of open files available. When you see this in the logs:

1
2
3
4
5
6
Nov  5 11:16:57 localhost mysqld: 111105 11:16:57 [ERROR] mysqld got signal 6 ;
Nov  5 11:16:57 localhost mysqld: InnoDB: Error number 24 means 'Too many open files'.
Nov  5 10:55:20 localhost mysqld: 111105 10:55:20  InnoDB: Error creating file './Mobility/dakendeur#P#p1570.ibd'.
Nov  5 10:55:20 localhost mysqld: 111105 10:55:20  InnoDB: Operating system error number 24 in a file operation.
Nov  5 10:54:03 localhost mysqld: 111105 10:54:03 [Warning] Aborted connection 70 to db: 'Mobility'
user: 'root' host: 'localhost' (Got an error reading communication packets)

If you have a quite some partitioned tables (with a few hundred per table ), you are going to need a lot of open files. Mysqld crashes with a signal 6 if it hits the limit. To bring order to chaos, you need to set some limits. I've had success with these:

1
2
3
4
[mysqld]
...
innodb_file_per_table   = 1
innodb_open_files       = 16000

In fact, if you have that first option set, you're gonna need a whole lot of files. You'll waste diskspace too but in my experience you gain quite some performance and avoid locks. These work fine for me (with a 8GB VPS).

Also, set the general file limit high enough, for me at the moment, this is about 4 times the amount of open files with a loaded DB.

1
2
3
[mysqld]
...
open-files-LIMIT = 65535

I also switch off the following engines for memory and stability issues:

1
2
3
4
5
[mysqld]
...
skip-pbxt
skip-archive
skip-pbxt_statistics

pbxt saves me a good chunk of memory, I found that using partitioned archive tables is really unstable so I switch them off for now (I do like archive being read-only but I cannot use those for now).

For finetuning my installation I do some tweaks, I'm on a VPS with write-back cache on the disks.

1
2
3
4
[mysqld]
...
innodb_flush_log_at_trx_commit = 2
innodb_flush_method     = O_DIRECT

and than specifically for XtraDB (One more reason to take MariaDB over stock Mysql).

1
2
3
4
5
6
7
8
[mysqld]
...
# XTRADB
innodb_fast_recovery = 1
innodb_read_ahead=none
innodb_ibuf_active_contract = 1
innodb_adaptive_flushing = 0
innodb_adaptive_checkpoint = estimate

This about does it for me, I'm still playing with the memory sizes and the innodb buffer cache.

im trying :

1
2
innodb_buffer_pool_size = 6G
innodb_additional_mem_pool_size = 64M

Comments are closed.

recruitment
recruitment
recruitment
recruitment