Unlike MyISAM where data for individual tables is stored in their respective files, InnoDB stores data in a tablespace. By default, there is one single tablespace and data of all the databases is stored in one file. This file has data dictionary, tables, as well as indexes in it. There is a global parameter innodb_data_file_path that defines this tablespace file. It has a syntax like ibdata1:256M:autoextend, this means at the beginning a file of size 256 MB will be created and then whenever the data size exceeds this, the file will be auto-extended. The innodb_autoextend_increment variable defines in MB's that by how much each increment should be.

Let's see how well can we play around:

1. Inserts: Suppose you have too many inserts and InnoDB is extending the file too frequently. It makes sense to increase the value of innodb_autoextend_increment. Say we increase it …

I'll be giving a 1 hour session at ZendCon this year entitled "MySQL Performance Coding - From Soup to Nuts". The abstract says 3 hours, but I believe it's a one hour slot... Either way, it'll be fun and rowdy. The PHP crowd always is a fun crowd!

Yesterday and today I gave a couple webinars on basic performance tuning of SQL queries and schema/index strategies. I've given this presentation quite a few times now, and a number of good questions always seem to come up. I figured I would summarize them and their answers in a blog post.

Q: Which will be faster out of these two queries?

SELECT ... WHERE some_col IN (1,2,3) or
SELECT ... WHERE some_col = 1 OR some_col = 2 OR some_col = 3

A:

Neither. The optimizer rewrites an IN() operator to a series of OR conditions, so there will be no performance diffference. Use IN() as it makes the code shorter and more readable.

Q: Where does MyISAM cache table records? A:

Nowhere. MyISAM does not cache table records like InnoDB does in it's innodb_buffer_pool. Instead, MyISAM relies on the operating system buffering to buffer table records as it reads …

Optaros, the company I am working for, released an open source catalog which lists close to 300 so called "enterprise ready" OSS applications. I guess most of you reading my blog are not exactly in the target group as to me it mostly seems to be directed about people that lack deep networking into the various OSS communities. That being said I still think that it might be worth a look, even if its just to blog about how we totally got the rating on a specific piece of software totally wrong.

In this updated and web enabled version end users can vote on projects, which is already a first step in the direction of allowing user contributions. I hope we will figure out ways on how to expand this. Again the target group are decision makers that are not so in tune with OSS communities, so we are ok with providing a little less information (if they want all the information they can always use …

Direct play this episode at:
http://tinyurl.com/2quk66

Feedback:

Email podcast@technocation.org

call the comment line at +1 617-674-2369

use Odeo to leave a voice mail through your computer:
http://odeo.com/sendmeamessage/Sheeri

Or use the Technocation forums:
http://tinyurl.com/sc6qw

Episode 18: De-Myth-tifying Indexes

Where I have been:
Wedding video (short) and photos:
http://www.sheeri.com/wedding

Honeymoon (and wedding) photos:
http://www.sheeri.com/photos

News:
FREE …

Last year (almost to the day), I wrote a post that detailed how JBoss went from $0 to a $350 million acquisition by Red Hat and scored a range of paying customers along the way. The research for that post was actually done in preparation for an OSCON presentation I was to deliver, which is the same impetus for this post.

One year later, my analysis of JBoss has proved to be remarkably accurate (at least for Alfresco). However, I was a little off on my timing (see the slide at right), and I didn't give enough credit to the power of open source to drive sales.

One year later, I'd add the following observations to my original analysis:

• …

IBM pledges patents for software interoperability. Digium acquires Sokol & Associates. OpenLogic releases open source software discovery tool to the public. (and more)

IBM Pledges Free Access to Patents Involved in Implementing 150+ Software Standards, IBM (Press Release)

Digium Acquires Key Asterisk Open Source Partner Sokol & Associates, Digium (Press Release)

OpenLogic Announces General Availability of Free Open Source Software Discovery Tool, OpenLogic (Press Release)

Emulex Virtual HBA Technology Accepted into Open Source Linux Kernel, Emulex (Press Release) …

Damien Seguy had an interesting observation on my use of self joins for generating rolling sums.

A sysadmin friend of mine was describing some DBA work he was doing, and wrote this:

I’m not much of a DBA, really - if it can’t be done through phpMyAdmin I’m not likely to be doing it.

This is in stark contrast to so many so-called DBAs who say “I’m a DBA….If it can’t be done through phpMyAdmin it must be Senior DBA work.”

I’ve used phpMyAdmin for MySQL administration, and there’s just something so nice about working on commandline.

Lately, I have had opportunity to evaluate a very large Ruby installation that also was growing very quickly. A lot of the work performed on site has been specific to the site, but other observations are true for the platform no matter what is being done on it. This article is about Ruby On Rails and its interaction with MySQL in general.

Continue reading "Rubyisms"