MySQL skip-grant-tables
I have administered and pushed MySQL to it’s limits for years, many boxes have fully melted under the load I hit them with, but today I’ve been absolutely blown away by this little used, not so well known configuration switch that DOUBLED the speed of every read and write.
It is important to note that this particular configuration switch skip-grant-tables completely disables all authentication that MySQL does so you should ONLY run this if your database is local and skip-networking is also included in the config, and this is not being implemented in a multi-user environment. If you implement this in a multi-user environment you will be allowing all users of the system to have full administrative control of MySQL, if you use it in a networked environment you will be allowing everybody who can access the MySQL port to administer the MySQL server.
The tremendous upside of this option is that when you disable grant tables, you effectively eliminate several queries that run before EVERY SINGLE connection you make, it’s not that it makes the server faster, it just stops MySQL from using it’s own internal myisam queries… I would have assumed that MySQL had been coded to keep the grant data in some sort of linked list, in fact when I tested this it was purely accidental because I was performance tuning a new box and had a little time on my hands to disable and enable every single option in our my.cnf file just to discover how each option impacted the benchmark tool I was playing with.
The only performance caveat I will add, is that this really will only improve performance in environments where you are handling many quick "connect/query/disconnect" scenarios, meaning that if you are running something like wordpress where every page load connects, does 300 queries, and then disconnects, the performance gain would be negligible.
With those warnings/thoughts, here are my performance tests… reads and writes both performed approximately the same:
WIth standard grant tables in place and only one user (root) in the grant tables:
mysqlslap –user=root –auto-generate-sql –iterations=3 -S /var/lib/mysql/mysql.sock –auto-generate-sql-load-type=read -e myisam –concurrency=3000
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 1.224 seconds
Minimum number of seconds to run all queries: 1.168 seconds
Maximum number of seconds to run all queries: 1.286 seconds
Number of clients running queries: 3000
With skip-grant-tables enabled in my.cnf:
mysqlslap –user=root –auto-generate-sql –iterations=3 -S /var/lib/mysql/mysql.sock –auto-generate-sql-load-type=read -e myisam –concurrency=3000
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.682 seconds
Minimum number of seconds to run all queries: 0.668 seconds
Maximum number of seconds to run all queries: 0.703 seconds
Number of clients running queries: 3000
Tags: mysqlslap, skip-grant-tables