This blog post shows how to create a cursor class for MySQL Connector/Python which will allow you to cache queries. It will hold the query itself and the result in a global variable.

Note: this is a proof of concept and is only meant as a demonstration on how to extend MySQL Connector/Python.

Why query caching?

You are doing lots of queries that have the same result. It would be expensive to always run the same exact query. MySQL has already a query cache, and there is also memcached. But you like MySQL Connector/Python so much you’d like to do it yourself.

A cursor caching queries and their result

To demonstrate a simple implementation of a query cache, we inherit …

MySQL Connector/Python doesn’t buffer results by default. This means you have to fetch the rows when you issued a SELECT. This post describes how you can change this behavior.

Why buffering result sets?

Buffering or storing the result set on the client side is handy when you, for example, would like to use multiple cursors per connection and you’de like to traverse each one interleaved.

Keep in mind that with bigger result sets, the client side will use more memory. You just need to find out for yourself what’s best. When you know result sets are mostly small, you might opt to buffer.

MySQLdb by default buffers results and you need to use a different cursor to disable it. oursql does not buffer by default. This is good to …

Lately I’ve had to do some environment load testing so I wrote this quick script. It can be modified as needed but the basic idea is that it spawns $x threads (–threads) and then sends two connections (or however many you want with –per-connection=) per thread to the URL (–url=). You can have it wait a configurable time between connections as well (–wait=).

The url is appended with a 32 character randomized string so that any database/caching on the backend of the site isn’t serving data from a warm cache. You can hunt down the string length for 32 and change it to whatever you want. Feel free to change and use as needed, just keep my info at top.

#!/usr/bin/python
################################################################################
## DATE: 2010-10-26
## AUTHOR: Matt Reid
## MAIL: mreid@kontrollsoft.com
## SITE: http://kontrollsoft.com
## LICENSE: BSD http://www.opensource.org/licenses/bsd-license.php …

This post describes how to make a custom cursor returning rows as dictionaries using MySQL Connctor/Python v0.2 (or later).

Problem: you want to fetch rows from the database and return them as a dictionary with keys being the column names.

First, lets check how you would do it without any custom cursor.

cnx = mysql.connector.connect(host='localhost',database='test')
cur = cnx.cursor()
cur.execute("SELECT c1, c2 FROM t1")
result = []
columns = tuple( [d[0].decode('utf8') for d in cur.description] )
for row in cur:
  result.append(dict(zip(columns, row)))
pprint(result)
cur.close()
cnx.close()
[python]

The above results in an output like this:

[python light="true"]
[{u'c1': datetime.datetime(2010, 10, 13, 8, 55, 35), u'c2': u'ham'},
 {u'c1': datetime.datetime(2010, 10, …

Next development release v0.2.0 of MySQL Connector/Python is available for download and testing. We still don’t recommend to use it in production: it is not beta or GA yet, but we are getting there.

Bug reports and feature requests are welcome through the Launchpad bug tracking tool.

Highlights:

• .executemany() now optimizes INSERT statements using the MySQL
  multiple row syntax.
• Setting sql_mode and time_zone when connecting as well as collation.
• Raw Cursors can be used when you want to do the conversion yourself.
• Unittests now bootstrap own MySQL server instance.
• Tidying the source tree.

Full list of …

As an update to my prior post, I’ve added a form to the workbench plugin.  Now, the user can select a slow query log file and generate statistics from it.  The plugin provides information to answer the following questions:

Figure 1. Sample plugin form

• What type of queries run most often?
• What type of queries are the slowest?
• Which queries access the most rows?
• Which queries send the most data?

The plugin scans the slow query log, aggregates similar queries, and provides summary statistics for each group.  It’s very similar to the mysqldumpslow perl utility, which is included in the mysql bin folder. …

There’s a new version of the dbbenchmark tool available. Now we’re creating the MySQL connection pool thread count based on automatic reporting of core quantity. There is still the same method to set the thread count manually if you are interested in finding your system’s thread limits. Download the MySQL benchmarking script now and add your server performance to the community database of results!

Lately I worked quite a bit with Python and Linux, writing monitoring and automation utilities. I am in a transition period, so I thought I ought to write some Python stuff interfacing with MySQL for fun, and start positioning myself for expanded career horizon, I hope.

To get started, I thought it would be fun to rewrite a Perl utility I wrote before with Python. That script converts MySQL character sets to utf8, a very common task for wikis and blogs during an upgrade. This time, I did everything from scratch: firing up an Amazon EC2 Linux instance, hand install and configuring MySQL 5.1.50 (creating mysql user, group, wget tarball, setting directory ownership and permissions, creating symbolic to MySQL binaries, editing my.cnf, /etc/init.d/ and chkconfig automatic startup, environmental variables, the works), compiled and …

This is my first attempt at creating a plugin for MySQL Workbench.  As a first step, I’ve created a plugin that summarizes the slow query log if it’s output to the slow_log table, which is an option available in MySQL version 5.1 or newer.  It’s similar to the mysqldumpslow perl script, except that it doesn’t require perl, which should be more convenient on Windows.  In my next update, the plugin will provide the same summary statistics for the slow query log file on disk.

While the slow query log reports query time, lock time, rows sent and rows examined for each query; it’s often useful to group and aggregate similar queries for analysis.  For example, here’s a sample of the plugin output, which is sorted by count, after just a few clicks on a drupal6 site:

The plugin …

We continue to see more evidence of the themes we discuss in our latest CAOS special report, Seeding the Clouds, which examines the open source software used in cloud computing, the vendors backing open source, the cloud providers using it and the impact on the industry.

First, as usual, we are seeing consistencies between our own research — which indicates open source is a huge part of today’s cloud computing offerings from major providers like Amazon, Google, Rackspace, Terremark and VMware — and that of code analysis and management vendor Black Duck. In its analysis of code that runs the cloud, Black Duck also found a preponderance of open source pieces, in many cases the same projects we profile in our report.

Indeed, open source software is an important part of the infrastructure, …