MySQL

There is more than one way to do it….

I spent Friday examining the systems for a more traditional consulting gig. It is a familiar story to many people — the database performance was unacceptable. The company had a few log tables that had huge amounts of inserts and foreign keys, so they used InnoDB. Unfortunately, they also used a GUID as a primary key — varchar(32) and utf8. That’s right — their primary key for many of these tables was 96 bytes long (32 characters * 3 bytes per character), and as an InnoDB table, the primary key is clustered with every other index.

Is the query cache useful?

Mark Callaghan posted a good test of the MySQL query cache in different versions. His tests clearly show that in 5.0.44 and 5.0.84 and 5.1.38, there is more query throughput when the query cache is disabled. Mark’s benchmark definitely reinforces that turning on the query cache without any knowledge of your system is a bad idea, and I agree with him on that. But it does not in any way mean that the query cache is always a bad idea. It is important to know how the MySQL query cache works, so I will first explain that, and then explain why Mark’s test is not a very good broad generalization. MySQL’s query cache is not useful all the time, but it can be quite useful!

Using the Sphinx Search Engine with MySQL

Implementing SphinxSE into MySQL proved to be easier than it seemed in the beginning, although it took some time to compile and install everything. With a creative use of views, it could potentially be implemented right away in legacy applications, offering numerous advantages.

MySQL 5.1 and InnoDB Hot Backup Gotcha

Recently while we were building a slave with a newer version of MySQL 5.1 from an InnoDB Hot backup an error occurred The problem is, in MySQL 5.1, it is possible to log the slow query log and general log to tables in the mysql schema. These tables are created by default as CSV tables for performance reasons, even if MySQL is set not to log to tables. CSV tables, however, are not copied as part of the InnoDB Hot Backup process, creating this error. Here is the fix to get the slave working

Page 47 of 85« First Page...10...4546474849...60...Last Page »