Yes, it’s been a while, I’ve been moving around a lot, and frankly haven’t been all too inspired to post anything lately. I have a lot I can share with the AM community, but since it’s full of blood-thirsty money-hungry dishonesty, I gotta be careful and keep some goodies to myself.
Enough of the B/S – Here I provide you with some tips to keep your CPVLab DB running a little smoother.
First thing you will need is SSH access on your server. Don’t know what that is? Then this is probably too advanced for you. However, if you’re on a managed server, I’m sure your hosting company can have a techie help you out with this one.
After digging around I found some recommendations and these settings seem to have improved performance for me. You’ll want to pay attention and ensure that these settings are ideal for YOUR server, as every server is different, especially given the different RAM allocation. My *new* server that I am currently moving to has 5gb of RAM allocated so my innodb_buffer_pool_size is equal to 2gb (or 2048M).
Tweaking InnoDB Settings
These are just the settings I’ve discovered so far to help speed things up. Feel free to add your findings in the comments.
Firstly, you’ll need to edit the following file on your server: “/etc/my.cnf”
Once logged in via SSH:
- Type “edit /etc/my.cnf”
- Add or Change the following settings (Just be aware, these are settings that I USED for my server. Speak with your hosting company if unsure on which settings to use or do some of your own research. Typically, the one you should be most concerned with is innodb_buffer_pool_size
[mysqld] thread_cache_size=16K table_cache=128M local-infile=0 innodb_file_per_table=1 max_connections=100 key_buffer_size=16M join_buffer_size=1M query_cache_type=1 query_cache_limit=4M query_cache_size=128M max_tmp_tables=1 default-storage-engine=MyISAM innodb_buffer_pool_size=2048M innodb_flush_method=O_DIRECT low_priority_updates=1 concurrent_insert=ALWAYS tmp_table_size=128M max_heap_table_size=128M
- Restart MySQL
- /etc/rc.d/init.d/mysql restart
Automate Defragmentation via Cron & Bash
This next step is going to automate defragmenting your tables which can help speed things up also.
Firstly, you’ll need to create a new “bash” script on your server to be run via Cron later.
Lets name this file “dbdefrag.sh”
Type: “edit dbdfrag.sh”and paste the following code, modifying the user/pass for your DB accordingly.
#!/bin/bash MYSQL_LOGIN='-u USERNAME --password=PASSWORD' for db in $(echo "SHOW DATABASES;" | mysql $MYSQL_LOGIN | grep -v -e "Database"$ do TABLES=$(echo "USE $db; SHOW TABLES;" | mysql $MYSQL_LOGIN | grep -v T$ echo "Switching to database $db" for table in $TABLES do echo -n " * Optimizing table $table ... " echo "USE $db; ALTER TABLE $table ENGINE=INNODB" | mysql $MYSQL$ echo "done." done done
The next step is scheduling with cron.
- crontab -e
- Go to the end of the file and add the following line (note it runs at 4am every day)
- 0 4 * * * bash dbdefrag.sh
- CTRL+X to exit then hit Y to save
- Alternatively, you can set up a cron job via cPanel
Please consult your server admin before making any of these changes to yours. I take no responsibility if you mess your server up. Perhaps do these changes on an experimental install or server first before making the changes to your live one.
If you’re looking for further PPV Guidance, the guys over at PPV Playbook know their shit. Great community of experienced marketers from around the globe.
Can’t Decide Tracking Software?
Often I get asked which tracking software I prefer to use for tracking my campaigns. I used to be a p202 fanboy and use it exclusively, until I set my eyes upon CPVLab, which is now, to date, still my preferred method of tracking. You can get CPVLab here >> for less than $300 .. any serious affiliate marketer will know that good tracking is a worthy investment!