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 |