At work today I ran into a new reason for not keeping open MySQL connections for a long time. It involves dynamic session variables like long_query_time. I wanted to capture a couple of hours worth of all queries in the slow query log so I could analyze them with pt-query-digest from the excellent Percona Toolkit.… Read More: Changes to long_query_time Not Changing »
When you use the MySQL JDBC driver to select rows from a table, the connection will block until the entire ResultSet has been pulled over to the client. In most cases this makes sense, especially if the server is on a different host. Retrieving the entire ResultSet will minimize the number of TCP packets that… Read More: Streaming ResultSets with the MySQL JDBC Driver »
If a replication slave gets out of sync with the master, you can bring them back in sync by running statements that don’t execute on every server in the replication chain. There are sane and insane ways to do this. The right way is to execute SET SESSION sql_log_bin=0; on your current connection before running… Read More: How to Avoid Replicating Statements in MySQL »
Like a lot of software tools, MySQL error messages are sometimes not very precise, and sometimes not even that accurate. Last night during a database schema update, we ran into an issue creating a table with a foreign key constraint. Our tests had run successfully on several other MySQL 5.0 and 5.1 systems, so I… Read More: MySQL Foreign Key Constraint Error 150 »
Monitoring MySQL with Cacti Cacti, like many other tools (Munin, Cricket, etc.), is a wrapper around RRDTool Baron feels that existing tools (zabbix, ZenOSS, OpenNMS, etc.) that try to do graphing and alerting do at least one of them poorly, though Reconnoiter looks promising. Cacti does graphing well RRDTool does interpolations, so you lose the… Read More: MySQL Conference 2010 Day 3 »
Replication Tips – slides Single master can typically handle a few dozen slaves If you have a lot of slaves and want to do filtering for all, maybe use blackhole engine to do filtering in one place. Especially if running blackhole slave on same server to limit network bandwidth. Replicate over WAN – slave_net_timeout, master_connect_retry,… Read More: MySQL Conference 2010 Day 2 »
What’s New in MySQL 5.5? Performance Unleashed! – slides Default storage engine is InnoDB (what had been called the InnoDB plugin, but now is the only version). Many locks and mutexes held back scalability in 5.1. Biggest win was fixing uses of LOCK_open. It was used for many things, some of which made no sense. Introduced… Read More: MySQL Conference 2010 Day 1 »
This year’s MySQL Conference was once again jam packed with very technical presentations. Best of all was the news about all the performance and feature enhancements in MySQL 5.5. The benchmark results for the performance improvements were very impressive. After attending this year’s conference, I’m more confident about MySQL’s future than I’ve been for a… Read More: MySQL Conference 2010 »
I just updated an old post on using Python with MySQL on OS X. The good news is that you don’t need to create an extra directory of symbolic links anymore. The not really bad news is that you have to be careful about 32-bit versus 64-bit. Ideally, you’ll first upgrade to a 64-bit version… Read More: MySQL, Python and Snow Leopard »
After upgrading to the newest version Maatkit, the invaluable toolkit for MySQL, I was no longer able to run any of the Maatkit tools. The problem turned out to be caused by my update to MacPorts after upgrading to Snow Leopard. Note, if you haven’t installed Maatkit, yet, you may need to first install the… Read More: Maatkit Install on OS X Snow Leopard »