In previous articles, we examined the maximum performance of basic web static and dynamic (php) requests. In this article, we will take a look at what happens when a database access is added to a simple php page. The application code is part of the corelamp workload (see webapp/hellomysql.php).

In this application, I added a single database row request to the TABLES table in the …

I’d like an UNTIL clause, please. I’d use it sort of like LIMIT in MySQL and PostgreSQL, except that it would define when to stop returning looking for rows, instead of defining how many to return. Example:

SELECT * FROM users ORDER BY user_id UNTIL user_id >= 100;

That would select users up to and including user 99. Ideally the clause could accept any boolean predicate, including subqueries. I’ll hold my breath and wait for this wish to come true now.

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.

[brian@gaz fix_is]$ ./drizzled/drizzled --console-enable 
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins.
100122 13:12:00  InnoDB: highest supported file format is Barracuda.
100122 13:12:00 InnoDB Plugin 1.0.4 started; log sequence number 44600
Listening on 0.0.0.0:3306
Listening on :::3306
Listening on 0.0.0.0:4427
Listening on :::4427
./drizzled/drizzled: Forcing close of thread 0 user: ''
./drizzled/drizzled: ready for connections.
Version: '2010.01.1273' Source distribution (fix_is)

drizzled> use data_dictionary;
OK

drizzled> show tables;
Tables_in_data_dictionary       
MODULES 
PLUGINS 
PROCESSLIST     

drizzled> desc PLUGINS;
Field   Type    Null    Key     Default Extra   

PATH ./data_dictionary/plugins
ID      bigint  YES             NULL            
USER    varchar(16)     YES             NULL            
HOST    varchar(64)     YES             NULL            
DB      varchar(64)     YES             NULL …

February and March as busy months for Community events.  There's MySQL University, Fosdem, the Seattle MySQL Meetup & Confoo.ca. Here are the details:

• February 4th - I'll be presenting a talk via MySQL University on Optimizing Queries with Explain.  This talk will be about learning to read the output from MySQL's EXPLAIN command, and optimizing some example queries from the IMDB dataset.  At 5.7G in InnoDB tables imported before any secondary key indexes IMDB is one of my new favorite example databases.  It's about the right size on most desktop PCs that you can measure the difference between a query that's optimized and one that's not.  All material comes courtesy of a new …

Back when the EU started the investigation of the Oracle-Sun deal, I made a bet. The bet hinged on whether Oracle would make concessions to get the EU’s approval. Please review the arguments, pro and con, and help us settle the bet.

Issue #1: The 10-Point Commitment to Customers Developers & Users of MySQL:
PRO CONCESSIONS: After meeting with the EU, Oracle issues this list of 10 concessions. Oracle prefaces the 10 points with the line: “In order further to reassure the Commission, Oracle hereby publicly commits to the following:” It then goes on to make certain commitments including #2 the non-assertion policy where is says “Oracle will change Sun’s current policy” and commit not to assert their copyright against storage engine vendors for 5 years. And continues to say: “Oracle shall reproduce this commitment in contractual commitments …

If you're wondering what MySQL 5.1 with the new InnoDB Plugin is all about you'll want to tune into the webinar I am hosting next Tuesday. I'll cover the why, what and how behind the immediate performance and scale improvements that can be had, especially on modern (> 4 CPU) servers, by enabling the new plugin in MySQL 5.1. Learn more and get registered, it should be good time!

You can also take it to a deeper level by joining Brian Miezejewski from our PS team on 3/9 for a practical guide to using and tuning the new plugin features to improve performance and scale. Hope you can join us!

Topics for this podcast:

*Open source in consumer devices
*VMware-Zimbra deal highlights open source, cloud
*A capitalist’s guide to open source licensing
*Latest on Oracle-Sun-MySQL, M&A implications

iTunes or direct download (24:48, 5.7 MB)

A new version of Kontrollbase – the enterprise monitoring, analytics, reporting, and historical analysis webapp for MySQL database administrators and advanced users of MySQL databases – is available for download. See the downloads page or run “svn update” to get your new version today. http://kontrollsoft.com/software-downloads

If you have seen the following error on the Perf Report tab “Message: SimpleXMLElement::__construct()…” – it has been fixed in revision 281. This only affects alerts 11 and 12 so you might not run into it immediately. The solution is to either remove lines “586, 590, 639, 650″ from the bin/kontroll-reporter-5.0.x_linux-x86-2.0.1.pl file, or to run [...]

When incrementing a table that manages a count are you an optimist or a pessimist? If the row is likely to exist, you should first try to update it and do an insert when the update fails. Otherwise, use insert ... on duplicate key udpate.

It is more expensive to use insert ... on duplicate key update when update is sufficient. That is fair given that insert ... on duplicate key update must do more work (first try the insert, then try the update). You can read the code for this in sql/sql_insert.cc. It is in the functions mysql_insert and write_record.

I ran a simple test using MySQL 5.0.84, InnoDB and an 8-core server. The test measures the time to run the statements above using 1, 2, 4, 8, 16 and 32 concurrent sessions. Each session ran the statement 131,072 times. These statements were tested: