Here’s the scenario. You’ve got a table in MySQL for reporting that has a few million rows, and is denormalized for reporting. You’ve got a Pentaho Report that is querying this MySQL table. You have two problems with the current report.

1. Your users are complaining that the query is slow, and they have to wait around for longer than they’d like to see their report. (approx 40s)
2. Your DBAs are cranky because they see the size of this table is getting bigger. (approx 1.8GB)

MySQL is fundamentally designed to be an OLTP database and while it does a fantastic job at that, its data warehouse features were built as “bolt on” additions. Can it be used for BI? Absolutely, I’ve used it a many customer sites. Does DynamoDB provide a better set of features/capabilities for doing BI? We think so! Are they both 100% open source? You bet;why not choose the right tool for the …

This Thursday (November 5th, 10:00 UTC - note the different time for this session!), Arjen Lentz of Open Query will present the brand new GRAPH computation engine for MySQL.

From the abstract: Most of us have had to deal with hierarchies (trees) and graphs (such as friend-of-a-friend style problems), and have thus learn that this is -at best- a bit of a monster to do in an SQL/RDBMS. Until now. The GRAPH engine (GPLv2 licensed) is a MySQL storage engine allowing hierarchies and more complex graph structures to be handled in a relational fashion. In a nutshell, tree structures and friend-of-a-friend style searches can now be done using standard SQL syntax, and results joined onto other tables. http://openquery.com/graph

For MySQL University sessions, point your …

This Thursday (November 5th, 10:00 UTC - note the different time for this session!), Arjen Lentz of Open Query will present the brand new GRAPH computation engine for MySQL.

From the abstract: Most of us have had to deal with hierarchies (trees) and graphs (such as friend-of-a-friend style problems), and have thus learn that this is -at best- a bit of a monster to do in an SQL/RDBMS. Until now. The GRAPH engine (GPLv2 licensed) is a MySQL storage engine allowing hierarchies and more complex graph structures to be handled in a relational fashion. In a nutshell, tree structures and friend-of-a-friend style searches can now be done using standard SQL syntax, and results joined onto other tables. http://openquery.com/graph

For MySQL University sessions, point your …

On Tuesday, November 3rd there will be a webinar on MySQL Cluster Performance Tuning.
Presenting will be Johan A. our resident MySQL Cluster expert, so this will be an excellent time to get some practical tips on tuning MySQL Cluster. I'll be also assisting the panel answering any questions you might have.
 
Register here.

Are you experiencing current performance bottlenecks in your high availability applications ? Are you designing a new mission-critical application and want to know how best to structure your schema and index strategy for optimal performance? Interested in how to transform your SQL into faster, more efficient queries?
Then this free web presentation is for you! You will get expert insight and learn best practices to help you identify those areas of database and application design that will …

Someone recently posted this to an email list as a sample of an interesting SHOW INNODB STATUS output: mysql SHOW ENGINE INNODB STATUS\G _______ _______ |\ /|( ____ \( ____ \ | ) ( || ( \/| ( \/ | | | || (_____ | (__ | | | |(_____ )| __) | | | | ) || ( | (___) |/\____) || (____/\ (_______)\_______)(_______/ _______ _______ _______ _________ _______ _______ _______ _______ ( ____ )( ___ )( ____ \\__ __/( ____ \( ____ )( ____ \( ____ \ | ( )|| ( ) || ( \/ ) ( | ( \/| ( )|| ( \/| ( \/ | (____)|| | | || (_____ | | | | | (____)|| (__ | (_____ | _____)| | | |(_____ ) | | | | ____ | __)| __) (_____ ) | ( | | | | ) | | | | | \_ )| (\ ( | ( ) | | ) | (___) |/\____) | | | | (___) || ) \ \__| (____/\/\____) | |/ (_______)\_______) )_( (_______)|/ \__/(_______/\_______) I thought it was worth trying out, so I gave it a shot:

This entry provides instructions for “silently” installing MySQL on a Microsoft Windows server.  I use the term “silent install” to describe the method of passing parameters to the Microsoft Installation Package (MSI) to bypass installation screens.  This allows you to deploy MySQL with your application and to install MySQL without end-user intervention.  The developer can define the install location, the appropriate storage engines, the root password and the server configuration.

Most application developers use some sort of software for creating an application installer.  Common tools include Install Shield, Wise Installation, InstallBuilder and Windows Installer.  These installation software tools can also be used to install MySQL by launching the MySQL MSI and the MySQL Instance configuration utility ( MySQLInstanceConfig.exe ) from the command-line.

To get started, download the Windows …

As Calpont announced availability of InfiniDB I surely couldn't miss a chance to compare it with previously tested databases in the same environment.
See my previous posts on this topic:
Analyzing air traffic performance with InfoBright and MonetDB
Air traffic queries in LucidDB

I could not run all queries against InfiniDB and I met some hiccups during my experiment, so it was less plain experience than with other databases.

So let's go by the same steps:

Load data

InfiniDB supports MySQL's LOAD DATA statement and it's own colxml / cpimport utilities. As …

I was debugging a server that had too many concurrent queries. From SHOW INNODB STATUS output, there were many long running transactions with uncommitted work and there were many more transactions blocked on InnoDB locks. This is not a good state for a busy OLTP server and I am trying to figure out if the problem is the application or InnoDB.

The state can be detected by reading SHOW INNODB STATUS output to find open transactions with uncommitted changes and other transactions blocked on InnoDB locks. This is an example of a transaction that has been open for 7 seconds (see ACTIVE 7 sec) which also has uncommitted changes (see undo log entries 1).

---TRANSACTION 1 1389611842, ACTIVE 7 sec, process no 23350, OS thread id 1794885952
2 lock struct(s), heap size 368, undo log entries 1
MySQL thread id 188195534, query id 868681019 X.X.X.X foobar

And this is an example of a transaction blocked waiting on a lock. …

Ok, they aren’t like the 2012 nonsense or anything but they are interesting articles that will be important topics in the near future for expanding our knowledge and usefulness in the job market. Being a DBA does not limit you to relational database systems. I expect to see more requests for non-relational or No-SQL type of data stores as they can have many advantages over an RDBMS. Stability, scalability, simplification of administration, higher performance on lesser hardware for larger datasets, and many other items make no-sql engines very interesting. Here are some useful links:

http://en.wikipedia.org/wiki/NoSQL

http://www.linux-mag.com/cache/7579/1.html

http://www.computerworld.com/s/article/9135086/No_to_SQL_Anti_database_movement_gains_steam_

http://www.viget.com/extend/nosql-misconceptions/

How can I pass up talking about the various Cloud offerings again. Here are some interesting news stories …

C and PHP MySQL clients can set a connection timeout before a connection is established to MySQL. The MySQL C API manual states about MYSQL_OPT_CONNECT_TIMEOUT , which is equal to PHPs MYSQLI_OPT_CONNECT_TIMEOUT:

MYSQL_OPT_CONNECT_TIMEOUT

Connect timeout in seconds.

From: http://dev.mysql.com/doc/refman/5.1/en/mysql-options.html

That is half of the story. The actual behaviour depends on the library you use, the operating system and the transport protocol (TCP/IP, Unix domain sockets, Windows named pipes, Shared Memory).

C users can choose between the MySQL Client Library, which ships with the MySQL server, and the …