Pythian Group

(Picture from Yanick Champoux)

Tuesday, May 26, the Ottawa Perl Mongers will be meeting once again at the Pythian headquarters. There will be two presentations given:

• Yanick Chanpoux will be presenting on Moose.
• Tim Procter (myself) will be presenting on Portable Perl Daemons. The focus is on developing light-weight background processes that will be compatible with both Unix and Windows systems. Additionally, I will be demonstrating communications with the daemon and child processes over TCP from an independent controller.

The presentations are now available online thanks to Andrew Ross at the Free and Open Source Software Learning Centre

• Portable Perl Daemons
• Moose

Comments are welcome.

Here is the perl script referred to by mysql-memory-usage-profile.

Downloadable: mysql_memory_profile.pl

Read the rest of this entry . . .

There are various tools available for gathering and monitoring mysql performance data, but it’s not always clear what they mean, what is important, and what can be tuned. I’m not going to try to post a comprehensive mysql tuning manual here. This post is about taking a snapshot profile of a mysql instance that will allow you to define what type of instance you are dealing with, and give you some idea about whether the current configurations are stable or not. Read the rest of this entry . . .

Finding duplicates with RANK
If you’ve made the mistake of forgetting a primary key on your table, it can be frustrating to find a way to delete all of the duplicate rows without deleting the initial instances.

delete from $table_name where rowid in
  (
  select "rowid" from
     (select "rowid", rank_n from
         (select rank() over (partition by $primary_key order by rowid) rank_n, rowid as "rowid"
             from $table_name
             where $primary_key in
                (select $primary_key from $table_name
                  group by $all_columns
                  having count(*) > 1
                )
             )
         )
     where rank_n > 1
  )

This query selects all of the ‘extra’ rowids and removes them. It is especially designed for limiting the query scans to only those records which have duplicates, which is useful if there’s only a subset of the table that you are dealing with. If you want to improve its efficiency for a table with a high percentage of duplicates, simply remove the inside where clause. In a sample test with 1% duplicates and 233 000 rows, the query took 22 seconds without the subquery, and 18 seconds with it.

Read the rest of this entry . . .

Pythian News is now using WordPress to transform our Industry News section into a group blog of articles relevant to the DBA community at large. We hope this will allow readers from throughout the DBA industry to get involved and not only view our blogs, but also be able to contribute to the discussions.

For convenience, archived old Industry News articles have been added to the blog below as authored by Paul Vallee, our President. Future articles will follow the blog writing philosophy more closely!