It’s a busy summer at Pythian, with our continuing wave of speaking sessions at upcoming community and regional industry events.

Coming to a city near you, watch for Pythian presenting hot Oracle and Microsoft SQL Server database topics:

IN CANADA:

Oracle Technology Days – Montreal
August 9, 2011 – 8:30am – 1pm, Hilton Montreal Bonaventure

• 11:00 am – Join Marc Fielding for Mixed Workload Management for Oracle Exadata
• More Pythian Oracle Exadata resources
• View the Evite
• Register/Inscrire

Oracle Technology Days – Toronto
August 25, …

There are many ways of improving response times for users. There are some people that spend a lot of time, energy and money on trying to have the application respond as fast as possible at the time when the users made the request.

Those people may miss out on an opportunity to do some or all of the processing the application needs to do at a different point in time. In other words, if you preprocess your data ahead of time, you can reduce the time it takes to complete a request.

Allow me to give you three examples of what I mean:

1)  There is a sales report that your managers would like to see on their fancy new dashboards. The query for this report takes 45 minutes to run and may disrupt other functions that the database server needs to do. You decide to run this report at 3am when there is very little happening on the database server and save the results to a separate table. When the dashboard …

I am happy to announce that the first MariaDB book is released!

The book is called MariaDB Crash Course and is written by Ben Forta, who also wrote the MySQL Crash Course book.

Quoting the book description:

"This book will teach you all you need to know to be immediately productive with MySQL. By working through 30 highly focused hands-on lessons, your MySQL Crash Course will be both easier and more effective than you'd have thought possible"

This is great news for new users to SQL and to MariaDB as it makes it easier for them to get things going quickly!

You can find a link to this book and other recommended MariaDB / MySQL books …

There has been a lot of chatter the past week about Apple replacing MySQL with Postgres in the new OSX Lion Server [U.S. | England | New Zealand ]. Most of it seems to tie things back to Oracle's new stewardship over the MySQL project, a lot of that stemming from what I would say is FUD from the EnterpriseDB folks, regarding doom and gloom about the way Oracle might handle the project in the future. Not that the FUD is entirely unwarrented; While Oracle has done a pretty decent job with MySQL so far, looking at what Oracle has done to projects like Open Solaris certainly would make one queasy. And …

About a month ago I needed to compare tens of thousands of tables in hundreds of databases between a few different servers. The obvious choice was, mk-table-checksum! The only problem was, that the tool needs to know the minimum and maximum value of the column by which each table is to be subdivided into chunks and checksummed. This select min(col), max(col) from table locks all write operations on the table and on a big table it meant downtime.

Looking at the source it was clear we could make mk-table-checksum run the select min(col), max(col) from table on the read-only slave and use the values to checksum the master.

It was subtle code changes in function:
get_range_statistics adding

my $cxn_string_dc = “DBI:mysql:;host=slavehost;port=3306;mysql_read_default_group=client”;
my $user = ‘user’;
my $pass = ‘password’;
my $dbh_slave = DBI->connect($cxn_string_dc, $user, $pass); …

Abstract: In this article we have a look at the compression options of common zipping tools and its impact on the size of the compressed files and the compression time. Further we look at the new parallel zip tools which make use of several cores.

Start with a backup first

From time to time I get into the situation where I have to compress some database files. This happens usually when I have to do some recovery work on customers systems. Our rule number 1 before starting with a recovery is: Do a file system backup first before starting!

This is sometimes difficult to explain to a customer especially if it is a critical system and time runs (and money is lost).

It happens as well that there is not enough space available on the disks (in an ideal world I like to have a bit more than 50% of free space on the disks). Up to now I have used the best compression method. This comes …

I'm posting this here since it has been useful for me, and the blog is a nice place to keep public notes.
If you have servers which have multiple application servers connected to them, you often need to see things like who's connected, how many connections they have, and which users. Using SHOW PROCESSLIST doesn't work that well, since it gives you a row for each server.

What we want is an output similar to this:

+-----------------+-----------------+----------+
| host_short      | users           | count(*) |
+-----------------+-----------------+----------+
| slave1          | repl            |        1 |
| slave2          | repl            |        1 |
| localhost       | event_scheduler |        1 |
| 111.111.222.111 | root, foo       |        2 |
| 111.111.222.222 | appuser, bar    |        3 |
| 111.111.222.333 | appuser, moshe  |        9 | …

About Jelastic:

Jelastic is the next generation of Java Platforms as a Service.

Unlike previous cloud platforms, Jelastic:

• Can run any Java application and so does not require developers to change their code or get locked-into the platform,
• Can scale any application up and down by automatically adding or removing memory and CPU units depending on the application load,
• Takes all configuration and management worries away: developers simply specify the application stack and database options they need and Jelastic creates, configures, and maintains the environment for them
• Supports a wide range of application server stacks including Tomcat, JBoss, Jetty, and GlassFish
• Out of the box, allows users to get a preconfigured instance of MariaDB up and running and available to the application.

A beta version …

Exciting news – MariaDB gets its first book!

Many years ago I read Ben Forta’s MySQL Crash Course . It is a book targeted at beginners of MySQL. Ben has now written another book, titled: MariaDB Crash Course.

Its still targeted at beginners, and covers many of the new features that are available in MariaDB up to version 5.2. I had the pleasure of pre-reading it, and did send in lots of comments to Ben, and if implemented we’ll see some stuff in there that is current even for MariaDB 5.3, like dynamic columns and …

In the two previous posts, we looked at simply connecting to a MySQL database via Python and Perl. In this post, we will:

- use an insert statement to input data into a MySQL table via Perl
- use a select statement to view the same data to confirm our results

For this example, we will use a table named “address”. Here is the SQL statement that we used to create the table:

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
  `name_first` varchar(30) NOT NULL,
  `name_last` varchar(30) NOT NULL,
  `address_01` varchar(40) NOT NULL,
  `address_02` varchar(40) NOT NULL,
  `address_city` varchar(30) NOT NULL,
  `address_state` varchar(20) NOT NULL,
  `address_postal_code` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 1;

We are only going to insert one address line with our data. You can modify this script to loop the process and insert …