Travellers Trail: February 2022

Thursday 17 February 2022

Count Actual Rows of Every Tables (MySQL)

On root database run, 1st change to your_db_name:


SELECT CONCAT('SELECT ''',table_name,''', COUNT(*) FROM ', table_name, ' union all') 
  FROM information_schema.tables WHERE table_schema = 'your_db_name';

Then connect to your database and remove the last 'union all' and add ';' then run sql e.g.


MariaDB > use db_dev_01; 

MariaDB [db_dev_01]> SELECT 'APPLICATIONS', COUNT(*) FROM APPLICATIONS union all                 
    -> SELECT 'ATTRIBUTE', COUNT(*) FROM ATTRIBUTE union all                                             
  
    ....
    
    ....
    
    ....

  -> SELECT 'ZBS_LOG', COUNT(*) FROM ZBS_LOG;

Thursday 10 February 2022

Mac Mini MySQL 5.6 start stop

MySQL 5.6 on Mac Start / Stop


# launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

# launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
  
# mysql -u root -p

Update config to tune MySQL cache


cd /etc
vi my.cnf

Mac MySQL tuning - basic


mysql> show variables like 'query_cache_%' ;
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
5 rows in set (0.07 sec)

Above is 1MB for both _limit and _size My 2022 Tuning setting


# cd /usr/local/mysql

# vi my.cnf

query_cache_limit = 2M
query_cache_size = 256M
query_cache_type = 1
innodb_buffer_pool_size=1G
  
  
  
mysql> show variables like 'query_ca%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 2097152   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 268435456 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.00 sec)

Start Stop MySQL (5.6.22) on Mac mini Catalina


# /usr/local/mysql/support-files/mysql.server stop 
Shutting down MySQL
. SUCCESS! 

# /usr/local/mysql/support-files/mysql.server start
Starting MySQL
.. SUCCESS! 

#

On my 2022 install, Mac Mini (2012) i5, 8GB