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.

1. What is the current database size?
2. What is the growth of data over time, say daily, weekly?
3. Which are the 2 largest tables now?
4. What 2 tables are growing the fastest?
5. What tables have greatest churn, specifically DELETE’s?
6. How often do you optimize your tables?
7. What is your archiving/purging strategy? Do you even have one?
8. Review data types? I average 25% reduction in footprints, just by choosing optimal data types, generally with zero …

More importantly, how often to you confirm access to your server and database with that alert monitoring?

With a client yesterday the primary database server while still usable and serving connections for a while, but was not accessible via SSH to investigate performance issues. It eventually became non responsive and required a physical reboot. With alert monitoring for system availability only recorded every 5 minutes this was simply too long a delay.

This lead to a discussion with more questions then answers including.

• How often should you ping your server(s), both internally and externally?
• How often do you connect physically to your server for confirmation, e.g. a ssh keyed authentication test?
• How often do you perform a physical database connection test?
• How often do you do an end to end test, including http request to database query test?

As with all of …

The NoSQL event in New York had a number of presentations on non relational technologies including of Hadoop, MongoDB and CouchDB.

Coming historically from a relational background of 20 years with Ingres, Oracle and MySQL I have been moving my focus towards non relational data store. The most obvious and well used today is memcached, a non persistent distributed key/value pair store. There are a number of persistent key/value stores in the marketplace, Tokyo Cabinet, …

There is no centralized authentication for mysql. You have to set up your user accounts individually for each of your mysql servers. You can use replication, but that would mean, your master servers must have the same authentication privileges as your slaves have. What about security?

So, here is a little workaround that I did and I can't find the same anywhere.

How does it work?

This is an unofficial and unsupported solution. Let's have a master and one or more slave hosts. On your master, you have to create a database lets say slave_auth. Create the same tables in this database as you have in the 'mysql' database and needed for authentication (more details below), and set up a user who can read/write those tables.

When you are done, create a temporary database on your slaves lets say auth_temp with the exact structure of the tables on your master, but with federated engine connected to your …

The favourite Open Query course modules as well as reworked and brand new ones, with November/December 2009 dates for Brisbane, Sydney, Canberra and Melbourne listed below. You can register for days/modules individually, to suit your time, budget and current needs. Your trainers are Sean, Ray and Arjen (see OQ people).

For the Canberra and Melbourne days which are DBA/HA, registrations for all of the modules in a series before 15 October will receive a copy of the “High Performance MySQL” book (normal bookstore price is AUD 105).

Canberra

• Thu 5 Nov: MySQL High Availability – Strategy and Tools
• Fri 6 Nov: MySQL Cluster …

(This note should not go to PlanetMySQL, sorry if it goes).
We again have problem with comments to blog posts. It's technical problem, we did upgrade some components and it seems it affected captcha. We are working on fix.
We are sorry for the inconvenience and thank for reports about the problem.

Entry posted by Vadim | No comment

Add to: | | …

Lot of talk lately about the effectiveness or lack thereof of the MySQL query cache.  I’m kind of surprised we are still having this discussion in late 2009, I really thought these issues have been long settled.

It’s well known that the query cache is terrible for environments with a constant stream of writes.  Each write invalidates the cached results for every query touching that table.  This means that your database needs to be constantly inserting and purging the query cache in an environment with mixed reads and writes (aka the most common type of “Web …

We’ve been able to do MySQL 5.1 binary tarballs for a bit now (great working together with Kristian Nielsen of Monty Program), but packages are bit more tricky. Peter has been working on Debian/Ubuntu while I’ve focused on RH/CentOS. The following is from an OurDelta (trial build run) RPM install on CentOS 5 x64:

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.38-maria-beta1-ourdelta (OurDelta - http://ourdelta.org/)

mysql> CREATE TABLE test.t1 (i int) ENGINE=PBXT;
Query OK, 0 rows affected (0.10 sec)

mysql> SHOW CREATE TABLE test.t1\G
*************************** 1. row ***************************
Table: test.t1
Create Table: CREATE TABLE `test.t1` (
`i` int(11) DEFAULT NULL
) ENGINE=PBXT DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO test.t1 values (1);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM test.t1;
+------+
| i    |
+------+
| …

I was asked today to confirm the operation of INET_ATON() and INET_NTOA() functions for converting IP4 strings to numeric representations. My tests on the machine I was just connected to at the very instant reported the following results.

mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 5.1.31-1ubuntu2 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT INET_ATON('74.165.97.204') AS ipn;
+------------+
| ipn        |
+------------+
| 1252352460 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT INET_NTOA(1252352460) as ipa;
+---------------+
| ipa           |
+---------------+
| 74.165.97.204 |
+---------------+
1 row in set (0.01 sec)

The results on the client via ODBC using MySQL 5.0.45 was?

ipn
1252352460

ipa
37342E3136352E39372E323034

What is causing this? It was immediately suspicious the pattern of 2E (the only letter), which translates from Hex to decimal …

Just how effective is the Query Cache on performance? If you are not comfortable reading the MySQL code you can cheat very easily with the SHOW PROFILE command. This demonstration will show you at a high level the relative impact without and with the Query Cache. First let us confirm the Query Cache is not used.

mysql> SHOW GLOBAL VARIABLES LIKE 'query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
5 rows in set (0.00 sec)

We now enable profiling.

mysql> SET PROFILING=1;

We run our …