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%'
Putting all this together should help, however with MariaDB there is the option to use
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!kill user username;. Hopefully you will never need any of this.
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.
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:
SELECT COUNT(*) 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.