This is a static archival copy of my old wiki.

My current blog is here: https://blog.bjdean.id.au/

• Programming Notes
• mysql

Creating a mysqldump with a limited number of rows per table

To create a subset version of a database (for instance to create a semi-usable test database using real data but when the live database is very large):

mysqldump --where="1 LIMIT 5000" db_name

This works because the --where parameter is appended to the query that selects content from the tables (so it's not actually limited to being a WHERE clause).

References:

• http://stackoverflow.com/questions/135835/limiting-the-number-of-records-from-mysqldump

Tuning

Various articles:

• http://yoshinorimatsunobu.blogspot.com.au/2009/04/linux-io-scheduler-queue-size-and.html

Synopsis: the myisam engine does not do any io optimisation (deferring this as is common with myisam to the operating system). To get the maximum benefit from the io scheduler optimisation increase the io scheduler queue size from the defaulf of 128:

root@server:~# echo 100000 > /sys/block/sda/queue/nr_requests
root@server:~# echo 100000 > /sys/block/sdb/queue/nr_requests