Archive for the ‘mysql’ Category
Thursday, October 11th, 2007
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 |
| ...
Posted in mysql | No Comments »
Monday, June 11th, 2007
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.
Posted in mysql | No Comments »
Wednesday, May 30th, 2007
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" ...
Posted in mysql, tips | 1 Comment »
Thursday, May 17th, 2007
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. ...
Posted in mysql | 3 Comments »
Wednesday, May 2nd, 2007
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, ...
Posted in mysql | No Comments »
Wednesday, March 28th, 2007
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 ...
Posted in mysql | 1 Comment »
Friday, March 9th, 2007
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, ...
Posted in linux, mysql | No Comments »
Tuesday, February 13th, 2007
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 ...
Posted in apache, linux, mysql, php | 2 Comments »
Thursday, February 8th, 2007
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 ...
Posted in mysql | 4 Comments »
Tuesday, January 23rd, 2007
Our sysadmin has a nice blog post with a few tips for using mysqldump, especially if your database is used for more than a basic site, or if you have stored procedures and/or triggers.
Posted in mysql | No Comments »