Optimizing Mysql For Dummies
Written by Joe on February 21, 2008 – 4:36 pm -
Sorry, this isn’t one of those new, overpriced yellow books at your local bookstore. It’s not even a book, rather a blog post about a handy tool I came across. It’s a script that’ll help you configure your mysql settings, ideal for those that aren’t fluent in database server optimization. The author doesn’t include instructions, so I’ll help make it easy for those that aren’t familiar with executing shell scripts or using ssh.
First, if you don’t already have an ssh client (much more secure than using telnet), I recommend putty. It’s a free Windows utility. You can download putty.exe from here. Nothing to install, just run it as-is. Besides using it for this tutorial, you can also run useful commands like “service mysql restart”, “service httpd restart”, and “top” to name a few. At any rate, let’s get into the mysql optimization.
You can see / edit your mysql configuration file using pico, so open putty -> type in your web address -> enter your login name (probably “root”) -> enter password and you’re in. Before we go any farther, and assuming your mysql config file is located at etc/my.cnf, let’s make a backup. Type “cp /etc/my.cnf /etc/my.cnf.bak” (without the quotes) which simply makes a copy of that file in case something goes wrong (which can and will when you play with these kinds of files). To get to your mysql config file, type ”pico /etc/my.cnf”. Here you’ll get a visual view of what’s in the file… yours likely will be a bit different.
Now, from here I’m not even going to begin to suggest what the best settings for your server / VPS might be… not only because I’m not an expert but also because every setup is different. Different memory and processor performance, your programs might be taxing your server differently, etc. But, this is why I wrote this – a great program I found written by Matthew Montgomery, called the MySQL Performance Tuning Primer Script and found here. Don’t bother downloading it to your computer, we’ll do it through ssh. Ps – before using the script make sure you mysql service had been running for at least 48 hours.
First, install it. While logged into your server through ssh, type “wget http://day32.com/MySQL/tuning-primer.sh” to download it, then “chmod u+x tuning-primer.sh” to set the proper permissions, and finally “./tuning-primer.sh” to run the program. The script will analyze your server and suggest the proper settings for settings such as Slow Query Log, Max Connections, Worker Threads, Key Buffer, Query Cache, Sort Buffer, Joins, Temp Tables, Table (Open & Definition) Cache, Table Locking, Table Scans (read_buffer), and Innodb Status.
Once you get your suggestions, exit out of the script back to the command prompt (ctrl X), then pico back into mysql’s config file “pico /etc/my.cnf” and change the settings accordingly. When finished, ctrl X back out and type “y” to save and overwrite the file (remember your backup!) You’ll need to restart mysql for the changes to take effect, so type “service mysql restart”, then I’d run a “top” command to watch the server loads… hopefully after things settle the load will be much better. Pay especial attention to the user “mysql” and how much CPU and memory it’s taking up.
I’d give it a couple days and run this optimizer again, remembering all you have to do now that it’s installed is type “./tuning-primer.sh” to run it. To close putty simple type “exit”. This is much easier then how I normally do it (playing with settings for days, watching changes work and not work – over and over), and likely better probably too. Happy optimizing!
Posted in Server Stuff | 34 Comments »
April 13th, 2009 at 8:26 am
Thanks for sharing this great tool. Is there a means to slow down the speed at which this script runs. Using putty, the first few lines just scroll up and all I can see are the last 6 or so suggestions.
May 4th, 2009 at 1:20 pm
Thanks!!!!!
November 23rd, 2009 at 3:00 pm
Nothing changes your opinion of a friend so surely as success – yours or his.
December 19th, 2009 at 5:25 am
Hi.
How are You?
Free java casino no download
casino
List flash no download casino
casino
Virtual casino birthday welcome bonus
casino
Flash no download casino without deposit
casino
Internet poker web sites
March 31st, 2010 at 9:00 pm
I was thinking I’d place a info here to train you the most effective way to create cash working with dating affiliate along with cost-free strategies. I succeeded to make four thousand bucks monthly using the steps inside this absolutely free article, http://bit.ly/5kmonthguide – click to read.
April 25th, 2010 at 10:05 am
Thanks for this but im confused on how to do via ssh.
May 1st, 2010 at 1:51 pm
gooday there, i just found your web portal via bing, and i would like to comment that you compose interestingly good via your web portal. i am truly moved by the mode that you write, and the message is superb. anyway, i would also like to know whether you would like to exchange links with my website? i will be more than happy to reciprocate and drop your link off in the blogroll. looking for your response, i give my sincere thanks and gooday!
May 10th, 2010 at 10:22 am
You should definitely check out the free $50 no deposit bonus being offered at our new Bet Phoenix Casino. Only available now at: Bet Phoenix. Good luck!
May 13th, 2010 at 6:45 pm
Great job. I’ll check and see if I can nominate your sitr to the 2010 website awards. I think this is kind of websites they are looking for.
May 31st, 2010 at 8:30 pm
Hello,this is Timmy Dopler,just observed your Post on google and i must say this blog is great.may I quote some of the Post found in this weblog to my local people?i’m not sure and what you think?in any case,Thank you!
June 15th, 2010 at 4:21 am
First thanks for the script,and second i got a lot of errors like this:
“tuning-primer.sh: line 440: bc: command not found”
how to solve it? i’m on vps..
thanks!
September 11th, 2010 at 4:28 am
Great Information, thanks for your useful Post. I will come back later _ Also great information about flirting : Singles
December 5th, 2010 at 12:22 pm
nicearticlecanyoupostmore? Thanks
December 10th, 2010 at 7:25 pm
Nice. Thank you for sharing this with us. its always fun to read good blog like this, keep up the good work.
January 2nd, 2011 at 12:18 am
You can see / edit your mysql configuration file using pico, so open putty -
January 2nd, 2011 at 7:01 am
You can see / edit your mysql configuration file using pico, so open putty -
January 7th, 2011 at 2:24 pm
Nice post. I learn something more challenging on different blogs everyday. It will always be stimulating to read content from other writers and practice a little something from their store. I’d prefer to use some with the content on my blog whether you don’t mind. Natually I’ll give you a link on your web blog. Thanks for sharing.
January 17th, 2011 at 5:01 pm
panama city spring break09
candice michael naked
dish network n3 fix
mucus in 10 month old baby poop
www dcash hot pussy pics com
apa illustrator citation
earflap hat knifty knitter pattern
mtd snowblower replacement track part 24
tijuana street walkers on youtube
rapper zro videos
free printable baseball field diagrams
sss table of contribution
the scarlet ibis lesson plans
trajectory chart 22 lr
george thoroughgood
shawty lo a town lyrics
busco porno infnatil
french braid wrap leather
boygushers com
3d lolicon bald pussy
January 18th, 2011 at 10:47 pm
I am really thankful to this topic because it really gives great information “”;
January 19th, 2011 at 5:24 pm
I am very thankful to this topic because it really gives up to date information ~’.
January 26th, 2011 at 4:27 pm
I actually stumbled across an intriguing element not long ago to generate income throughout committing to old watches bullion current market. I think it’s time unique because the old watches economy seems to be exploding these days and very not everybody are in reality profiting from that.
March 1st, 2011 at 10:16 am
I am linking this web page from my personal web page .
March 17th, 2011 at 8:30 am
Great post thanks – I was a bit confused at first but I went over your instructions again and understand it now.
April 29th, 2011 at 5:11 am
I get this erro
“- INITIAL LOGIN ATTEMPT FAILED -”
after ./tuning-primer.sh command
Can you help me ho to fix it?
May 15th, 2011 at 5:06 pm
For a slightly less techy approach, I keep my Wordpress databases optimized by using the DB-Manager plugin which has an auto optimize feature.
May 29th, 2011 at 6:47 pm
What exactly youre indicating is utterly legitimate. I’m sure of which all people have to claim the same principle, although I just now imagine that people said in a fashion that all people can certainly fully grasp. When i likewise appreciate this graphics people put in place in this article. They can fit and so very well with the information youre seeking to claim. I will be guaranteed youll get to lots of people with the information youve had got to claim.
June 17th, 2011 at 4:32 pm
I am
really
thankful to this topic because it really gives great information “”;
June 22nd, 2011 at 4:21 pm
Your site is great thanks for the info. Also learn how to be acne free today using this method
August 6th, 2011 at 8:50 am
Give a try at cupid dating site and practise how to get a visit.
August 30th, 2011 at 7:03 am
check out room buddies for good info
September 4th, 2011 at 11:47 am
Recognize Stain (position the maxim goes), the main parities appeared to be cured from ‘04, as outlined by BIS:
September 29th, 2011 at 4:56 am
Well, It looks quite nice and went well until I got :
which : no bc in (/usr/local/sbin:/usr/local/bin:/sbin:/
…..
Error :command line calculator ‘bc’ not found!
Then I’m stack
December 29th, 2011 at 4:40 pm
Vielen Dank für dieses Blog-es ist toll! Ich mag diese Art von Menschen, die Wissen mit anderen zu teilen.
January 18th, 2012 at 4:09 am
Does internet affiliate marketing function !? that is the query which was blaring inside my go for that very first 30 days and a half involving my personal affiliate marketing profession. It is very all to easy to end up being …affiliate marketing