GNU Parallel for speeding up scripts

One of my favorite tools for speeding up work is GNU Parallel.

https://www.gnu.org/software/parallel/

When I write automation scripts, I try to write them to do "one simple task". For example, it will logon to a single database or a single server and so something.

In reality, I usually want the task to be repeated thousands of times and to be done in parallel to finish quickly.

Alternatively, I some times write the script to only print out the statement required to complete the task on the command line.

With GNU parallel I can use it to control my script and have it run in parallel and throttle the process. GNU parallel can limit the script to a certain number of instances of the script running at the same time.

In this example, my "createSomething.py" script only print out the commands to do the work. It will print out hundreds of commands which need to be run.

By piping it into parallel, it will automatically run 10 of those processes at a time and keep running them until they are all done.

 ./createSomething.py <input values> | parallel

In this example the file server_list.txt has multiple columns. Each column in the text file is separated by a tab. This is specified in --colsep '\t' as seen below. The values from each line will appear where is {}.

cat server_list.txt | parallel -j10 --colsep '\t' "my_shell_script.sh {}"

In this example the value which is read from server_list.txt is put into the input values after "my_python_script.py" where  {.} is:

cat server_list.txt | parallel -j10 python my_python_script.py {}

Here is another example running a query on a long list of servers:

contents of long_list_of_servers.txt would look like:
server1.com
server2.com
server3.com

Execute whatever code is in the "execute_this.sql" on every server in long_list_of_servers.txt. I use the -vvv option to log all the commands so they can be reviewed.

cat long_list_of_servers.txt  | parallel -j10 "mysql --defaults-file='my_defaults_file' -h '{}' -vvv < execute_this.sql >> outputfile.log"

If the code in "execute_this.sql" was a grant statement to create a new user or add more permissions on a list of Aurora instances, you could verify that it worked by running this next:

cat long_list_of_servers.txt | parallel -j10 "mysql --defaults-file='my_defaults_file.cnf' -h '{}' -e \"show global variables like 'aurora_server_id'; show grants for myUSER; '\" >> outputfile.log"

Now the log file will have the aurora_server_id for each servers and you can verify that each instance has the correct grants.

REPLACE function

The replace function is another one of my often used data analysis MySQL functions. However, it can create some ugly queries when it is nested many times. Here is an example I how I used it remove some text from server name. I have a client that creates replicas of their servers and names them <servername>_0, _1, _2. etc. I wrote a script to collect database sizes, table sizes, row counts and then each day run some reports on the changes. I only collect the data from the replicas and not the primary server but wanted to remove the replica names and only refer to the "cluster" name.

MySQL REPLACE() replaces all the occurrences of a substring within a string.
https://www.w3resource.com/mysql/string-functions/mysql-replace-function.php

Here is my example query that will give me the last 30 days of data:

SELECT
ss.server_name as server_name,
REPLACE(REPLACE(REPLACE(REPLACE(ss.server_name, '_2', ''),'_1',''),'_0',''),'_3','') as server_name,
ss.total_size_mb as total_size_mb,
ss.date_created,
ss.date as date
FROM growth_stats_lmp.schema_stats ss
WHERE ss.minutes_since_last_timestamp IS NOT NULL -- Removes the entries from a first run of growth_stats collection
AND ss.date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
GROUP BY server_name,schema_name,date;

The replace function will remove the _2, _1, _0, _3 out of the servername column (if it finds those values) and display only the remaining text that hasn't been "replaced out".

Setting up sys schema for Aurora

Here is a good article on how to setup the sys schema for AWS Aurora:

https://www.datadoghq.com/blog/how-to-collect-aurora-metrics/

These are the steps I followed for my Aurora instances:

git clone https://github.com/mysql/mysql-sys.git

cd mysql-sys

./generate_sql_file.sh -v 56 -b -u root

gsed -i '10486d' gen/sys_1.5.1_56_inline.sql

mysql -u root -p -h <Aurora Server> -P 3306  < gen/sys_1.5.1_56_inline.sql

<enter password at prompt>

After setting up sys schema, you will need a user that can view the reports.

If you plan to use MySQL workbench to view the reports, some won't be available unless the user has EXECUTE on the SYS schema and PROCESS globally in addition to SELECT access to databases.  

GRANT SELECT, SHOW VIEW, EXECUTE ON sys.*  TO 'UserName'@'%';

GRANT SELECT ON peformance_schema.*  TO 'UserName'@'%';

GRANT SELECT, SHOW DATABASES, PROCESS ON *.*  TO 'UserName'@'%';

If you don't give the user SELECT access to *.* because you want to limit permissions to not include the mysql schema then you would need to GRANT SELECT to the non system databases. Also make sure the user has a password hash statement after running the GRANT statements so the user doesn't have an empty password. 

Barracuda format causes Aurora read only instances to restart randomly

As of writing this, have found a problem with using Barracuda table format in Aurora.

Per the MySQL manual, to use Barracuda, you can set ROW_FORMAT=COMPRESSED or ROW_FORMAT=DYNAMIC.

From: https://dev.mysql.com/doc/refman/5.6/en/innodb-compression-usage.html

Aurora doesn’t actually support compressed tables and will automatically change the format if you try to use ROW_FORMAT=COMPRESSED. 

Per their documentation:

Amazon Aurora doesn't support compressed tables (that is, tables created with ROW_FORMAT=COMPRESSED).

Copied from: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/AuroraMySQL.Migrating.RDSMySQL.Import.html

So, if you want to use Barracuda, you are left with ROW_FORMAT=DYNAMIC. I have a client who wanted to use Barracuda and was using it on a physical machine at their data center. After doing the migration to Aurora, everything automatically changed to Antelope because the table create statements were using ROW_FORMAT=COMPRESSED. We went through and changed everything to ROW_FORMAT=DYNAMIC in order to force the tables to use Barracuda. This client has a sharded application spread out over about 30 instances. Each cluster has a writer end point instance and a replica for read only traffic. After we converted the tables to  Barracuda, the read only replicas would randomly restart. The error on the application side was "org.mariadb.jdbc.internal.util.dao.QueryException: unexpected end of stream". Then they would get a number of errors related to the instance not being available. Then it would work fine for a while. 

It was hard to figure it out but eventually we traced it back to the read only replicas using Barracuda. For a while we transferred all the read only traffic to the writer end points and all the problems stopped. 

If I log onto the writer end point, you can see that I've set the innodb_file_format  and innodb_file_format_max to Barracuda (same settings for read only replica also). 

mysql> show global variables like 'innodb_file_%';

+--------------------------+-----------+

| Variable_name            | Value     |

+--------------------------+-----------+

| innodb_file_format       | Barracuda |

| innodb_file_format_check | ON        |

| innodb_file_format_max   | Barracuda |

| innodb_file_per_table    | ON        |

+--------------------------+-----------+

4 rows in set (0.09 sec)

This query shows I'm on the writer end point:

mysql> show global variables like 'innodb_read_only';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| innodb_read_only | OFF   |

+------------------+-------+

1 row in set (0.12 sec)

mysql> show global variables like 'aurora_version';

+----------------+--------+

| Variable_name  | Value  |

+----------------+--------+

| aurora_version | 1.15.1 |

+----------------+--------+

1 row in set (0.12 sec)

mysql>

mysql> use tmp;

Database changed

mysql>

mysql> DROP TABLE IF EXISTS test_table;

Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE `test_table` (

    ->   `id` int(11) NOT NULL AUTO_INCREMENT,

    ->   `message` varchar(255) NOT NULL,

    ->   `created_at` datetime NOT NULL,

    ->   PRIMARY KEY (`id`)

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED;

Query OK, 0 rows affected, 2 warnings (0.13 sec)

mysql> SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE NAME = 'tmp/test_table';

+----------+----------------+------+--------+-------+-------------+------------+---------------+

| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |

+----------+----------------+------+--------+-------+-------------+------------+---------------+

|      196 | tmp/test_table |    1 |      6 |   138 | Antelope    | Compact    |             0 |

+----------+----------------+------+--------+-------+-------------+------------+---------------+

1 row in set (0.09 sec)

Table became Antelope (Aurora silently changes the format for you).

mysql> DROP TABLE IF EXISTS test_table;

Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE `test_table` (

    ->   `id` int(11) NOT NULL AUTO_INCREMENT,

    ->   `message` varchar(255) NOT NULL,

    ->   `created_at` datetime NOT NULL,

    ->   PRIMARY KEY (`id`)

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC ;

Query OK, 0 rows affected (0.11 sec)

mysql> SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE NAME = 'tmp/test_table';

+----------+----------------+------+--------+-------+-------------+------------+---------------+

| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |

+----------+----------------+------+--------+-------+-------------+------------+---------------+

|      197 | tmp/test_table |   33 |      6 |   139 | Barracuda   | Dynamic    |             0 |

+----------+----------------+------+--------+-------+-------------+------------+---------------+

1 row in set (0.10 sec)

Now the table is Barracuda. 

The issue we seem to have is something to with Aurora starting up with the setting in the parameter group for innodb_file_format_max as Barracuda. The system tables are using Antelope but when it reads a table using Barracuda, it tries to set the innodb_file_format_max to Barracuda but since it is read only it crashes. 

After the read only replica crashes, it seems to fix itself for a while. And then eventually crashes again. I'm not sure how it gets back into a bad state which allows it to crash again. Reverting all the tables back to Antelope solved the issue.