{themattreid}

Easy Python: multi-threading MySQL queries

2010-08-30T21:57:28Z

There are many times when writing an application that single threaded database operations are simply too slow. In these cases it’s a matter of course that you’ll use multi-threading or forking to spawn secondary processes to handle the database actions. In this simple example for Python multi-threading you’ll see the how simple it is to improve the performance of your python app.

#!/usr/bin/python
## DATE: 2010-08-30
## AUTHOR: Matt Reid
## WEBSITE: http://themattreid.com
## LICENSE: BSD http://www.opensource.org/licenses/bsd-license.php
## Copyright 2010-present Matt Reid

from __future__ import division
from socket import gethostname;
import threading
import sys
import os
import MySQLdb

class threader(threading.Thread):
    def __init__(self,method):
        threading.Thread.__init__(self)
        self.tx =
        self.method = method
    def run(self):
        run_insert()

def run_insert():
    sql = "INSERT INTO table (`id`,`A`,`B`,`C`) VALUES (NULL,'0','0','0');")
        try:
            cursor.execute(sql)
            db.commit()
        except:
            print "insert failed"

def init_thread():
    for thread in range(threads):
        background = threader()
    background.start()
    background.join()

def main():
    try:
        init_thread()
    except:
        print "failed to initiate threads"

    sys.exit(0)

if __name__ == "__main__":
    mysql_host = "localhost" #default localhost
    mysql_pass = "pass" #default dbbench
    mysql_user = "user" #default dbbench
    mysql_port = 3306 #default 3306
    mysql_db = "schema" #default dbbench
    threads = 4 #must be INT not STR

    try:
        db = MySQLdb.connect(host=mysql_host, user=mysql_user, passwd=mysql_pass, db=mysql_db, port=mysql_port)
    except MySQLdb.Error, e:
        print "Error %d: %s" % (e.args[0], e.args[1])
        sys.exit (1)
    cursor = db.cursor()

    main()
    db.close()

Easy Python: display LVM details in XML

2010-08-30T19:49:50Z

If you need to work with LVM in your scripts but haven’t found a good method to access details about Logical Volume Groups, here’s a simple Python script that will print the details about any volumes on your system. This could be useful for writing a partition check script for your MySQL data directory (if you’re not using a standard monitoring system like Nagios).

import sys
import os
import commands
import subprocess
import select

def lvm():
    print ""
    LVM_PATH = "/sbin"
    LVM_BIN = os.path.join(LVM_PATH, 'lvm')
    argv = list()
    argv.append(LVM_BIN)
    argv.append("lvs")
    argv.append("--nosuffix")
    argv.append("--noheadings")
    argv.append("--units")
    argv.append("b")
    argv.append("--separator")
    argv.append(";")
    argv.append("-o")
    argv.append("lv_name,vg_name,lv_size")

    process = subprocess.Popen(argv, stdout=subprocess.PIPE)
    output = ""
    out = process.stdout.readline()
    output += out
    lines = output.splitlines()
    for line in lines:
        line = line.strip()
        words = line.split(";")

        lvname = words[0].strip()
        vgname = words[1].strip()
        lv_size = int(words[2])
        print '''
    %s
    %s
    %s
  '''%(lvname, vgname, lv_size)

    print ""

lvm()

Easy MySQL: how to backup databases to a remote machine

2010-08-13T21:28:05Z

Here’s a simple answer to a simple question. “How do I run a backup of MySQL to another machine without writing to the local server’s filesystem?” – this is especially useful if you are running out of space on the local server and cannot write a temporary file to the filesystem during backups.

Method one – this writes a remote file.
mysqldump [options] [db_name|--all-databases]| gzip -c | ssh user@host.com "cat > /path/to/new/file.sql.gz"

Method two – this writes directly into a remote mysql server
mysqldump [options] [db_name|--all-databases]| mysql --host=[remote host] –user=root –password=[pass] [db_name]

Easy Python: MySQL connection and iteration

2010-08-30T19:52:47Z

If you’ve been looking for a simple python script to use with MySQL that you can use to expand upon for your next project, check this one out. It has error handling for the connection, error handling for the sql call, and loop iteration for the rows returned.

#!/usr/bin/python
import sys
import MySQLdb

my_host = "localhost"
my_user = "user"
my_pass = "password"
my_db = "test"

try:
    db = MySQLdb.connect(host=my_host, user=my_user, passwd=my_pass, db=my_db)
except MySQLdb.Error, e:
     print "Error %d: %s" % (e.args[0], e.args[1])
     sys.exit (1)

cursor = db.cursor()
sql = "select column1, column2 from table";
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
    column1 = row[0]
    column2 = row[1]
    print "column1: %s, column2: %s"%(column1,column2)

db.close()

How to: rotate wordpress posts into headline/feature status

2010-08-25T20:20:00Z

If you’re using the new Arthemia theme for WordPress you might notice that there are two areas of the theme that can have articles promoted to; namely Headline and Featured sections. This is controlled by category association. Basically you have a post and if you want it in the Headline area of the theme you attach the category “headline” to it, similarly for the featured section. Now, let’s say you don’t want to manually change this all the time since it can be time consuming to promote posts to those categories if you want rotating content.

Here’s a simple solution. In this bash script I connect to MySQL and remove the current associations from posts and then randomly choose posts to be promoted to the Headline and Featured categories. This can be modified for other ideas you might have involving categories/posts/randomized associations in WordPress.

The queries contain IDs for the Headline and Featured categories. In my installation, which will be different than yours, has the Headline category as ID=’103′ and Featured as ID=’104′ – replace as needed. I’m also doing some matching (see the WHERE sections) so that I don’t promote posts with certain IDs that are specific to the site for this script. You’ll want to customize the queries as needed for your site. You can find the script here: http://pastebin.com/1QqiM5rh

N900 – control all of your accounts with this script

2010-07-27T22:42:27Z

If you own a Nokia N900 cellular device you might be interested in the ability to control all of your IM accounts from the command line. For those that do not know, the N900 runs Maemo Linux and is capable of running MySQL embedded if you so choose. Here’s a quick script I wrote to provide that functionality for IM accounts. It’s at the bottom of the page, called “im-connections”.

wiki: http://wiki.maemo.org/N900_Mission_Control#Set_all_SIP_accounts_to_online_or_offline
pastebin: http://pastebin.com/qAC57E1N

Benchmarking MySQL ACID performance with SysBench

2010-06-20T23:49:30Z

A couple of question I get a lot from MySQL customers is “how will this hardware upgrade improve my transactions per second (TPS)” and “what level of TPS will MySQL perform on this hardware if I’m running ACID settings?” Running sysbench against MySQL with different values for per-thread and global memory buffer sizes, ACID settings, and other settings gives me concrete values to bring to the customer to show the impact that more RAM, faster CPUs, faster disks, or cnf changes have on the server. Here are some examples for a common question: “If I’m using full ACID settings vs non-ACID settings what performance am I going to get from this server?”

Let’s find out by running sysbench with the following settings (most are self explanatory – if not the man page can explain them):

sysbench –test=oltp –db-driver=mysql –oltp-table-size=1000000 –mysql-engine-trx=yes –oltp-test-mode=complex –oltp-read-only=off –oltp-dist-type=special –max-requests=0 –num-threads=8 –max-time=120 –init-rng=on run

MySQL Settings:

In the first test MySQL is set to the following ACID related settings. This will give us results for TPS performance without full ACID compliance – very common settings on a server that is handling blogs, ad serving, general business websites, and other roles where full ACID is not required and performance is valued over the benefits of full ACID. These are important settings when we look at the difference in performance when we change to full ACID in the second test.

innodb_flush_log_at_trx_commit = 0
sync_binlog=0
transaction-isolation=REPEATABLE-READ

System configuration and InnoDB buffer pool size:

XEON E5345 Series 2.33ghz 8-core, 16GB RAM, Local SATA 7.2K disks
innodb_buffer_pool_size = 10G

Full result set from sysbench:

Summary OLTP test statistics:

queries performed:
transactions: 172426 (1436.83 per sec.)
read/write requests: 3276664 (27304.51 per sec.)
other operations: 344882 (2873.91 per sec.)

Non-ACID results:

We can simplify the results by looking at the following TPS results for this non-ACID test:

transactions: 172426 (1436.83 per sec.)

Full ACID results:

Let’s go ahead and run the test again with different ACID settings. This will give us the TPS results for full ACID compliance:

innodb_flush_log_at_trx_commit = 1
sync_binlog=1
transaction-isolation=REPEATABLE-READ

We get the following results for TPS:

transactions: 3197 (26.58 per sec.)
read/write requests: 60743 (505.04 per sec.)
other operations: 6394 (53.16 per sec.)

Final Results:

So as you can see the difference between full ACID settings and not (on the same server with only those values on the cnf being changed) results in a huge difference in performance on this standard database server. We can now hand this data to the customer and they will know what impact the settings will have on their application’s performance and what to expect when running full ACID vs non-ACID.

More info on using sysbench here: http://sysbench.sourceforge.net

Amazon now accepts hard drives for EC2 data transfer

2010-06-13T05:37:29Z

I guess they got tired of people sending angry emails about data transfer fees:

“Amazon provides an online calculator to help customers decide whether it makes financial sense to ship data via mail rather than uploading over the Internet. You plug in the number of terabytes, devices, average file size, return shipping information and other factors, and find out how much the data transfer would cost via mail compared to standard Internet uploads.

For example, transferring data from a single device containing 2TB would require 26 hours of data loading time and cost $144.74. Uploading the same amount of data over the Internet would cost $204.80. The calculator does not show how long the Internet transfer would take.”

http://www.networkworld.com/news/2010/061010-amazon-cloud-fedex.html

How to install MongoDB on CentOS 5.4 / RHEL5 and interface with PHP 5

2010-05-11T20:53:14Z

If you’ve been reading up on the various NoSQL offerings and have wanted to try out one but don’t know how to get started, this is one of the easiest ways. I chose MongoDB for this example because I’m going to start using it for a project that needs features that MySQL isn’t as fast at: namely denormalized data with billions of rows. MongoDB has plenty of drivers for other scripting and high-level languages but I’ll focus on the PHP driver today. If there is interest I can do a write up on Python usage later. This example is limited to CentOS, Fedora, and Redhat 5 servers that use the yum package management system. For more information you can reference their download page: http://www.mongodb.org/display/DOCS/Downloads

First install the prerequisites:

sudo yum install gcc php php-pear

Then install the mogo php extension via pecl and add the extension to the ini file. (Increase the php.ini memory_limit to 32M if necessary). Then we restart apache to have the extension loaded. You can check your extensions via the phpinfo(); command in php.

sudo pecl install mongo
sudo echo “extension=mongo.so” >> /etc/php.ini
sudo /etc/init.d/httpd restart

For the last part we add the yum repo and get the actual MongoDB server and client installed

cd /etc/yum.repos.d
sudo emacs mongodb.10gen.repo
paste in the following. This uses the repo for 64bit servers. See their download page for other architecture repo settings.

[10gen] name=10gen Repository baseurl=http://downloads.mongodb.org/distros/centos/5.4/os/x86_64/ gpgcheck=0

sudo yum install mongo-stable-server
sudo /etc/init.d/monod start
chkconfig mongod on

That’s it, you’re ready to start using Mongo! You can check the logfile to make sure things are running smoothly: “sudo cat /var/log/mongo/mongod.log”

How to get colored output from ‘ls’ on Solaris10

2010-04-27T01:04:17Z

For all of those linux users out there that have moved over to, or tried out, Solaris10 or OpenSolaris because they heard the tales of how MySQL is faster on Solaris… or perhaps you wanted to learn how to use Sol10 for the great features of Zones or the ZFS filesystem? Regardless of why you’re on it you are probably wondering why Linux has colored output of filenames and directories but Solaris does not. The question of ‘why?’ isn’t important, but how to enable colors is. It’s very simple, and here’s how I fixed it. This is a result of digging through multiple semi-related links on Google.

Download all packages from SunFreeware.com
- dependency: libintl-3.4.0-sol10-x86-local
- dependency: libiconv-1.13.1-sol10-x86-local
- dependency: gmp-4.2.1-sol10-x86-local
- dependency: gcc-3.4.6-sol10-x86-local or libgcc-3.4.6-sol10-x86-local depending on your system needs
- coreutils-8.4-sol10-x86-local
Install ‘coreutils’ dependency packages using the command “pkgadd -d [package_name]
Install ‘coreutils’ packages using the command “pkgadd -d coreutils-8.4-sol10-x86-local
Enable color aliases in your rc file: “alias ls=’/usr/local/bin/ls –color=auto’”