We maintain a lot of servers under Kinja, so we have to use some orchestrator software to perform some tasks on a lot of servers. The Ansible software is used by us because it is cool.
We have also a lot of MySQL servers (and counting!) under Kinja, so we have some tasks to perform on them, such as managing replication. Of course, there are some ways to do this, for example using multiplexed terminals, or run ansible shell commands what performs mysql queries (e.g. ansible mysql-master1 -m shell -a “mysql -e “SOME SQL QUERY HERE”) but it is not too comfy, and needs a lot of manual work.
So, there is a way to make it easier for us, and that’s why I made a mysql_replication module for Ansible. (And I made a pull request for that on GitHub, so I hope it will be merged soon to ‘official’ branch)
The mysql_replication module helps you to
- Query slave status
- Query master status
- Change replication slave’s parameters (such as master server IP, master_log_pos, etc.)
- Stop slave thread
- Start slave thread
The module can be found in my GitHub repo here.
First, I have to tell you, that the “fragmentation” is not the best word what I should use, but that was the closest to that what I wanted to say. The base of the fragmentation checker script is Peter Zaitsev’s 2008 article about Information_schema, I used that query to get that results what I needed.
Some background: a few weeks ago we got some free space related problems (well, they weren’t a real problem, but they could lead onto one, and we had to act fast.) The InnoDB is working on a bit strange way, it is not like to free up space what is deleted before, and because of this, the datafiles will grow to the end of the world. The first thing what we could do to reclaim free space is to use
configuration option, what will split up the database to a lot of .ibd datafiles, instead of one big ibdata file. If you got your data in different tables, you can run “OPTIMIZE TABLE” from mysql, what will compact the datafiles (creates a new one, copies the table contents onto this new table, deletes the old file, and renames the new) for you. It takes a lot of time to run, and you have to know, that the “optimize” command will also be replicated, so after you did this on master be aware that it will happen on slaves too. So, when you take some maintenance time, you have to calculate with this. It’s easy to determine what tables would be optimized, you can check the table size from the data dictionary, and you can see how big is that table on disk. If they differ, then you can reclaim the space what is the difference between the two sizes.
Continue reading “Free up space on mysql servers”