Check out this SlideShare Presentation: Mysql Explain ExplainedView more presentations from Jeremy Coates.

Check out this SlideShare Presentation: ExplainView more presentations from Ligaya Turmelle.

Check out this SlideShare Presentation: Real World Scalability MySQLView more presentations from techdude.

Lately in the MySQL community, we only hear about scalability or performance improvements of storage engines, but nothing about query engine itself. For example, one classic example being InnoDB; if we look back all the scalability issues that community reported a year back or even few months back; most part of those issues have been [...]

Here is some file IO performance numbers from DELL MD1120 SAS storage array. Last year I did the same test with HP P800 storage array and numbers were impressive. But when it comes to this high end storage array, few surprises.  Before getting into actual details; lets see the test stats and configuration details. System Configuration: DELL [...]

There are many areas you need to review when addressing MySQL performance such as current database load, executed SQL statements, connections, configuration parameters, memory usage, disk to memory ratio, hardware performance & bottlenecks just to name a few.

If you were to just look at the data that is held in the database, what would you consider?
Here are my tips, when looking just at the data.

What is the current database size?

What is the growth of data over time, say daily, weekly?

Which are the 2 largest tables now?

What 2 tables are growing the fastest?

What tables have greatest churn, specifically DELETE’s?

How often do you optimize your tables?

What is your archiving/purging strategy? Do you even have one?

Review data types? I average 25% reduction in footprints, just by

  [Read more...]

We are pleased to announce the release 1.0.16 of HoneyMonitor, our GUI for MySQL™ administration and monitoring.

In this release, available for immediate download, we have fixed many bugs and included several improvements.

We are working to release a RC version as soon as possible.

The following is the list of changes:

• New Features:
  • new Tab “Defaults Folders” in the “HoneyMonitor Options” Window to set the default

  [Read more...]

Reading this article you will learn how to create a Customized Performance Report for one of your MySQL™ Servers using HoneyMonitor, a GUI for MySQL™ Server Administration, Monitoring & Performance Tuning.

Contents

• Introduction
• Step 1 - Choosing a File Name and Opening the Report Designer
• Step 2 - Editing the SQL Queries used by the Report
• Step 3 - Editing the Charts contained in the Report
  • Axis

  [Read more...]

A lot of my clients are in a position where their database performance is deteriorating but they are not “big enough” (or not willing/able to) explore sharding all of their data structures. They’re too big for the solution to be adding another read slave, but too small to justify the resources for re-designing their [...]

Yes, it has been too long since I last posted on this blog (3 months) - that's my apology; let's move forward.There's too many interesting MySQL bits and pieces that I still come across my desk that I cannot help but post these fantastic learning opportunities to my blog. Obviously, you can read about all things interesting from PlanetMySql.org, but I hope that my blog will help sift people

Having a status column is very common in databases today. It can be used to denote a user status: CREATE TABLE IF NOT EXISTS `user` ( `user_id` int(10) unsigned NOT NULL auto_increment, `email` varchar(32) NOT NULL, `pw_hash` char(40) NOT NULL COLLATE latin1_general_cs, `status` enum('PENDING', 'ACTIVE', 'DISABLED') default 'PENDING', `date_created` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`), UNIQUE KEY `idx_email` (`email`) ); or user-uploaded [...]

One of the cardinal rules of writing web applications is to escape user-generated input with functions like PHP’s real_escape_string. This is a great rule, but one that can have a negative impact on your application’s performance if used unnecessarily. For instance, when querying data with an integer parameter that is passed internally (not [...]

The purpose of this post is to outline a general flow-chart for improving the performance of queryies in MySQL. Much has been written on using EXPLAIN to optimize queries, but there is a whole process that should be followed in order to maximize the effectiveness of query performance tuning. Following is a visual [...]

Everybody knows than Nagios can be used as a service monitor to monitor things like Load Averages, MySQL Replication Status, RAID Array States, etc… Fewer know that there are plug-ins to monitor MySQL Performance Status, such as check_mysql_perf. Fewer still utilize Nagios’ built-in triggering mechanism to execute an additional script on the event of [...]