Using tmpfs for MySQL tmpdir setting

This is incredible, by the way. Any time MySQL needs to use a tmp table on disk, you can make it use RAM disk instead.

WARNING: if the tmpfs partition you make isn’t big enough, MySQL will not be able to complete queries. Make sure you have enough RAM to do this.


mkdir /tmp/mysqltmp
chown mysql:mysql /tmp/mysqltmp

id mysql
# example:   uid=502(mysql) gid=503(mysql) groups=503(mysql)

#to set up on server restart, put in fstab something like (replace gid, uid with number from above)
tmpfs    /tmp/mysqltmp    tmpfs   rw,gid=503,uid=502,size=2G,nr_inodes=10k,mode=0700 0 0

mount /tmp/mysqltmp

# you don't need this:
# mount -o size=2g,gid=520,uid=518,nr_inodes=10k,mode=0700 -t tmpfs tmpfs /tmp/mysqltmp

#edit my.cnf, adding
tmpdir=/tmp/mysqltmp/

restart mysql

Read moreUsing tmpfs for MySQL tmpdir setting

DbTable and all its glory

The example: http://www.koopman.me/dbtable/

I got the concept of the class, DbTable, from a book called PHP5 Professional. The idea is we have a class, this abstract class, that allows us to quickly make a new class out of any database table. Database tables make good objects. We often make them names of objects, like Shopper or Product. It makes sense to create classes that represent these objects, and to have a clean, consistent way to manipulate the data in the table. It also abstracts the database layer from application logic. If you just looked at example.phps, you’d have no idea if the database was flat file, MySQL, postgreSQL, or if it was even a database at all. Abstraction is a good thing, and one of the principles of object oriented programming.

Read moreDbTable and all its glory

Scaling MySQL Vertically, The Sun Way

This is a response to a response to an article about Scaling MySQL with a Sun server with a 256-thread count.

With x86 based processors, you get high speed threads, but only a couple (up to four) per processor. With the Sun Coolthread T2 processor, you get slower speed threads, but a lot of them, 64 threads per processor. The 256-way is 4 of these processors on one board.

Read moreScaling MySQL Vertically, The Sun Way

Internet Video, Not Taking Off As Fast As….

Silverlight seems to be taking off a lot slower than I anticipated. One problem is it doesn’t work on Linux. There is Moonlight, but, its not so obvious. Here I am browsing my blog on my Xubuntu desktop, and I visit a blog entry I made awhile ago on Silverlight (at the time on an XP desktop), and I can’t view my video.

Read moreInternet Video, Not Taking Off As Fast As….

MySQL 4.1 “WHERE … OR … ” results in full table scan

This is a real life example. Case study is, we had performance problems that was narrowed down to a large number of the following query:

SELECT sg.gid FROM Scheduleable_Group sg WHERE sg.domain=’domain.com’ OR sg.sid=’555′;

The database engine is InnoDB on MySQL 4.1.

Continue reading for the analysis and solution.

Read moreMySQL 4.1 “WHERE … OR … ” results in full table scan