Gil's Blog

I have my aviation site Planenews on a FreeBSD server. As traffic increased, I was getting more database errors. Looking around the web for clues, I discovered that FreeBSD did not have a default my.cnf file in /usr/local/etc. You can find sample files in /usr/local/share/mysql. I used my-huge.cnf, renamed it to my.cnf, put it in /usr/local/etc, et voila (don’t forget to restart MySQL)!

Problem solved? Nope.. I was still getting errors at peak traffic. I then found mysqltuner, a Perl diagnosis tool for MySQL. I was missing a few variables in my.cnf. See the file below, and notice the additions under “Added by Gil.”

---

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 128M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4

# Added b Gil:
# max_connections 250 crashes my server, use with caution..
#max_connections = 250
wait_timeout = 180
interactive_timeout = 45
tmp_table_size = 64M
max_heap_table_size = 32M

# Disable Federated by default
skip-federated
skip-innodb
skip-bdb

# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/
#log-update     = /path-to-dedicated-directory/hostname

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

---

The site seems to be running fine now, with no errors. I guess I will have to wait for a story to make it to a major social networking site to see if it really can take a heavy load. Please tell me about your optimization tips, and how you prepared for traffic spikes…

I just discovered the Pretty Link Wordpress plugin, and I think it will help getting more returning visitors to my site, especially using the Pretty Bar feature. Problem is, I have 11,000 posts, with almost as many links. So, I wrote a Python Script to convert all your old post links to Pretty Links. You need to insert your database login on lines 23,24,25,26. Replace ‘planenews’ on line 49 by your site name, without the .com. Replace my domain by yours on line 58. If you leave line 69 as it, the script will set your Pretty Links to show with the Pretty Bar, standard parameter forwarding, group 1, traking on. If it works for you, you better send me a few bucks to buy a Cigar or a Cointreau, because this will save you a lot of time! (Click on the Donate button below), Scroll down to get the code…

Update: You may have to run it a few times until the script returns no result.. No time to figure out why.. Make sure you backup your database before playing with this!

#!/usr/local/bin/python import MySQLdb import sys import re from random import * import string ##### Functions. chars = string.ascii_letters images=['jpg','jpeg','png','gif'] def makeslug(): return “”.join(choice(chars) for x in range(randint(4,4))) def isimage(s): s = s.lower() return s[s.rfind(".")+1:] in images ##### Connect to MySQL. try: conn = MySQLdb.connect (host = “”, user = “”, passwd = “”, db = “”) cursor = conn.cursor() except MySQLdb.Error, e: print “Error %d: %s” % (e.args[0], e.args[1]) sys.exit url=re.compile(‘(?#Protocol)(?:(?:ht|f)tp(?:s?)\:\/\/|~\/|\/)?(?#Username:Password)(?:\w+:\w+@)?(?#Subdomains)(?:(?:[-\w]+\.)+(?#TopLevel Domains)(?:com|org|net|gov|mil|biz|info|mobi|name|aero|jobs|museum|travel|[a-z]{2}))(?#Port)(?::[\d]{1,5})?(?#Directories)(?:(?:(?:\/(?:[-\w~!$+|.,=]|%[a-f\d]{2})+)+|\/)+|\?|#)?(?#Query)(?:(?:\?(?:[-\w~!$+|.,*:]|%[a-f\d{2}])+=?(?:[-\w~!$+|.,*:=]|%[a-f\d]{2})*)(?:&(?:[-\w~!$+|.,*:]|%[a-f\d{2}])+=?(?:[-\w~!$+|.,*:=]|%[a-f\d]{2})*)*)*(?#Anchor)(?:#(?:[-\w~!$+|.,*:=]|%[a-f\d]{2})*)?’) ##### Get already used slugs slugs={} query=”SELECT slug,url FROM wp_prli_links” cursor.execute(query) for line in cursor.fetchall(): slugs[line[1]]=line[0] ##### Get all links in post_content query=”SELECT ID,post_date,post_title,post_content FROM wp_posts WHERE post_status=’publish’ ORDER BY ID DESC” cursor.execute(query) count=0 for line in cursor.fetchall(): found=url.findall(line[3]) for link in found: try: if ‘planenews’ not in link and not isimage(link): if link not in slugs: # Generate slug slug=makeslug() slug=slug.lower() while slug in slugs: slug=makeslug() slug=slug.lower() slugs[link]=slug newlink=’http://planenews.com/’+slug print “%d : %s | %s” % (line[0],newlink,link) # Replace link in content newcontent=line[3].replace(link,newlink,10) # Clean-up for mySQL newcontent=MySQLdb.escape_string(newcontent) title=MySQLdb.escape_string(line[2]) # Save new content qq=”UPDATE wp_posts SET post_content=’%s’ WHERE ID=%d” % (newcontent,line[0]) cursor.execute(qq) # Save Pretty Link to database. qry=”INSERT INTO wp_prli_links (name,url,slug,nofollow,track_me,param_forwarding,redirect_type,created_at,group_id) VALUES (‘%s’,'%s’,'%s’,0,1,’on’,'prettybar’,'%s’,1)” % (title,link,slug,line[1]) cursor.execute(qry) else: # Swap already assigned slugged url. newlink=’http://planenews.com/’+slugs[link] newcontent=line[3].replace(link,newlink,10) print “ALREADY PRETTY: %s : %s” % (newlink,link) newcontent=MySQLdb.escape_string(newcontent) title=MySQLdb.escape_string(line[2]) # Save new content qq=”UPDATE wp_posts SET post_content=’%s’ WHERE ID=%d” % (newcontent,line[0]) cursor.execute(qq) except: print “ERROR” cursor.close() conn.close()

I had problems with the Google XML sitemap generator plugin because of my database size, more than 10,000 articles. Here is a Python script that will work no matter how large your database is. Make sure you edit the domain name at the end, for the home page entry, path to the sitemap.xml file, and of course, put in your database login info. Put the script on a cron-job, et voila! See the code below…

#!/usr/local/bin/python import MySQLdb import sys ##### Connect to MySQL. try: conn = MySQLdb.connect (host = “”, user = “”, passwd = “”, db = “”) cursor = conn.cursor() except MySQLdb.Error, e: print “Error %d: %s” % (e.args[0], e.args[1]) sys.exit ##### Open file for writing f=open(‘/home/gil/public_html/sitemap.xml’,'w’) ##### Print xml headers to file. f.write(“< ?xml version='1.0' encoding='UTF-8'?>n <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"n xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"n xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9n http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">nn”) ##### Get all posts data. query=”SELECT ID,post_modified FROM wp_posts WHERE post_status=’publish’ ORDER BY ID DESC LIMIT 10000″ cursor.execute(query) count=0 for line in cursor.fetchall(): count+=1 # Set priority to 1 for most recent 12 posts, then 0.6 if count>12: priority=’0.6′ else: priority=’1.0′ if count<13: freq=’daily’ elif count<100 and count>12: freq=’weekly’ else: freq=’monthly’ mod=str(line[1]) mod=mod.replace(‘ ‘,’T') mod=mod+’-05:00′ if count==1: modhome=mod urlset=”<url><loc>http://planenews.com/archives/%s</loc><lastmod>%s</lastmod><changefreq>%s</changefreq><priority>%s</priority></url>n” % (line[0],mod,freq,priority) f.write(urlset) ##### Save entry for home page urlset=”<url><loc>http://planenews.com/index.php</loc><lastmod>%s</lastmod><changefreq>daily</changefreq><priority>0.8</priority></url>n” % (modhome) f.write(urlset) f.write(“</urlset>“) conn.close() f.close()

Here is a script to convert you PHP-Nuke database to your new Wordpress one. It does not transfer comments, but does users, topics and stories. It is written in Python. You need to edit the database logins and specify your url and PHPNuke admin user id. The MySQLdb Python module must be installed. If it works for you, you better send me a few bucks to buy a Cigar or a Cointreau, because this will save you a lot of time! (Click on the Donate button below), Scroll down to get the code…

And by the way, here are the two lines to put in your .htacesss file to convert the old PHPNuke URLs to the new Wordpress numeric urls. The will turn, for example:

http://planenews.com/modules.php?name=News&file=article&sid=11117

into: http://planenews.com/archives/11117

So your visitors coming from an old link won’t miss a beat.. Here it is:

RewriteCond %{QUERY_STRING} ^name=News&file=article&sid=([0-9]*)
RewriteRule ^modules\.php$ /archives/%1? [R=301,L]

#!/usr/local/bin/python url=’http://planenews.com’ # We will conver your PHPNuke user to 1. nukeuser=40 import MySQLdb import re dt=re.compile(‘^(w+) (d+), (d+)$’) ##### INSERT IGNORE your Wordpress MySQL login below: try: conn = MySQLdb.connect (host = “”, user = “”, passwd = “”, db = “”) cursor = conn.cursor() except MySQLdb.Error, e: print “Error %d: %s” % (e.args[0], e.args[1]) sys.exit ##### INSERT IGNORE your PHPNuke MySQL login below: try: konn = MySQLdb.connect (host = “”, user = “”, passwd = “”, db = “”) kursor = konn.cursor() except MySQLdb.Error, e: print “Error %d: %s” % (e.args[0], e.args[1]) sys.exit ##### Import users query=”SELECT user_id,username,user_password,user_email,user_website,user_regdate,user_interests FROM nuke_users WHERE user_id<>1″ kursor.execute(query) user={} for line in kursor.fetchall(): user[line[1]]=line[0] print “%d %s” % (line[0],line[1]) lcname=line[1].lower() m=dt.match(line[5]) day=m.group(2) year=m.group(3) month=m.group(1) if month==’Jan’:month=’01′ if month==’Feb’:month=’02′ if month==’Mar’:month=’03′ if month==’Apr’:month=’04′ if month==’May’:month=’05′ if month==’Jun’:month=’06′ if month==’Jul’:month=’07′ if month==’Aug’:month=’08′ if month==’Sep’:month=’09′ if month==’Oct’:month=’10′ if month==’Nov’:month=’11′ if month==’Dec’:month=’12′ regdate=year+’-'+month+’-'+day+’ 10:00:00′ qry=”INSERT IGNORE INTO wp_users (ID,user_login,user_pass,user_nicename,user_email,user_url,user_registered,display_name) VALUES (%d,’%s’,'%s’,'%s’,'%s’,'%s’,'%s’,'%s’)” % (line[0],line[1],line[2],lcname,line[3],line[4],regdate,line[1]) cursor.execute(qry) desc=’Interests: ‘+line[6] caps=’a:1:{s:10:”subscriber”;b:1;}’ qry=”INSERT IGNORE INTO wp_usermeta (user_id,meta_key,meta_value) VALUES (%d,’%s’,'%s’)” % (line[0],’nickname’,line[1]) cursor.execute(qry) qry=”INSERT IGNORE INTO wp_usermeta (user_id,meta_key,meta_value) VALUES (%d,’%s’,'%s’)” % (line[0],’rich_editing’,'true’) cursor.execute(qry) qry=”INSERT IGNORE INTO wp_usermeta (user_id,meta_key,meta_value) VALUES (%d,’%s’,'%s’)” % (line[0],’comment_shortcuts’,'false’) cursor.execute(qry) qry=”INSERT IGNORE INTO wp_usermeta (user_id,meta_key,meta_value) VALUES (%d,’%s’,'%s’)” % (line[0],’admin_color’,'fresh’) cursor.execute(qry) qry=”INSERT IGNORE INTO wp_usermeta (user_id,meta_key,meta_value) VALUES (%d,’%s’,'%s’)” % (line[0],’wp_capabilities’,caps) cursor.execute(qry) qry=”INSERT IGNORE INTO wp_usermeta (user_id,meta_key,meta_value) VALUES (%d,’%s’,'%s’)” % (line[0],’user_url’,line[4]) cursor.execute(qry) qry=”INSERT IGNORE INTO wp_usermeta (user_id,meta_key,meta_value) VALUES (%d,’%s’,'%s’)” % (line[0],’description’,desc) cursor.execute(qry) qry=”INSERT IGNORE INTO wp_usermeta (user_id,meta_key,meta_value) VALUES (%d,’%s’,'%s’)” % (line[0],’s2_format’,'excerpt’) cursor.execute(qry) qry=”INSERT IGNORE INTO wp_usermeta (user_id,meta_key,meta_value) VALUES (%d,’%s’,'%s’)” % (line[0],’s2_autosub’,'yes’) cursor.execute(qry) qry=”INSERT IGNORE INTO wp_usermeta (user_id,meta_key,meta_value) VALUES (%d,’%s’,'%s’)” % (line[0],’s2_cat456′,’456′) cursor.execute(qry) qry=”INSERT IGNORE INTO wp_usermeta (user_id,meta_key,meta_value) VALUES (%d,’%s’,'%s’)” % (line[0],’s2_subscribed’,',456,602′) cursor.execute(qry) qry=”INSERT IGNORE INTO wp_usermeta (user_id,meta_key,meta_value) VALUES (%d,’%s’,'%s’)” % (line[0],’s2_cat602′,’602′) cursor.execute(qry) ##### Import topics. query=”SELECT topicid,topicname,topictext FROM nuke_topics” kursor.execute(query) tax={} for line in kursor.fetchall(): print “%d %s (%s)” % (line[0],line[1],line[2]) slug=line[1].lower() slug=slug.replace(‘ ‘,’-') qry=”INSERT IGNORE INTO wp_terms (term_id,name,slug) VALUES (%d,’%s’,'%s’)” % (line[0],line[1],slug) cursor.execute(qry) qry=”INSERT IGNORE INTO wp_term_taxonomy (term_id,taxonomy,description) VALUES (%d,’category’,'%s’)” % (line[0],line[2]) cursor.execute(qry) # Get taxonomy ID. tax[line[0]]=conn.insert_id() ##### Import stories. query=”SELECT sid,informant,time,hometext,bodytext,title,topic FROM nuke_stories” kursor.execute(query) for line in kursor.fetchall(): try: if user[line[1]]==nukeuser: user[line[1]]=1 except: user[line[1]]=1 hometext=MySQLdb.escape_string(line[3]) bodytext=MySQLdb.escape_string(line[4]) title=MySQLdb.escape_string(line[5]) guid=url+’/archives/’+str(line[0]) content=hometext+’<p>‘+bodytext qry=”INSERT IGNORE INTO wp_posts (ID,post_author,post_date,post_content,post_title,post_excerpt, post_status,comment_status,ping_status,post_modified,guid,post_type) VALUES (%d,%d,’%s’,'%s’,'%s’,'%s’,'publish’ ,’open’,'open’,'%s’,'%s’,'post’)” % (line[0],user[line[1]],line[2],content,title,hometext,line[2],guid) #cursor.execute(qry) print “[%d | %s %d] %s” % (line[0],line[1],user[line[1]],line[5]) qry=”INSERT IGNORE INTO wp_term_relationships (object_id,term_taxonomy_id) VALUES (%d,%d)” % (line[0],tax[line[6]]) cursor.execute(qry) qry=”UPDATE wp_term_taxonomy SET count=count+1 WHERE term_id=%d” % tax[line[6]] cursor.execute(qry) cursor.close() kursor.close() conn.close() konn.close()

I can’t tell you how many times an acquaintance or friend has come to me with a broken hard drive asking “Can you get my data back?” as if their lives depended on it. The experience can be quite dramatic. I feel like a doctor telling family members that their loved-one has passed away. Losing all your data can be very stressful. Think about photos you can never get back, hours, weeks, months of work erased, just like that. You get the picture. Hard drives have moving parts, they spin at around 5000rpm. Soon or later, they will break, you can’t avoid it. Is your data backed-up? If you say yes, go do something else. Otherwise, keep on reading, you will thank me profusely some day.

It used to be that a couple CDs, or more recently, DVDs was enough to back-up all of one’s data. Today, with videos, movies, high resolution cameras, data fills-up hard drives like shoppers at Walmart on black Friday. Not to mention that if your drive bites the dust, you will have to reinstall your operating system. If you’re using Ubuntu like I am, not big deal, it’s free. If however you can’t find your Windows registration, guess what, $200 for Micro$oft. Hopefully you also have all your software somewhere safe, ready to be reinstalled.. Think about the amount of work though.. Who has the time to sit in front of a computer looking at a progress bar all day, or pay some geek a fortune to do it for them? I don’t.

These days, you are looking at two options. 1, a full drive backup on separate hard drives. 2, an online backup service. Let’s look at both solutions..

A full drive backup is great because you can make a mirror image of your drive, which can then be restored to a similar drive. You don’t have to reinstall anything, your entire drive is copied bit by bit independently of your operating system. It takes a few hours, but the recovery is painless and very easy. You will need two USB external hard drives of the same capacity as your main hard drive. Why two? Because your hard drive can fail during a backup operation, rendering your backup useless! So, you alternate the drives and backup every other week. At the most, you’ll lose one month of data updates. The downside of this scheme is that you can’t use your computer while it is backed-up. I do it at night, usually it is finished when I get up. You can also lose quite a bit of data if you are not diligent enough. You will need a drive cloning tool. Get G4L, it’s free and works great. You burn it onto a CD and use it to boot your computer. Be careful when you choose your source and target drive, you don’t want to backup an empty or old image to your current drive! An remember, it only works if you actually do it regularly!

The online backup solution is very convenient. You pay a small monthly fee, usually $10, to have your data automatically uploaded to a storage facility via the Internet. You can choose what folders to save, or exclude certain types of files by extension or size. What I like most about it is that it is transparent. You can still use your computer as usual while the program works in the background. I don’t notice any performance difference with the service I use. You need to make sure that the transmission of your data is encrypted, and that it also be encrypted wherever it is saved. Nobody should be able to read your files. The best service I have found is SpiderOak. They give you 2Gb for free, and charge only $10 per 100Gb. Their client program is great, and all data is encrypted. You can even synchronize your laptop with your desktop, or other computers. The process is pretty slow the first time you use it. I just uploaded about 84Gb in the span of a week. After your initial upload however, it is much faster. The good part was that I didn’t have to care about it at all. It works on Windows, Linux and Mac.

Which solution is best? Well, I highly suggest doing both! Make a mirror copy of your hard drive (you can use only one drive then), and have your data backed-up online with SpiderOak. This way, after a crash, you load the drive image with G4L, then update your data from your online backup. Everything is up-to-date, and you don’t have to reinstall anything.

Can you afford to spend $80 on an external hard drive, or/and pay $10 per month? Can you afford not to? Can you lose all your data? Remember, it’s not “if,” it’s “when.” Back-it-up!