We were using replication to deal with certain queries that were producing table scans. I realize this is not a great long term solution but we were migrating a web site that was set up this way, so it wasn’t really a choice.
We had a database that was a mix of InnoDB and a few MyISAM tables. The MyISAM tables were used for fulltext searches.
To get a database dump, we were using the command mysqldump --all-databases --single-transaction --master-data=1 > dumpfile.
mysql
- MySQL query cache can be useful, if it works. Here’s how to check it’s effectiveness. show status like 'qc%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 6407 | | Qcache_free_memory | 24176544 | | Qcache_hits | 3075026 | | Qcache_inserts | 2435740 | | Qcache_lowmem_prunes | 363018 | | Qcache_not_cached | 157193 | | Qcache_queries_in_cache | 16022 | | Qcache_total_blocks | 39912 | +-------------------------+----------+ You can see here that we have 24Megs of unused query cache.
- Let me first state that I’ve only run MySQL on Linux and MacOS X, never Windows. This is the first I’ve heard of Innodb having massive performance issues. Check out Karl Seguin’s blog post.
- I didn’t find any simple ways to determine how hard our mysql database was working, so I whipped this up. It uses a 10 second delay to figure out the queries per second average. time=10 orig=mysql -e “show status” | awk ‘{if ($1 == “Questions”) print $2}’ sleep $time last=mysql -e “show status” | awk ‘{if ($1 == “Questions”) print $2}’ diff=expr $last - $orig avg=expr $diff / $timeecho "$avg"
- After banging my head against a wall for a while, I finally found this thread from June 2005 in the MySQL support forums regarding using variables in the LIMIT clause. They don’t support it yet. There’s no time table that indicates when it will be supported either. It’s very unfortunate, I was looking forward to benchmarking the difference between our ad hoc code and the stored proc version. This code will not work: SELECT field from table LIMIT limit_var
- Aggregates are awesome. But sometimes you want to do 2 counts in a query, and have one of them be more restrictive than the other. Lets say our database focuses on pictures, and rating them on a scale from 1-100. We want to know the average rating, the minimum rating, the max, the number of ratings, and the number of ratings over 75. First let’s setup the tables. create table rating ( picture int not null, userid int not null, rating int not null, primary key (picture, userid) ) engine=innodb;
- MySQL won’t let you delete from a table using a subquery that references itself. Fair enough. To get around this, up till now, I’ve used temporary tables. However, I’ve never really liked it, and I’ve always wanted a JOIN delete. Well, apparently it exists. I’m cleaning out some data from some really old database dump. I needed to preserve the data in the article table, but remove the rows in the city table based on a column in the article table.
- Using mysql -e’s feature, combined with awk and xargs, I was able to call an existing stored procedure repeatedly for a resultset. Yes, I could have written another stored procedure to do this, I realize. But I guess I like doing things the hard way. Either that, or this is just less code. Or I wanted the awk and xargs practice. Whatever. mysql database -e "select id from category where foreign_key in (2771, 2769, 2766, 2772, 2767)" | awk -F\| '{print $1}' | xargs -ivar mysql database -e "call move_category(var, 5666)"
- Every now and then, we find that we will have a sudden increase in the number of apache processes, load average will spike up, and then go back down to normal. In rare cases, we will see the same thing happen, and the load avg spike WAY up, all queries appear locked up, and the server must be rebooted. I am looking for ways of determining what caused this. I should note that it happens extremely rarely, and has never shown up in a load test.
- As of MySQL 5.0 there is no SHOW CREATE TRIGGER which is pretty annoying. I don’t like using “show triggers like “, so I figured I’d write a wrapper around mysqldump. For the record, this was a huge pain the ass. I did this on Fedora, GNU sed version 4.1.5. You need to have your password stored in ~./my.cnf for this to work, I believe. I haven’t tried it without it.