How to check for low mem prunes:
mysql> show status like ‘%cache%’;
Shows all running queries and processes from command line:
mysql> show full processlist;
How to give user all privileges, including RELOAD which cannot be turned on in cPanel.
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username’@’localhost’;
How to set the global query cache size in a live environment
set GLOBAL query_cache_size=xxx;
or in /etc/my.cnf
set-variable = query_cache_size=32M
The mysqladmin tool’s version ouput tells you some much needed and valuable information about your mySQL databases and usage. Check it out from the shell command line:
BRIEF LOGGING HOWTO:
Logging is very resource intensive on a highly utilized mySQL server. However, if you must enable it for diagnostic purposes, here’s how:
In your /etc/my.cnf add:
log = /var/log/mysql.log
You will need to actually ‘touch’ this file and set the ownership of it to be writable by the mySQL server. Typically, this would be done like so:
# touch /var/log/mysql.log ; chown mysql:mysql /var/log/mysql.log
Less intensive, and widely used for optimizing slow queries, is the “slow query log”. You define in my.cnf what a “slow query” is (in seconds), and then only queries that exceed this run time are logged.
#enable slowq logging in /etc/my.cnf
log-slow-queries = /var/log/slowqueries
The same permissions caveat applies here.
# touch /var/log/slowqueries ; chown mysql:mysql /var/log/slowqueries
These are just some notes, and more info will eventually find its’ why in here as I advance my SQL skillz. 🙂Tweet