Triple The Volume Your Tracker Can Handle By Using The Dark Arts (42)
Triple The Volume Your Tracker Can Handle By Using The Dark Arts
We've talked about hosting. We've talked about landing page optimisation. But now, it's time to consider using the dark arts.
I'm referring, of course, to the dark arts of system administration.
Yep, in today's tutorial we'll be diving right into hardcore MySQL optimisation, command-line utilities, shell scripts, and all kinds of other intensely beardy-UNIX stuff.
But fear not - it's not as scary as it looks, and particularly if you're starting to drive some volume on your campaigns, it can make a MASSIVE difference to your ROI.
What You'll Need
We're going to be making some pretty deep-level edits to your MySQL system today. That means that you'll need:
- To be running MySQL. If you're using any standard tracking program (Prosper202, CPVLab, STM Mobile Tracker, POFpro, and most others), you'll be using MySQL.
- Command-line access to your web server using an SSH client like Putty or Terminal. You don't need this for the first part of the tutorial, but you will for the rest of it.
- At least basic familiarity with the Linux command line. You don't need much - if you can make new directories and edit text files you'll be fine.
- To have some testing time where it's not a disaster if your server crashes. Particularly for the third part of this tutorial, things Could Go Wrong, so don't do this on a server that's mission-critical.
What We'll Achieve
In my tests, working with an old Prosper server, I was able to:
- Reduce the maximum initial load time for the tracking down from over 8 seconds to under 300ms
- Reduce the maximum redirect time from over 3 seconds to less than a second.
- Take the practical maximum load for my server from around 50 concurrent users to at least 250 (and possibly more) - in other words, taking the server from being able to handle 18,000 clicks a day to over 90,000.
- Reduce the time for Prosper to generate a report by a considerable amount.
Summary
As always, here's the short version in case you've only got five minutes to read this while your image scraper runs:
- STEP 1: Run Optimize Tables on your database. Set up a cron job to run it daily.
- STEP 2: Install Memcached.
- STEP 3: Run this MySQL Server Optimisation Script, test their initial recommendations, then make them permanent in your my.cnf file.
Step 1: Optimise Your Tables!
There's a single 51-character command that can speed your database up 3,200%. No, really.
Here's a Blitz.io test that I ran on my Prosper installation before I ran The Command:
And here's one afterward:
Want some of this action? Then just log into your server via SSH, and type the following at the comment line (replace MYSQLUSER with your MySQL user name):
mysqlcheck -u MYSQLUSER -p -o --all-databases
An old database that hasn't been optimized in a while can be massively, massively slower than a freshly-optimised one. It's definitely the first thing to do any time you want to speed up your tracking.
Running Optimize Tables automatically
There's a good chance you already knew about Optimize Tables. I did - but had I bothered to run it on my Prosper install in a while? Nope. It's one of those things that's very easy to forget.
But handily, it's easy to set it up to run automatically - and this might be the single biggest optimisation you can make to your tracking if you're running volume.
From the command-line, type
crontab -e
59 05 * * * mysqlcheck -o -u <mysql username> -p <password> -o --all-databases
That'll keep your databases in tip-top shape.
Running Optimize Tables Without Command-Line Access
If you're on a system that doesn't have SSH access (like a Beyond Hosting starter server), you can still set all this up.
To optimize your tables manually, assuming that your system uses PHPMyAdmin to administrate MySQL, click on the database you want to optimize, then:
1. Click "Check All"
2. Choose "Optimize" from the drop-down list.
To set up automatic optimization, you'll need to set a cron job up using whatever control panel your host offers. Ask your hosts or check their documentation for the best way to do that. Here's Beyond Hosting's excellent guide to setting up a cron job - http://wiki.beyondhosting.net/Configure_a_cron_job
The cron control panel will require a command to run. Use this command:
mysqlcheck -o -u <mysql username> -p <password> -o --all-databases
Step 2: Install Memcached
What's better than making your database faster? Avoiding using it entirely.
And that's exactly what memcached does.
Originally developed for emo teens and Russian bloggers over at LiveJournal, memcached minimises the number of times an application has to even call on a database. It only works with applications that are designed to use it, but fortunately many popular tracking applications - including Prosper 202 - are.
Want to see the results of enabling Memcached? Well, I didn't even have to do the benchmark on this one, because Nana Gilbert-Bafoe, the maintainer of Prosper 202, already did a fascinating series of benchmarks - http://prosper.tracking202.com/blog/...ect-technology. Short version? The Memcached-enabled version of Prosper outperformed the regular one 2:1.
Once it's enabled, Memcached just sits there and does its job. It can be a bit of a pain in the ass to install, though.
Ideally, I'd recommend asking your hosting provider nicely if they'll install Memcached for you. If not:
- If you're on Beyond Hosting, they've got a great write-up on installing Memcached on their servers. (I'm not sure if and when they'll install Memcached for you- Tyler, care to comment?)
- If you're on Debian or Ubuntu Linux, "apt-get install memcached php5-memcached" will do the installation work for you. You'll need to restart your web server after that's installed.
- If you're on RedHat or CentOS, this appears to be a pretty good guide to installing memcached.
- If you're on something else, ask your hosts, Google, or comment below and I'll try to figure it out!
Step 3: Install An Artificial Intelligence!
Well, OK, that's a bit of an overstatement. But the best way to proceed IS now to install a simple Expert System to tell you what to do!
Back in the Dark Ages of web administration (say, 2002), an article like this would have now listed 20 or 30 wierd, arcane MySQL variables to test and tweak.
To make matters worse, I couldn't even say "OK, set foo_bar_obscure = 50", because database optimisation is a very individual thing. Settings that will work for a 512Mb VPS running a simple landing page will fail horribly on a 4Gb dedicated server running a more complex funnel. Settings that will work well for Prosper will cause other tracking systems to slow to a grinding crawl. And so on.
Frankly, back then, the best I could have done would be a massive list of options: "If blah, then test blah, and if that turns out to be true, then try adjusting blah_foo to 50 gigglepixels, then retest.".
Does that sound a bit like a program? Well, it turns out that a program is the answer to this problem. Yep, rather than hiring an expensive system optimisation expert or learning three books' worth of knowhow about MySQL, you can download a script which runs on your server, examines how your MySQL is working, and gives you good recommendations for stuff you can optimise.
(It's not quite as good as hiring a MySQL expert, still - but it's a lot cheaper, and will still give you some pretty major optimizations.)
How well does it work? Well, here's a blitz.io test of my Prosper referrer speeds before I ran the script:
And here it is after I ran the script and implemented its recommendations a couple of times:
Before: 2294 ms average referral time. After: 687 ms.
Let's do this thing!
Note: this is quite an advanced process, and could crash your server or possibly even corrupt your database. Don't do this if you really, really need the server to stay running, and do back up first!
Using The MySQL Optimization Script
- Run a benchmark on your tracking system so that you know where you're starting, performance-wise. You may also want to take a backup of your database using mysqldump or PHPMyAdmin.
- Log into your server and create a directory somewhere outside your web directories - inside /tmp is fine - called "mysqltest"
- CD into that directory, then run "wget http://www.day32.com/MySQL/tuning-primer.sh".
- Run "sudo sh ./tuning-primer.sh"
- It should find a socket for MySQL - type "N" when it asks you if you want to provide a different socket.
- Then it will ask for your MySQL login details: provide them. It'll also ask if you want it to create a "my.cnf" file: say no for now.
- And that's it - it'll run and give you a big pile of information, like this:
Now, look through all the information it's just spat out at you for any yellow or red text - that's stuff that you can change and improve. In the above example, we need to boost our table_cache.
First, let's do that temporarily. Run MySQL from the command line - "mysql -u <mysql user> -p", then type:
SET GLOBAL <variable to change> = <number recommended>
SET GLOBAL table_definition_cache = 512
Now, run your benchmark again. If you're happy with the result, move on - otherwise, run the script again and see if it has any other recommendations.
Making It Permanent
Once you've got a setup that you're happy with, you need to make it permanent. To do that, locate your my.cnf file in your system - it'll usually be in /etc/mysql. Run "cp my.cnf my.cnf.old" to create a backup in case something goes wrong. Then, either open that in your Linux text editor of choice, or download it and edit it on your main computer.
At the end of the section beginning [mysqld], add each of the variables you changed, and their new value. For example, we'd add a new line at the bottom of the section, in which we'd write:
table_definition_cache = 512
Problems?
A couple of the variables that the script recommends you change are different when you're changing them using SET GLOBAL. For example, to change table_cache, you actually want to run "set global table_open_cache". Google for the variable name if you have a problem - it'll usually be pretty obvious what you need to do - or run SHOW VARIABLES LIKE "%<insert the first word of the variable >%"; to see variables named similarly.
Some variables can't be changed live, like innodb_buffer_size. You don't have much of a choice here - test everything else first, then test those variables by adding them to my.cnf, restarting mysql, and re-running your benchmark.
It's rather easily possible to run your server out of memory if you're incautious with this testing. Use the command "top" to keep an eye on your memory usage whilst you're benchmarking, and lower the settings you're testing if you start running out of memory.
And there you have it! If you've made it all the way to the end of this tutorial, you've ventured into the very heart of server administration, where brave men fear to tread - and hopefully come out with a much higher-powered server!
Any other tips? Confused by anything? Think there's an error in this tutorial? Comment below and I shall respond!
Ummmmm..... Can BeyondHosting set this up for us?
:-)
Heh - it does look pretty scary, I know, but actually, a lot of this stuff is simpler than you might think. Dig in, be careful and keep backups, and it's all very doable without being a Proper Sysadmin - goodness knows I don't really qualify for that title 
Yeah alright, I'll bite. Have some time before a contractor wakes up anyway, since I'm waiting on him...... Let's do this.
Cool stuff! Let us know how you get on!
1. Done
2. Waiting on BH
3. Waiting on BH to do 2 so I can run 3 based on the output of 2
Cheers!
ps Not gonna lie it felt pretty l33t logging in to my server via Terminal.
Ouch... @.@ here after reading this...
Awesome info caurmen.
I have done this for a long time on my servers and helps a lot to keep redirects and landers fast.
Would you recommend the same for CPVLab? Haven't used p202 in ages.
Me Gusta <3
Fuuuuu, STM subscription just paid for itself... again!!! You're the man!
Glad everyone's finding it useful!
@timtetra - Yep, the same steps should work for any tracking software that uses MySQL. There's nothing Prosper-specific in here, just general good database practise.
Damn, all the advanced mysql stuff at the end did nothing for me :'-( There's a reason I instinctively hate anything with SQL in the name.
Ah, darn! Did the recommended settings not improve your performance, or was there a problem with the script?
Adjusted settings based on the primers recommendations, no observable difference in blitz.io test - made permanent adjustments to my.cnf and rebooted mysql to make sure, no luck. Did however reduce my VPS memory usage quite a bit, must have had some inappropriate base settings in there somewhere. How do your blitz.io tests look for timeouts/errors? I always get quite a lot after ~100 concurrent users but not at all sure what the standard response is.
I'd hope doing this removes any need for going to a Litespeed setup.
Thank you!
My kind of post. Now if only I could get blitz.io to validate my domain in order to test this out.
Originally Posted by thedudeabides
@zeno - I don't usually see many if any errors or timeouts. After going through those steps on my old Prosper database, I ended up with 0 timeouts, 1 error. Having said that, when testing database settings I usually set the timeout in Blitz to about 5 seconds, to get a more accurate idea of what's going on.
If you've reduced memory usage and are seeing timeouts/errors, I'd recommend aggressively pushing some of the memory-intensive settings next. Max connections, table cache, and (depending on what your CPU usage is like) thread settings. It can sometimes take setting the darn things pretty ridiculously high to improve your system's response - for example, I think my table cache is around 500!
Alternatively, it could be that the bottleneck's your web server rather than your database - in which case, stay tuned for this week's thrilling installment...
@thedudeabides - what problems are you having with Blitz.io's validation?
I'm a newbie when it comes to load testing...on blitz.io, will i just want to enter in my tracking URL, and then it will measure the response time for redirect+LP load, correct?
~Ian
Handily, there's an STM tutorial on just this subject - how to use Blitz.io to test your server: Check Whether Your Server Can Handle Your Traffic!
Let me know if there's anything that's still not clear after reading that!
Note: that's for load-testing your server. In other words, that's for checking how it performs when a LOT of people are hitting it at the same time. If you're looking for a speed test (ie how fast your LP loads normally), http://tools.pingdom.com is my usual go-to application, and that is simply a matter of "enter URL, choose location to test from (hidden underneath the text box, natch), test".
I'm trying to run the shell script from step 3 "sudo sh ./tuning-primer.sh" with and without sudo but it giving me this error. I'm using LiteSpeed, might that be the problem?
root@vps [/tmp/mysqltest]# sudo sh ./tuning-primer.sh which: no bc in (/usr/bin:/bin) Error: Command line calculator 'bc' not found! root@vps [/tmp/mysqltest]# sh ./tuning-primer.sh which: no bc in (/usr/local/jdk/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/bin:/usr/X11R6/bin:/root/bin) Error: Command line calculator 'bc' not found!
Aha - right, this is a common problem. You're lacking the command-line calculator "bc" that the script uses.
If you're on Debian or Ubuntu, just run
sudo apt-get install bc
If you're on CentOS or RedHat, run
sudo yum install bc
Once bc is installed, the script should run fine.
Originally Posted by caurmen
Hey caurmen,
Is this something can be done on a fresh prosper202 installation? I feel somewhat afraid to try it on a tracking domain that is making most of my money.
Also, currently I am experiencing problems with accessing stats. Everything is so slow. I'm really crap at techie stuff and was wondering if following this tutorial is risk-free: http://www.pjkdirect.com/2011/03/speeding-up-prosper202
Thanks,
Kai
@kai1 - most of the suggestions here are significantly more risk-free than the tutorial you link, which involves deleting data (albeit after backing it up first).
If your system is slowing down, I'd strongly recommend backing up your database then running OPTIMIZE TABLES, at least - as you can see in this tutorial, it has a pretty dramatic effect, and is usually safe.
I'm actually going to be looking more at Prosper slowdown issues this week, so stay tuned!
Hey caurmen, thanks for the reply. So by optimizing tables I can still access all the data when I log in?
Will definitely stay tuned.
Yep, exactly so. It's a one-line command to massively speed up your database, most of the time.
SWEEEEEEEEEEEEEEEEEEEEEEEEEEET! You da man!
Thanks, will give it a shot on an old account and then dive into my main one which is starting to crawl at snails pace. Just saw you started a thread to conquer the dreaded problem http://stmforum.com/forum/showthread...blem-FOR-GOOD.
This forum seriously kicks ass!
Optimising tables works!
Just implemented this on 3 tracking domains. One is still painfully slow, but I don't use it anyway so no bother, the other two are significantly quicker to load.
Thank you caurmen! No more staring at prosper202 waiting for it to crash!
No worries! Glad to be of help.
Hopefully with my current project we can speed up Prosper still further to the point this is no longer a problem
Caurmen - If I ask my host to install mariadb, can they import all my tables and settings in my current prosper202 mysql database? I dont want to start over... also do they have an interface like phpmyadmin where I can go in and delete clicks and make a few cpc edits or whatever I need in the tables? If not, is it just via command prompts and SSH? I would hope not as I have no idea how to really use stuff through that...
Thanks !
MariaDB is a drop-in replacement for MySQL - it actually works with existing MySQL tables and will work with programs like phpmyadmin without any changes needed. It's basically the same as just upgrading MySQL - install and go. You won't need to change your databases or the way you manage them at all.
Hey caurmen,
I'm just going to launch my first campaign. I'm not that technical, but I do understand the concept you taught. Would you receommend it adding right in the begining or, First run a campaign, optimize it for other things and then optimize for tracking?
thanks
Hey Caurmen,
I know this is an old post. Is this still effective today?
I'm running a shared hosting in Hostgator and just tried this. No dice!
user@xxx.com [/etc]# sudo yum install bc
sudo: effective uid is not 0, is sudo installed setuid root?
user@xxx.com [/etc]# su -
-jailshell: /bin/su: Permission denied
user@xxx.com [/etc]#
I don't think HG is going to give me root access on their server LOL.
Now, I'm running CPV Lab on there. Just had to get it reinstalled but figured I'd try this method here to see if I can improve load times.
Let me know what you think. All the other commands: mysqlcheck -u userxxx -p -o --all-databases I don't know the MySQL Database password.
If I reset the password for the MySQL database in the CPanel, will it cause problems with anything else like the CPV LAB software?
It still works, but most of these tips won't work on shared hosting.
I'd strongly advise you to get at least a VPS in any case. Shared hosting really isn't a good idea for AM as you're not in control of the resources you need.
Hope that helps! You can get a VPS from $5 a month on many providers - one of the best investments you'll make in AM!
Originally Posted by caurmen
Yup, I can suggest a few around that price range.
Digital Ocean are pretty good. Cheap and cheerful, good to get started with.
Vultr are more like $10pm iirc, but get very good reviews.
And Linode servers are, again, very good. $10 rather than $5 pm at base but worth it.
Depending on what tracker you're planning to run you may need a bit more power - check with support. But overall your VPS costs should end up being a fraction of what you spend on paid traffic, so it's worth it to make sure your hardware can cope.
Originally Posted by caurmen
I know my way around the Linux Command line so that doesn't scare me.
I'll take a look into this and grab one and test And tweak it.
Home > Hosting, Servers & Security >