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.
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:
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:
...
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.
...
open-files-LIMIT = 65535
I also switch off the following engines for memory and stability issues:
...
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.
...
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).
...
# 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 :
innodb_additional_mem_pool_size = 64M