{"id":327,"date":"2007-11-23T13:16:46","date_gmt":"2007-11-23T18:16:46","guid":{"rendered":"http:\/\/blogs.n1zyy.com\/n1zyy\/2007\/11\/23\/speeding-up-mysql-with-tmpfs\/"},"modified":"2007-11-23T13:16:46","modified_gmt":"2007-11-23T18:16:46","slug":"speeding-up-mysql-with-tmpfs","status":"publish","type":"post","link":"https:\/\/blogs.n1zyy.com\/n1zyy\/2007\/11\/23\/speeding-up-mysql-with-tmpfs\/","title":{"rendered":"Speeding up MySQL with tmpfs?"},"content":{"rendered":"

I’m still getting a decent percent of files being created on disk in queries, even though my tmp_table_size is an astonishing <\/a>128MB. (The whole blogs database uses about 6MB of disk.)<\/p>\n

The problem is described here<\/a>: TEXT and BLOB queries apparently don’t like being in memory. This page<\/a> explains it further.<\/p>\n

The problem is that… These are blogs. Aside from some trivial cross-table type stuff, every single query uses TEXT queries. Interestingly, the solution everyone proposes is using a ramdisk. I was somewhat concerned about using a ramdisk, though: for one, the procedure for creating it looked somewhat arcane, and one place talking about it mentioned that his 16MB of ramdisk was almost as big as his 20MB hard drive. I think of my old 20GB hard drive as ridiculously old. The other reason, though, is that ramdisk is scary: it’s a finite size. I’d love something like a 1GB ramdisk for \/tmp, but I don’t even have a gig of RAM, much less a gig to allocate for file storage.<\/p>\n

Enter tmpfs<\/a>. In a nutshell, it’s like tmpfs, but the size can be dynamic, and it can swap, which means I don’t have to worry about my 16MB tmpfs partition trying to store 17MB of data and blowing up. Creation was eerily easy:<\/p>\n

\n# Make a directory to use as a mountpoint, and give it a misleading name\nmkdir \/tmp\/ramdisk\n\n# Mount it as type tmpfs\nmount -t tmpfs tmpfs \/tmp\/ramdisk -o size=16M,mode=1777\n<\/pre>\n
In my.cnf, it’s as easy as changing tmpdir=\/tmp\/<\/tt> to tmpdir=\/tmp\/ramdisk\/<\/tt>.<\/p>\n
And now, we let it run for a while and see how performance feels.<\/p>","protected":false},"excerpt":{"rendered":"
I’m still getting a decent percent of files being created on disk in queries, even though my tmp_table_size is an astonishing 128MB. (The whole blogs database uses about 6MB of disk.) The problem is described here: TEXT and BLOB queries … Continue reading →<\/span><\/a><\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,8,10,13,18,22],"tags":[],"class_list":["post-327","post","type-post","status-publish","format-standard","hentry","category-computers","category-hero-of-the-day","category-ideas","category-linux-tips","category-ocd","category-programming"],"_links":{"self":[{"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/posts\/327","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/comments?post=327"}],"version-history":[{"count":0,"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/posts\/327\/revisions"}],"wp:attachment":[{"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/media?parent=327"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/categories?post=327"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/tags?post=327"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}