Page MenuHomeDevCentral

Provision MySQL
ClosedPublic

Authored by dereckson on Oct 28 2017, 10:35.

Details

Summary

MariaDB has been selected as MySQL server flavour,
to get Cassandra, Aria and XtraDB.

FreeBSD servers with ZFS can benefit from a tweaked configuration
to avoid a duplicate InnoDB/ZFS cache, and to adjust block size.

For FreeBSD, we also require MariaDB 10.2 instead of 10.1.

Reference: https://cdn.oreillystatic.com/en/assets/1/event/21/Optimizing%20MySQL%20Performance%20with%20ZFS%20Presentation.pdf

Fixes T1306.

Test Plan

Deploy on Ysul and check ZFS partitions are populated

Diff Detail

Repository
rOPS Nasqueron Operations
Lint
Automatic diff as part of commit; lint not applicable.
Unit
Automatic diff as part of commit; unit tests not applicable.

Event Timeline

dereckson created this revision.Oct 28 2017, 10:35
dereckson planned changes to this revision.Oct 28 2017, 10:35
dereckson updated this revision to Diff 2890.Oct 29 2017, 20:45

Rebased

dereckson updated this revision to Diff 2893.Oct 29 2017, 22:07

Rebased against imagemagick map.jinja change

dereckson updated this revision to Diff 2999.Nov 4 2017, 22:52

Don't use tabs for alignment. Create /var/run/mysqld.

dereckson retitled this revision from WIP: Provision MySQL to Provision MySQL.Nov 4 2017, 22:52
dereckson edited the summary of this revision. (Show Details)
dereckson updated this revision to Diff 3000.Nov 4 2017, 22:53

Fix commit range to select only MySQL

I reported upstream an issue for recordsize, falsely reporting to be updated in test mode: https://github.com/saltstack/salt/issues/44404

I'm moving zfs states in their independent file, as it's a little scary to run that right now.

dereckson updated this revision to Diff 3001.Nov 5 2017, 01:46

In ZFS we trust.

dereckson updated this revision to Diff 3002.Nov 5 2017, 01:47

Tweak commit range

$ salt-call --local state.apply roles/dbserver-mysql
local:                                 
----------                             
          ID: mysql_server_software    
    Function: pkg.installed            
      Result: True                     
     Comment: All specified packages are already installed                     
     Started: 01:45:51.880169          
    Duration: 246.099 ms               
     Changes:                          
----------                             
          ID: arcology/mysql-innodb-data                                       
    Function: zfs.filesystem_present                                           
      Result: True                     
     Comment: filesystem arcology/mysql-innodb-data was updated                
     Started: 01:45:52.131408          
    Duration: 563.66 ms                
     Changes:                          
              ----------               
              arcology/mysql-innodb-data:                                      
                  ----------           
                  mountpoint:          
                      /var/db/mysql/mysql-innodb-data                          
                  recordsize:                                                  
                      16k              
----------                             
          ID: arcology/mysql-innodb-logs                                       
    Function: zfs.filesystem_present   
      Result: True                     
     Comment: filesystem arcology/mysql-innodb-logs was updated                
     Started: 01:45:52.695790          
    Duration: 557.842 ms               
     Changes:                          
              ----------               
              arcology/mysql-innodb-logs:                                      
                  ----------           
                  mountpoint:          
                      /var/db/mysql/mysql-innodb-logs                          
                  recordsize:          
                      128k             
----------                                                                                                                                                     
          ID: arcology/mysql-root                                              
    Function: zfs.filesystem_present                                           
      Result: True                                                             
     Comment: filesystem arcology/mysql-root was updated                       
     Started: 01:45:53.254366                                                  
    Duration: 574.172 ms                                                       
     Changes:                                                                  
              ----------                                                       
              arcology/mysql-root:                                                                                                                             
                  ----------                                                                                                                                   
                  mountpoint:                                                                                                                                  
                      /var/db/mysql/mysql-root                                                                                                                 
                  recordsize:                                                                                                                                  
                      8k
----------
          ID: /usr/local/etc/my.cnf
    Function: file.managed
      Result: True
     Comment: File /usr/local/etc/my.cnf updated
     Started: 01:45:53.838402
    Duration: 95.623 ms
    Duration: 95.623 ms                
     Changes:                          
              ----------               
              diff:                    
                  New file             
              mode:                    
                  0644                 
----------                             
          ID: /var/run/mysqld          
    Function: file.directory           
      Result: True                     
     Comment: Directory /var/run/mysqld updated                                
     Started: 01:45:53.934487          
    Duration: 7.571 ms                 
     Changes:                          
              ----------               
              /var/run/mysqld:         
                  New Dir              
----------                             
          ID: /var/db/mysql            
    Function: file.directory           
      Result: True                     
     Comment: Directory /var/db/mysql updated                                  
     Started: 01:45:53.942532          
    Duration: 5.229 ms                 
     Changes:                          
              ----------               
              group:                   
                  mysql                
              user:                    
                  mysql                

Summary for local                      
------------                           
Succeeded: 7 (changed=6)               
Failed:    0                           
------------                           
Total states run:     7                
Total run time:   2.050 s      
$ zpool history
2017-11-05.01:45:52 zfs set mountpoint=/var/db/mysql/mysql-innodb-data arcology/mysql-innodb-data
2017-11-05.01:45:52 zfs set recordsize=16k arcology/mysql-innodb-data
2017-11-05.01:45:53 zfs set mountpoint=/var/db/mysql/mysql-innodb-logs arcology/mysql-innodb-logs
2017-11-05.01:45:53 zfs set recordsize=128k arcology/mysql-innodb-logs
2017-11-05.01:45:53 zfs set mountpoint=/var/db/mysql/mysql-root arcology/mysql-root
2017-11-05.01:45:58 zfs set recordsize=8k arcology/mysql-root

The extraneous set recordsize issue has been commented upstream in the bug describe above.

dereckson planned changes to this revision.Nov 5 2017, 02:00
dereckson added inline comments.
roles/dbserver-mysql/mysql-server/files/my.cnf
41

Linux specific.

? But we use FreeBSD for this role mainly.

74

Provide this.

dereckson updated this revision to Diff 3003.Nov 5 2017, 13:56
dereckson marked 2 inline comments as done.

Proofread and improve configuration file: stop words, performance schema

dereckson planned changes to this revision.Nov 5 2017, 13:59
$ /usr/local/etc/rc.d/mysql-server start
Installing MariaDB/MySQL system tables in '/var/db/mysql' ...
2017-11-05 13:57:44 34429034496 [Warning] No argument was provided to --log-bin and neither --log-basename or --log-bin-index where used;  This may cause repli
ction to break when this server acts as a master and has its hostname changed! Please use '--log-basename=ysul.nasqueron.org' or '--log-bin=mysqld-bin' to avoi
d this problem.
2017-11-05 13:57:45 34429034496 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2017-11-05 13:57:45 34429034496 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2017-11-05 13:57:45 34429034496 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2017-11-05 13:57:45 34429034496 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2017-11-05 13:57:45 34429034496 [ERROR] InnoDB: Cannot open datafile '/var/db/mysql/mysql-innodb-data/ibdata1'
2017-11-05 13:57:45 34429034496 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, an
d it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attemp
t. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious d
ata!
2017-11-05 13:57:45 34429034496 [ERROR] InnoDB: Database creation was aborted with error Cannot open a file. You may need to delete the ibdata1 file before try
ing to start up again.
2017-11-05 13:57:45 34429034496 [ERROR] Plugin 'InnoDB' init function returned error.
2017-11-05 13:57:45 34429034496 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-11-05 13:57:45 34429034496 [ERROR] /usr/local/libexec/mysqld: unknown variable 'bin_log_format=MIXED'
2017-11-05 13:57:45 34429034496 [ERROR] Aborting

By the way, Aria totally ignores MyISAM datadir.

dereckson updated this revision to Diff 3004.Nov 5 2017, 14:09

Allow InnoDB engine to write in their directories

dereckson updated this revision to Diff 3012.EditedNov 5 2017, 18:07

Use K for recordsize unit (see upstream task), simplify node.get per D1176

dereckson updated this revision to Diff 3013.Nov 5 2017, 19:19

Create empty directories after the parent volume exists

dereckson updated this revision to Diff 3014.Nov 5 2017, 19:33

Define log_basename

dereckson updated this revision to Diff 3015.Nov 5 2017, 20:55

+rc. Fix idsa/id typo

dereckson accepted this revision.Nov 5 2017, 21:36
This revision is now accepted and ready to land.Nov 5 2017, 21:36
This revision was automatically updated to reflect the committed changes.