FREE THOUGHT · FREE SOFTWARE · FREE WORLD

MySQL Performance Tuning Scripts and Know-How

mysql-tuning-primerUnless you are a total linux-freak-guru like myself, and even if you are, it can be enormously challenging and somewhat overwhelming to locate and eliminate MySQL bottlenecks. While many DBAs focus on improving the performance of the queries themselves, this post will focus on the highest-impact items: MySQL Server Performance and OS Performance for MySQL. Specifically showing 4 awesome tools to auto-tune your MySQL and a few more tools just for monitoring!

MySQL

View all MySQL Variables for Pasting into my.cnf

mysql-variables-my-cnf-fileThis is really useful for me because I work with dozens of different database servers. The first thing I do is run this command and paste it into the servers /etc/my.cnf file. That way I will always know the original value and it just makes life much easier.

$ mysql -NBe 'SHOW VARIABLES' |sed 's,\t,^=,'|column -ts^|tr "\n" '@'|eval $(echo "sed '" "s,@\("{a..z}"\),\n\n\1,;" "'")|tr '@' "\n"|sed 's,^,# ,g'

MySQL

MariaDB with LZ4 Compression Howto for CentOS

I really really wanted to run the latest MariaDB with LZ4 Page Compression.. it is a game changer for many types of large databases I deal with. So I compiled it manually in a way that is repeatable and follows best-practices and it's now powering this site. Now I can use InnoDB Page Compression with lzo, lzma, bzip2, snappy, or my favorite algorithm: LZ4.

Thought this would be a good chance to post a howto, and show that there is a lot you can do by compiling software yourself and breaking the package management one-click install shackles.

Linux, MySQL

The Latest and Greatest php.ini

Grab the latest php.ini developmental version and discover new or previously hidden php runtime configuration settings... ahead of everyone else!

PHP

30x Faster Cache and Site Speed with TMPFS

NOT a typo.. 30x is measurable, well-documented, and easily tested. This is what open-source is about. I haven’t had time to post much the past year, I'm always working! So I wanted to make up for that by publishing an article on a topic that would blow your mind and be something that you could actually start using and really get some benefit out of it. This is one of those articles that the majority of web hosting companies would love to see in paperback, so they could burn it.

Optimization

Vetted – Top 3 WordPress Speed Plugins

There are so many WordPress plugins out there now that I wanted to post my favorite 3 plugins for speeding up a WP-Powered blog. These are the 3 plugins that I install for pretty much all of my WP-Powered sites, which I run about 300 now. They work together to provide a very optimized blog for speed.

DB-Cache Reloaded does something entirely different, it saves the mysql queries that are made to the WP-database, as well as the mysql results to static files, and then through php serves those cached-files instead of re-querying the mysql database. Most mysql databases are stored on separate servers, and although many are on the same local network there is a limit to how many queries, and how many connections can take place.

So DB-Cache Reloaded basically makes WP-Super Cache work alot faster when generating the cache files, and DB-Cache Reloaded helps in a number of areas un-related to WP-Super Cache, like in the admin panel. And DB-Cache without WP-Super-Cache is a joke because it still uses the application-level and php for everything. Gotta use both (or just WPSC).

WordPress

Optimizing Servers and Processes for Speed with ionice, nice, ulimit

To prepare for several upcoming articles on AskApache that are focused on optimizing Servers and Sites from a server admin level, here is an article to introduce the main tools that we will be using. These tools are used to optimize CPU time for each process using nice and renice, and other tools like ionice are used to optimize the Disk IO, or Disk speed / Disk traffic for each process. Then you can make sure your mysqld and httpd processes are always fast and prioritized.

Optimization

Advanced WordPress wp-config.php Tweaks

The bottom line for this article is that I want to make WordPress as fast, secure, and easy to install, run, and manage because I am using it more and more for client production sites, I will work for days in order to solve an issue so that I never have to spend time on that issue again. Time is money in this industry and that is ultimately (time) what there is to gain by tweaking WordPress.

Note: I spent no time on readability, this is primarily a read the code and figure it out article.. This is for advanced users looking for a reference or discussion and for those of you looking to advance. Feedback would be great if you make it that far..

WordPress

Encrypted WordPress / phpBB Backups

Enter your DOMAIN_ROOT and the location of your wp-config.php or config.php, and this script finds all the mysql settings by parsing the phpbb or wordpress config file, then creates GPG encrypted backups, and saves your settings for future automation.

Shell Scripting

Fix Linux Users Home Permissions with a Cron Job

As a security nut myself, and also a Linux admin, one of my biggest pet peeves is when I've taken the time and care to segment all the users on a server into separate home directories, and then some developer comes along, logs in as root, and changes the ownership of files. Other things can cause this, like Apache, PHP, Mutt, etc.. So I've always used a cron job that executes daily (and on demand) which automatically fixes all the permissions back to what they should be.

Linux

Update: AskApache Password Protect Plugin

The Plugin Control PageWordPress plugin gives you control over HTTP Basic Authentication for your WordPress blog which among other things, stops most automated hacking attempts and exploits being attempted, cutting down on the number of requests, connections, and mysql queries for all WordPress blogs on the Internet.

WordPress

Port Redirector

can you please suggest me any tiny and good port redirector for linux and unix like oses ? so that when I connect to the box to a given port it redirects my connection to another box and port that I can chose ? thanx a lot

Security

Faster Form Submission and Processing with fsockopen

Speedy Forms using background-requestsPart II: Example illustrating how to speed up GET/POST form submissions. Uses fsockopen to initiate a server-side background request to process the submitted data, so that the result page of the form is displayed to the client lightningly quick.

PHP

Bayonetta Trailer

You've probably seen this.. one of THE coolest video games I've ever seen (though I haven't played).. Its tempting me back to MLG.. Games are games today, but in the future Gaming Skill will be highly sought after.. So I check in with the gaming world occasionally, I'd hate to miss something like a new Duke-Nukem (cancelled after 10 years in development and no releases).

I have around 10 really nice articles I've been working on for awhile now, my problem is that I am a perfectionist, so I keep finding improvements.. worth it.

Web Design

WordPress RewriteRules Viewer

AskApache RewriteRules Viewer Plugin PanelOften I am programming a plugin, or modifying my .htaccess rules, or editing a WordPress template file and I get stuck when it comes to how WordPress rewrites url's internally. This simple plugin displays all the internal WordPress rewrites.

WordPress

Caching WordPress with Batcache and Memcache

join-meUsing memcache for me personally and the client sites I work on, has improved performance by over 1000%, in some cases even higher than that. It is a truly revolutionary way to optimize the performance of MySQL queries, remote requests such as to twitter or other apis, and this article is step 1. Plan is to go all the way to step 10 in future articles, it it grossly underused and misused in the WP world.

Optimization, WordPress

Google Trends for SEO / Apache Htaccess

.htaccess Topic vs htaccess Keyword

Google, Htaccess, SEO