PlanetMysql.ru – информация о СУБД MySQL

Here are some quick commands for installing the proper packages and requirements for the MySQL dbbenchmark program.

export PKG_PATH="ftp://openbsd.mirrors.tds.net/pub/OpenBSD/4.7/packages/amd64/"
pkg_add -i -v wget
wget http://dbbenchmark.googlecode.com/files/dbbenchmark-version-0.1.beta_rev26.tar.gz
pkg_add -i -v python
Ambiguous: choose package for python
 a       0:
         1: python-2.4.6p2
         2: python-2.5.4p3
         3: python-2.6.3p1
Your choice: 2

pkg_add -i -v py-mysql
pkg_add -i -v mysql
pkg_add -i -v mysql-server
ln -s /usr/local/bin/python2.5 /usr/bin/python
gzip -d dbbenchmark-version-0.1.beta_rev26.tar.gz
tar -xvf dbbenchmark-version-0.1.beta_rev26.tar
cd dbbenchmark-version-0.1.beta_rev26
./dbbenchmark.py --print-sql
 - login to mysql and execute sql commands
./dbbenchmark.py

PlanetMySQL Voting: Vote UP / Vote DOWN

Quick solution to an issue that the affected Debian Lenny release: the process used to collect the MEMORY_ACTIVE_BYTES variable has been modified to correct a situation where some systems report an array of memory information instead of the expected single integer value. The bug has been fixed in revision 21 and the current download (revision 22) is available for download or svn update. As usual, you can download the MySQL dbbenchmark script here: [downloads].

Thanks goes to Brian Vowell at Evernote.com for bringing this bug to my attention. The original bug report can be found here: [link]

PlanetMySQL Voting: Vote UP / Vote DOWN

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()

PlanetMySQL Voting: Vote UP / Vote DOWN

We had a very successful weekend of Planet.mysql users submitting their database statistics so I’ve pushed some code into a new release today so that everyone can benefit from some new features. The biggest change is to the threading logic. Previously the benchmarking script was serializing MySQL operations and only making use of a secondary thread (not the invoking thread) to query the database. Now you have the option of running with “–threads=x” to make use of your multi-core server. A good example of this improvement was on my Macbook Pro; before the threading change it was inserting ~700/sec, after the code change I tried –threads=4 and saw an improvement to ~900/sec. Rather significant.

Download the new script now and see how your server compares to the ones in the central database!

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Connector/Python is (or should be) compliant with the Python DB-API 2.0 specification. This means that you can use DBUtils' PooledDB module to implement database connection pooling.

Here below you'll find an example which will output the connection ID of each connection requested through the pooling mechanism.

from DBUtils.PooledDB import PooledDB
import mysql.connector

def main():
    pool_size = 3
    pool = PooledDB(mysql.connector, pool_size,
        database='test', user='root', host='127.0.0.1')
    
    cnx = [None,] * pool_size
    for i in xrange(0,pool_size):
        cnx[i] = pool.connection()
        cur = cnx[i].cursor()
        cur.execute("SELECT CONNECTION_ID()")
        print "Cnx %d has ID %d" % (i+1,cur.fetchone()[0])
        cur.close()
    
    for c in cnx:
        c.close()

The output will be something like this:

Cnx 1 has ID 42
Cnx 2 has ID 41
Cnx 3 has ID 40

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQLdb is a Python wrapper around _mysql written by Andy Dustman. This wrapper makes it possible to interact with a MySQL Server performing all sorts of DDL and DML statements. I began my Python journey recently and stumbled at the installation of the MySQLdb module install. I was keen not to jump at an apt/yum installation as we have servers that have no outbound connections I decided I needed to build the modules from source.

You can download the MySQLdb files from SourceForge (70kb)

When downloaded you need to prep before your system is ready to build the file. Here are some prerequisites that will make life easier for you. I performed this particular install using an Ubuntu 10.04 64bit OS.

Before you start ensure you have the following installed (MySQL isn't actually required but for local Python development it's nice to have a database server to develop against!)

• MySQL Server. I used the MySQL Community Server Version 5.1.49
• gcc - The GNU Compiler Collections

With this installed I decided I was ready to build again but again another splurge of error code and this time my system was complaining about 'mysql_config' (you might not incur this issue after the previous apt-get installs but I'm including it anyway just in case you see this message.


I updated my PATH environment variable and I was ready to try again...


You should be ready (properly ready this time!) to build and install your MySQLdb wrapper


Now open your python command line and import the new MySQLdb module


Success!
PlanetMySQL Voting: Vote UP / Vote DOWN

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). Here’s the link: http://codepad.org/8kjOBJdj

PlanetMySQL Voting: Vote UP / Vote DOWN

Following up on Matt Reid’s simple python, mysql connection and iteration, I would like to share one of my own, which is the base for mycheckpoint & openark kit scripts.

It is oriented to provide with clean access to the data: the user is not expected to handle cursors and connections. Result sets are returned as python lists and dictionaries. It is also config file aware and comes with built in command line options.

I hope it comes to use: my.py

PlanetMySQL Voting: Vote UP / Vote DOWN

Following up on Matt Reid’s simple python, mysql connection and iteration, I would like to share one of my own, which is the base for mycheckpoint & openark kit scripts.

It is oriented to provide with clean access to the data: the user is not expected to handle cursors and connections. Result sets are returned as python lists and dictionaries. It is also config file aware and comes with built in command line options.

I hope it comes to use: my.py

PlanetMySQL Voting: Vote UP / Vote DOWN

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. Click here for the script: http://pastebin.com/H2NTNt3h

PlanetMySQL Voting: Vote UP / Vote DOWN