Connections Issue

We had an issue where, for some reason, the number of connections spun out of control and we had to kill them. When killing a process either the owner needs to do it, so the user that initiated it or another user with the "super" privilege.

The list of processes can be got from show processlist or you can execute a query like this:

select concat('KILL ',id,';') from information_schema.processlist
where user='geoff' and time > 200 into outfile '/tmp/a.txt';
source /tmp/a.txt;

Some other where clauses include the following: and host like 'cloudsqlproxy%'
and db = 'MY_DB' -- NULL means no databases was "used"
and time > 200 -- note this will not include the current session
and id <> connection_id() -- exclude the current session!
Putting all this together should help, however with MariaDB there is the option to use kill user username;. Hopefully you will never need any of this.

Command Line Issues

When running mysql you might see an error like this:
mysql: Can't read dir of '/usr/local/etc/my.cnf.d' (Errcode: 2 "No such file or directory")
Fatal error in defaults handling. Program aborted

The fix was just to create that directory but the other option is to remove the reference to it in "/usr/local/etc/my.cnf.d" or comment that out.

Table Sizes

Generally there are two things to consider with table sizes, storage space and row count. typically few people are interested in the disk space these days, so let's focus on counting row, for now. The classic query for getting the number of rows is:

FROM my_schema.my_table;

You can add where clauses but that's another story. So, the COUNT(*) approach is accurate, however for large tables this number can take a while to get, especially when you want to do this across a lot of tables. This is where the following query is much better and quicker:

SELECT table_schema, table_name, table_type, table_rows
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
  AND table_type <> 'VIEW';

This does however come with a warning, it can be inaccurate by about 45%, so not ideal but good for getting a rough order of magnitude.