Arlo Gilbert’s Blog

On Sunday at approximately 3:00 PM, my wife’s grandfather passed away.

He was a stubborn, loving man who lived his life the way he saw fit for better or for worse.

While I was not very close to him and only met him a handful of times, I can’t help but feel sad inside, not because he died, but sadness for the people who he left behind.

I’m sure my thoughts are not unique, but I feel that death is a natural part of the cycle of life. I will die. You will die. We all will die. It is the only guarantee in life, that it will end. So when a person dies I don’t feel terribly bad for them, but for the ones they left behind I feel terrible.

Ellis (my wife’s grandfather) had a wife of 50+ years. They spent most of their waking moments together after he retired, traveling in the RV, they watched television together, she made his meals, he took care of “manly” work… but they built an entire routine around eachother. From wake til sleep they were eachother’s most important person. They were partners in life.

The sadness I feel is for his wife Kay. She is now faced with one of the hardest parts of life (I imagine as an outside observer) which is finding a reason to live and finding a way to move on when the person closest to you disappears. I can only imagine her waking up in the morning and thinking “what do I do? he doesn’t need breakfast!” and I imagine a moment where she giggles at a late night talk show host, only to realize that she is alone in the room.

I guess the only purpose of this post was to put down “on paper” that I feel badly for people who lose somebody because change is hard, big change is really hard, and the older you get, the more change stinks.

Here’s to you Kay… and as Ellis would say “Man Alive!”

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