Too much MySQL writting to the HDD, how to offload writting?

postcd

• October 29, 2014 15:13

I have optimised mysql configuration i think quite good, but there are queries that i believe canot be cached in RAM (i have plenty of free RAM), but use HDD swap? My result: "TEMP TABLES Current max_heap_table_size = 1.00 G Current tmp_table_size = 1.00 G Of 27262557 temp tables, 35% were created on disk [COLOR="#FF0000">Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables [COLOR="#008000">Note! BLOB and TEXT columns are not allow in memory tables. If you are using these columns raising these values might not impact your ratio of on disk temp tables."
Can i anyhow offload mysql HDD writting/(swapping?) on other HDD/server without physically moving mysql databases causing more than 40 seconds downtime to one website? WHM feature "Setup Remote MySQL Server" do not offload existing mysqls unless i transfer them to new server, and as i say i dont want bigger downtime. Also it sounds better 4me to have all at one server. thx PS, one possibility is googling "MySQL tmpdir on a RAM disk", these two tutorials looks good: (issue is i dont know hot to prevent case mysql want to write more data then is ramdisc size)

• 

  whr

  • October 30, 2014 09:03

  If you can afford a little downtime, get an SSD disk and move the entire database folder there. Then symlink it to the actual location. SSD drives are good in handling higher read/write cycles.

  0
• 

  cPanelMichael

  • October 30, 2014 15:50

  Hello :) Note that the following post might be helpful if you plan on implementing the steps suggested on the previous post: Moving MySQL Data Location Thank you.

  0

Please sign in to leave a comment.