1

When I restart mysql, website becomes too slow for about 10 minutes.I think it is because innodb_buffer pool and query cache. innodb buffer pool =8G and query cache = 2G.

I am changing my .cnf for optimizing but i have 200 online users and slowing down server for 10 minutes makes users angy.

Is there anyway I can change mysql variables without mysql restart?

Anirudh Ramanathan
  • 46,179
  • 22
  • 132
  • 191
Mehdi Azizi
  • 187
  • 2
  • 10
  • Those aren't the variables making you slow after restart, those are the variables making you faster after a few minutes when everything is in memory again. Some global variables may be changed with `SET global varname=...`, some can't. So, it all depends. Having multiple mysql servers & taking one offline for a reload (the other one gets all the queries) can be blessing, but may be pricey of course. – Wrikken Dec 14 '12 at 22:13
  • Also, see [this answer](http://stackoverflow.com/questions/3430865/how-to-preload-tables-into-innodb-buffer-pool-with-mysql) to perhaps speed up the buffer filling. – Wrikken Dec 14 '12 at 22:14
  • thank you playing with variables with SET GLOBAL is better. – Mehdi Azizi Dec 14 '12 at 22:28

1 Answers1

1

Those aren't the variables making you slow after restart, those are the variables making you faster after a few minutes when everything is in memory again.

Some global variables may be changed with SET global varname=..., some can't. So, it all depends. Just a reminder: if you're satisfied with them... don't forget to add them in the config for when you DO need to restart. You wouldn't be the first to accidentally use a vital piece of configuration that way.

Having multiple mysql servers & taking one offline for a reload (the other one gets all the queries) can be blessing, but may be pricey of course.

Wrikken
  • 69,272
  • 8
  • 97
  • 136