I had trouble today on a client site using my MySQL power tools Maatkit and Statpack.

$ ~/scripts/statpack.py --files=mysql.status.091015.080001.txt,mysql.status.091015.090001.txt
Traceback (most recent call last):
  File "/home/rbradfor/scripts/statpack.py", line 563, in ?
    main()
  File "/home/rbradfor/scripts/statpack.py", line 527, in main
    locale.setlocale(locale.LC_NUMERIC, '')
  File "/usr/lib64/python2.4/locale.py", line 381, in setlocale
    return _setlocale(category, locale)
locale.Error: unsupported locale setting

$ cat /var/log/slow-query.log | ./mk-query-digest
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "e_US"
    are supported and installed on your system.
perl: warning: Falling back to the standard …

Q: What SQL is running on your MySQL database server now?
A: The bane of pain for MySQL DBA’s when there is no official MySQL instrumentation that is dynamic and fine grained sufficiently to solve this problem at the SQL interface.

While hybrid solutions exist, the lack of dynamic and real-time are the issues. There is however great work being done by Baron and others on Maatkit mk-query-digest and packet sniffing the MySQL TCP packets.

$ sudo tcpdump -i eth0 port 3306 -s 65535  -x -n -q -tttt | ./mk-query-digest --type tcpdump
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
# Caught SIGINT.
5444 packets captured
8254 packets received by filter
2809 packets dropped by kernel
# 2.1s user time, 40ms system time, 22.23M rss, 57.60M vsz
# …

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, …

Feels like a long time ago, but my talk at the MySQL User’s Conference back in April 09 on running MySQL Multiple Times to get better performance is now available online at YouTube. The original PDF of the presentation is available here.View it here: YouTube – Improving Performance by Running MySQL Multiple Times

Feels like a long time ago, but my talk at the MySQL User’s Conference back in April 09 on running MySQL Multiple Times to get better performance is now available online at YouTube. The original PDF of the presentation is available here.

View it here: YouTube – Improving Performance by Running MySQL Multiple Times

Has anyone read the report Microsoft, IBM and Oracle Lead the SMB and Mid-Market Database Segment - Yankee Group - 7/31/2006 - 5 Pages - ID: YANL1327246?

Paying 800USD just to know the percentages of each database is a bit expensive for my small startup budget, especially since it is not me who needs the information.

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 …