MySQL
Looking just at the data
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.
Read moreWhat alert monitoring do you use?
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.
Read moreNoSQL options
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.
Read moreUnexplained function output
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.
Read moreMySQL Query Cache path
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.
Read moreUsing the Query Cache effectively
Maximize your strengths, minimize your weaknesses. You can apply this approach to many things in life, I apply it to describing and using MySQL the product, and it’s components. The Query Cache like many features in MySQL, and indeed features in many different RDBMS products (don’t get me started on Oracle *features*) have relative benefits.
Read moreEXPLAIN – An essential tool for MySQL developers.
Just recently I came across the presentation “Bend SQL to your will with EXPLAIN” by Ligaya Turmelle via the Linked In – MySQL Speakers and Presenters group. Slides available at Slideshare .
Read moreCalculating your database size
I generally use the following MySQL INFORMATION_SCHEMA (I_S) query to Calculate Your MySQL Database Size . This query and most others that access the MySQL INFORMATION_SCHEMA can be very slow to execute because they are not real tables and are not governed by physical data, memory buffers and indexes for example but rather internal MySQL data structures.
Read moreUnderstanding Innodb Transaction Isolation
The MySQL Innodb storage engine provides ACID compliance, the ‘I’ being isolation. There are four states defined in MySQL with the tx_isolation system variable, READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ and SERIALIZABLE. Generally MySQL installations do not modify the default value of tx_isolation = REPEATABLE-READ, however I have seen with a number of clients the default value has been changed to READ-COMMITTED.
Read moreSELECT INTO DUMPFILE
While learning a new ORDER BY syntax recently, as a diligent architect/DBA I reviewed the documentation. What I also found in the SELECT syntax which I did not also know was the keyword DUMPFILE.
Read moreMy favorite MySQL data type – DECIMAL(31,0)
It may seem hard to believe, but I have seen DECIMAL(31,0) in action on a production server. Not just in one column, but in 15 columns just in the largest 4 tables of one schema.
Read moreMySQL SHOW PRIVILEGES
Some days you learn about MySQL commands even without knowing about them. Today I wanted to check the privileges a user had because they did not have permissions to drop a view.
Read moreGetting started with Drizzle JDBC
In preparation for some Java work I wanted to configure and test the Drizzle JDBC Driver. Any chance to swing Drizzle into a MySQL discussion is worth the research. What I found was an issue compiling and an issue running on Ubuntu 9.
Read moreEngine agnostic MySQL test cases
Mark writes Now we all need the storage-engine independent test suite . I could not agree more. I have made comments about this probably as early as 4 years ago, and both before and while working for MySQL Inc.
Read more