Aaron Brown
  • Member for 10 years, 3 months
  • Last seen more than a month ago
Calculating disk space usage per MySQL DB
Accepted answer
62 votes

There are 3 scenarios. If you are using MyISAM, it is easiest to just look at the filesystem and use du -sh /var/lib/mysql/database. If you are using InnoDB with innodb_file_per_table set, then you ...

View answer
Order by column should have index or not?
Accepted answer
33 votes

Yes, MySQL can use an index on the columns in the ORDER BY (under certain conditions). However, MySQL cannot use an index for mixed ASC,DESC order by (SELECT * FROM foo ORDER BY bar ASC, pants DESC). ...

View answer
How to manage millions of users?
32 votes

You're not going to have a billion users tomorrow and MySQL can handle several million rows without any problem. I have 5 million users in my user table and trust me, it isn't even on my radar of ...

View answer
InnoDB row locking - how to implement
Accepted answer
29 votes

What you want is SELECT ... FOR UPDATE from within the context of a transaction. SELECT FOR UPDATE puts an exclusive lock on the rows selected, just as if you were executing UPDATE. It also ...

View answer
What is the recommended way to backup a MySQL/Amazon RDS database to S3?
Accepted answer
24 votes

The recommended way to back up RDS is with automatic backups and DB snapshots. DB snapshots are basically the same as EBS snapshots, which are stored in S3 behind the scenes, but are only available ...

View answer
Why is MySQL is creating so many temporary tables on disk?
Accepted answer
18 votes

mysqltuner rarely provides any useful information. It uses mostly irrelevant statistics about "hit rates" and puts arbitrary limits on what is an acceptable number of widgets are acceptable. If you ...

View answer
Setting Circular Replication in mysql
14 votes

Rolando told you how to do it and his answer is excellent. The truth is, circular replication is an awful, terrible, terrible idea. Don't do it. Ever. "Absolutes are never true," but in this case, ...

View answer
How can I speed up "show columns" on MySQL?
12 votes

MySQL recalculates table statistics for certain operations that access INFORMATION_SCHEMA tables (SHOW COLUMNS is just a convenient alias for querying INFORMATION_SCHEMA.COLUMNS). Set ...

View answer
Where should you define foreign keys?
11 votes

I'm going to go out on a limb here fully expecting this to get down-voted since this is a DBA-focused group. I agree that using strict foreign keys is the best decision in most scenarios. However, ...

View answer
MySQL subquery slows down drastically, but they work fine independently
11 votes

In MySQL, subselects within the IN clause are re-executed for every row in the outer query, thus creating O(n^2). The short story is, don't use IN (SELECT).

View answer
MySQL reads / writes per table
Accepted answer
10 votes

Method 1 If you are using Percona Server or MariaDB (>= 5.2), you can simply set the userstat/userstat_running variable to enable a bunch of new INFORMATION_SCHEMA tables including one called ...

View answer
Any disadvantages of "SQL_BUFFER_RESULT" for MySQL SELECT?
Accepted answer
10 votes

SQL_BUFFER_RESULT creates a temporary table on the server for every result set. This is not a temporary table like CREATE TEMPORARY TABLE - it is an implicit temporary table as would be created when ...

View answer
Monitoring replication on mysql
Accepted answer
9 votes

Replication can break or misbehave in all sorts of fun and exciting ways. You need to monitor for three things: 1. Replication is running and has not stopped due to error To monitor whether or not ...

View answer
Running SQL Query on all databases
8 votes

Try running this and then cut n paste the output back into phpMyAdmin SELECT CONCAT('UPDATE `', schema_name, '`.\'wp_users\' SET \'user_pass\' = MD5(\'somepassword\') WHERE \'user_login\' =\'...

View answer
Sub-query executes 1 time for a parent query or more?
Accepted answer
8 votes

Prior to MySQL 5.6, the inner query is executed once per entry in the outer row and is easy to prove: mysql> SELECT COUNT(*) FROM sample_data; +----------+ | COUNT(*) | +----------+ | 8 | +-...

View answer
to what "scale" of applications does nosql support?
Accepted answer
8 votes

This question is really far too vague to answer effectively. There are dozens of "NoSQL" data stores out there which have various use cases. Here is a 10,000 foot view of what's out there. In my ...

View answer
Repairing Myisam Table when there was no additional Disk space & Table corrupted
7 votes

If you cannot free up or add any disk space, you could copy the table_data.* files (MYD,MYI,frm,etc) to another machine with plenty of free disk, run the repair there with myisamchk, then copy the ...

View answer
MySQL replication - replicate to multiple DBs on slave?
Accepted answer
6 votes

The first obvious question is why would you want to do this? It's probably a bad idea and completely unnecessary, since you can join across databases: SELECT * FROM db1.table1 JOIN db2.table2 ON db1....

View answer
MySQL SBR vs RBR
Accepted answer
6 votes

There is nothing special about master/master replication in MySQL - it is bidirectional Master/Slave where each server is just a slave of the other server. Otherwise, it behaves exactly as a "regular"...

View answer
Performance settings for MyISAM tables - keep everything in memory
Accepted answer
6 votes

MySQL only caches the indexes in MySQL's memory, not the data. The data is only cached by the underlying operating system's filesystem caches. The most important value to tune in my.cnf is ...

View answer
Is it possible to pass parameters to pt-table-checksum using an options file?
Accepted answer
5 votes

Yes, you can do this. pt-table-checksum --config FILENAME Where FILENAME looks like tables = table1,table2,table3 ignore-tables-regex = ^tmp chunk-time = .2 max-...

View answer
Mysql slows down temporarily after large file copying
5 votes

MyISAM tables rely on the filesystem cache to keep data in memory (the indexes are kept in the internal key buffer cache, as specified by key_buffer_size). You haven't said what your total data size ...

View answer
Create table statement in MYSQL
Accepted answer
5 votes

You have to specify a length for your VARCHAR fields and also make your AUTO_INCREMENT field a the primary key. I made up lengths here for you - you should consider the maximum length that you ...

View answer
MySQL replication: "Houston, We've Got a Problem"
Accepted answer
5 votes

If you really don't care about that table, you can use pt-slave-restart on the slave and have it skip those problems. I would be conservative about running it and make sure that you are only skipping ...

View answer
Randomly generated password for mysql admin over time?
Accepted answer
5 votes

Why would you want to do this? What risk does it mitigate? This sounds like a great way to lock yourself out of your database in a way that requires downtime to recover. Limit your exposure by ...

View answer
mysql change schema on the fly
5 votes

My first quesiton is, what is "very huge?" Depending on the answer to that question, there may be other ways to optimize your migrations to reduce the time that they take. Second question is, how ...

View answer
What's wrong with this MySQL query?
Accepted answer
4 votes

You are using the wrong quotes. Use ` instead of ' CREATE TABLE `topics` ( `topicid` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, `creator` varchar(255) NOT NULL, `createtime` ...

View answer
Read mysql config (my.cnf) using mysql console
Accepted answer
4 votes

Depending on how your access was implemented, you may be able to look at my.cnf with the \! command. mysql> pager less mysql> \! cat /etc/mysql/my.cnf .... mysql> nopager If you want to ...

View answer
Incremental backups using MySQL Workbench
4 votes

To be frank, if you are using a GUI tool as your backup strategy, you are doing it wrong. Backups need to be scripted, automated, and regularly tested or else you might as well not have them. If ...

View answer
MySQL general query log performance effects
4 votes

Like Rolando, I would recommend using pt-query-digest to capture this data, but instead of using the --processlist option, capture data with tcpdump. The processlist method will not capture ...

View answer