Rows and Columns

I would like to invite everyone to be involved in the beta testing of our
brand new graphical monitoring tool. The SQL Anywhere Monitor is a
browser-based administration tool that provides you with information about
the health and availability of SQL Anywhere databases and MobiLink servers.
The SQL Anywhere Monitor collects metrics and performance data from
databases and MobiLink servers running on other computers, while a separate
computer accesses the SQL Anywhere Monitor via a web browser.

More information about the SQL Anywhere Monitor is available here, and the software can be downloaded here.

Sybase Techwave is happening this week in Las Vegas. SQL Anywhere has made 3 major announcements:

Sybase iAnywhere Announces Availability of SQL Anywhere 11
Version 11 has lots of cool new features. Check out the Top 10 Cool New Features in SQL Anywhere 11

Next we have Sybase iAnywhere Targets Web Developer Community with New Version of SQL Anywhere
The SQL Anywhere Web edition is a free version of SQL Anywhere for development and deployment of web applications.

and finally:
Sybase iAnywhere Announces First TPC-C Benchmark Using SQL Anywhere Server
I was heavily involved in getting this benchmark completed. Though my opinion is that generic benchmarks are of questionable use, it is a measuring stick that many technology evaluators use to both compare competitors and to gauge capabilities of a database server. Now we can point people to the benchmark (viewable here) as another piece of proof that SQL Anywhere provides excellent performance at affordable cost.

We recently published our new DocComment Xchangewebsite, built on SQL Anywhere.

DocCommentXchange is a community site for viewing and discussing SQL Anywhere documentation.

You can Use DocCommentXchange to:

• View documentation
• Check for clarifications users have made to sections of documentation.
• Add your own comments and examples to clarify the documentation
• Improve documentation for all users in future releases.

We are not currently moderating any of the comments, but you must be logged in to the Sybase Developer Network in order to add comments (registration is free).

The SQL Anywhere documentation team monitors all comments left by contributors, and will be using them to improve future versions of our documentation.

I have not had a lot of time to post lately. Mostly due to
1) Getting everything done for the release of SQL Anywhere v 11.
2) My upcoming wedding at the end of August.

Regular posts should begin again soon…

SQL Anywhere ships a sample called instest (found in the Samples\SQLAnywhere\PerformanceInsert directory of your SQL Anywhere install) which allows you to experiment on insert performance for SQL Anywhere. However, it is important to think things through before picking requirements out of the air.

In speaking with potential customers, they often ask us whether we can support an insertion rate of X for data.
The answer to this question is not straightforward, since it depends on a variety of things

• How many concurrent users are there?
• How many concurrent inserts into the same table?
• How many users reading data from the table at the same time?
• How large are the rows?
• How many columns?
• What kind of hardware (CPU, hard drive speed/size, OS, etc…)?

Once faced with these questions, the customer oftens responds with what they believe to be a number of inserts/amount of data they will get if their product is successful. This is where the trouble comes in. These numbers are often way out in left field, and some simple math demonstrates this. I have had variations on the following conversation many times, with both large and small companies looking at using SQL Anywhere for a project.

Prospect: How fast can SQL Anywhere insert data?
Me: (asks all the questions above)
Prospect: Well we’re not sure about most of that stuff yet, but we can assume the rows will be 100 bytes, and we want to insert 20000 rows per second 24×7. We will be running on fairly recent hardware. Can the server do this?
Me: Depending on hardware and the other questions I asked, the server can do this, but you realize that at that rate, you are inserting about 2M of data every second?
Prospect: Yes, so what?
Me: Are you planning to keep all this data?
Prospect: Yes, of course. We will need it for reporting.
Me: Well, if you are running 24×7, you will be inserting 2.8G of data every day. After a month, you are going to have 85G of data, which will probably mean a database about 100G in size if you are also maintaining any indices. After a year, you are going to have a database > 1TB. Will your box have enough storage for this? How are you going to do backups?
Prospect: Oh, well, uh… I need to discuss that with my team and get back to you.

There is nothing wrong with needing to know that the database server will support your maximum throughput requirements for insert. However, asking those questions with no frame of reference and without thinking your decisions through is not productive. Placing requirements on the database server that you will never be able to reach because your hardware and application architecture won’t support can needlessly limit your choices.

I’ve been playing with SQL Anywhere’s support for web services. What is particularly cool is that you can call out to (or consume) a web service right from the database server using a stored procedure. Since I have been playing with blogs, and setting up rss, I wondered if I could consume RSS feeds via the database server. It turns out this is very easy. The following functon pulls the rss feed from digg.com:

CREATE FUNCTION DBA.getdiggrss()
returns long varchar 
url 'http://www.digg.com/rss/index.xml'

Once that is done, I can retrieve the rss as follows:

SELECT DBA.getdiggrss() FROM DUMMY;

Easy… However, XML is not necessarily the most readble output. It would be nice if I could shred it into its component pieces and view the relevant ones. This requires that I look at the XML and determine the tags I wish to look at. Once I identify them, I can use the OpenXML(…) function to build a resultset that is much more readable:

SELECT * 
FROM OPENXML( DBA.getdiggrss(), '/rss/channel/item', 1, 
	'<root xmlns:content="http://purl.org/rss/1.0/modules/content/"
	xmlns:wfw="http://wellformedweb.org/CommentAPI/"	
	xmlns:dc="http://purl.org/dc/elements/1.1/"
	xmlns:digg="http://digg.com/docs/diggrss/"/>' ) 
WITH( title long varchar 'title', 
	link long varchar 'link', 
	postdescription long varchar 'description', 
	diggcount integer './/digg:diggCount', 
	commentcount integer './/digg:commentCount', 
	pubdate long varchar 'pubDate', 
	permalink long varchar 'guid')

Basically, I pull all the namespaces from the XML file so OPENXML() knows how to process it. Then I tell OPENXML that I want to examine all of the “item” elements (which equate to particular RSS feed entries). I use the WITH clause to define which fields of the “item” elements I want returned in my resultset, what their column names should be and what their datatypes should be.

Not too difficult, and potentially very powerful. I think next I will create an event now that will periodically pull down the RSS and shred it and then store the information in a table, which I can then create a full text index on so I can do local searching of the blog feed. In fact, if I add several feeds, I will be able to search across them with a single query.

Well, I am in the middle of updating the blog to use the “Bad Behaviour” plugin to see if I can cut down on the amount of spam comments I am getting.
Fortunately the work I did to get the blog running on SQL Anywhere in a generic way allowed me to get this plugin up and running with a minimal amount of work. Specifically, the rewrite function I created to replace MySQL Syntax with SQL Anywhere syntax worked great with the Bad Behaviour plugin. The only thing I had to add was some re-write functionality for the CREATE TABLE statement.

If you haven’t seen this site, you should check it out. TED.com is a site containing presentations and information from the TED conferences (Technology, Entertainment, Design). The top representatives from a variety of fields present short (usually less than 20 minutes) talks about their area of expertise. There is a lot of good stuff there. Every talk I have listened to has been excellent, but a couple of my favorites are Why People Believe Strange Things by Michael Shermer and 18 minutes With an Agile Mind by Clifford Stoll.
These talks aren’t specifically database related, but understanding the world and the way people think is important when thinking about how to design anything, including database software and database applications.

In my previous article, I explained how I downloaded an XML dump of the wikipedia articles and built a SQL Anywhere database with the data.
In order to test version 11, I created a full text index on the title and article data and populated the index:

CREATE TEXT INDEX "wikidex" ON "DBA"."wikipedia"
    ( "title","details" ) CONFIGURATION "SYS"."default_char" IMMEDIATE REFRESH

It took about 4 hours to build the index. Of note, the SQL Anywhere server temporary file grew to about 35G in size during the refresh process for the text index. This space is required in order to sort and generate the index contents. The database itself increased in size by about 4G after the index was created.

Once finished, I could start running queries. I ran some simple queries at first, like the following:

SELECT title, details FROM wikipedia WHERE contains(  details, "Canada" );

These queries were pretty fast (< 1sec) to return reults. As a comparison, a similar query using like to find the same results took several minutes to return (basically every row had to be scanned sequentially for the text):

SELECT title, details FROM wikipedia WHERE details LIKE "%Canada%";

One interesting thing to note in the result sets is that by changing the query slightly, you can get score data - basically this measures how closely the row matched the query string. I always sort on the column so I can see the most closely matched rows first:

SELECT ct.score, title, details FROM wikipedia contains(  details, "Canada" ) ct ORDER BY ct.score DESC;

At the end of my last post regarding blog creation, I had gotten to the point of creating the Wordpress blog based on SQL Anywhere. Unfortunately, the admin. console didn’t work and I couldn’t post or view my new blog.

The problem was the MySQL-specific syntax used in the Wordpress code. Fortunately, SQL Anywhere has equivalent functionality, but uses different syntax (we more closely match Microsoft SQL Server syntax than MySQL syntax).

I could have just gone through the Wordpress source and fixed all the queries to run against SQL Anywhere, but I wanted to be able to share my Wordpress/SQL Anywhere blog with people who were interested, and I did not want to package the entire Wordpress install (not to mention updating it everytime Wordpress is updated). I just wanted to supply a plugin type of module so a user could just drop a file (or set of files) into an existing Wordpress install, use the SQL Anywhere migration wizard to move their data/schema from MySQL to SQL Anywhere and continue blogging.

I needed to come up with a way to fix all of the queries in Wordpress so they would run against SQL Anywhere, but without modifying any Wordpress source. I noticed that all queries in Wordpress are funnelled through the query() function in my db.php file. This function has an ‘apply_filters’ hook that I could have used, but this would not work because (as stated in the code comments) there are queries executed before the apply_filters() function is called. This meant I would have to write my own function.

Enter search and replace and regular expressions. I was not looking forward to this since it always took me a long time to get regular expressions right when we covered them in university. However, it actually turned out to be straightforward (though time-consuming) to build a search and replace function to turn the MySQL syntax into SQL Anywhere syntax. I am the first to admit it isn’t perfect, but all of the features and functionality of the blog work, so it is good enough for now.

My approach was to look at each type of query, determine the SQL Anywhere equivalent, and then build a search and replace function to fix the query syntax. The first one I did was the LIMIT clause. This is used all over the place in Wordpress. SQL Anywhere has equivalent behaviour using the TOP N clause:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

//Find LIMIT clause integer arguments and replace with TOP N START AT m
//MySQL syntax for LIMIT is:
//  LIMIT {[offset,] row_count | row_count OFFSET offset}]
//Note that the LIMIT clause is at the end of the query
 
// pattern is LIMIT followed by an integer, possibly followed by a comma and another integer
$pattern = "/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*)/i";  
$matched = preg_match( $pattern, $query, $limitmatches );
if( $matched == 1 ) {
    //Remove the LIMIT statement, replace offset 0 with 1
    $query = preg_replace( $pattern, '', $query);
    if( count( $limitmatches ) == 5 ) {
        if( $limitmatches[1] == '0' ) {
            $limitmatches[1] = '1';
        }
        $replacement = 'SELECT TOP '.$limitmatches[4].' START AT '.$limitmatches[1].' ';
    } else {
        $replacement = 'SELECT TOP '.$limitmatches[1].' ';
    }	
    $query = preg_replace( '/^\s*SELECT\s*/i', $replacement, $query );
}

Once I completed all of my search and replace code (this took a couple of days), I bundled them together into a function (which I placed in db.php) called rewrite_query (click through for the complete source).

I then added a call to the rewrite_query function at the beginning of the query() function in db.php.

At this point, the blog was fully functional. I was able to create a new blog, and run all of the management functions. Woo hoo!

I only had one final problem, which I didn’t notice until I had several articles. The query used to determine whether the blog homepage had next/previous links depended on the MySQL FOUND_ROWS function. SQL Anywhere does not have this function, but luckily Wordpress provides a filter hook called ‘found_posts_query’. I simply added a hook and
created a query to return the count of the number of posts:

function set_query_post_count() {
    global $wpdb;
 
    $query = "SELECT COUNT(*) FROM ".$wpdb->posts." WHERE post_status = 'publish'";
    return $query;
}

That was it… In total, except for the last fix (which I didn’t notice for a while because of its nature), I was able to get this work done in just a few days. I assume someone familiar with PHP and/or Wordpress and/or regular expressions could probably have done it faster, but I was quite happy with the way things went. And now here we are - hopefully everything on this blog will continue to work as I expect.