|
  |
|
|
Mysql Optimization on Linux
Mysql is always problem when we have a lot of databases.
If you have a dedicated server you probably use mysql server on your machine. In linux shell we allow to watch mysql processes via top and another good mysql watchers like mytop. Other mysql performance checkers are;
Mysqlreport and tuningprimer.sh script.
Mytop is not important now for us. We investigate important mysql variables first. First variable is "key_buffer".
Key_buffer is used to keeps the index keys on RAM like an index cache.Key_buffer speeds up the I/O processes.
Your Key_buffer size must be greater than your mysql index total.
How we will find mysql index total?
find /var/lib/mysql/ -name *.MYI | xargs du -sch
command shows us total index spaces of mysql.
We will change key_buffer in our my.cnf in /etc/my.cnf
1/4 of your current ram is enough for key_buffer size. Second variable is "query_cache".
Query_cache is used to keeps your query results in cache.
If frequently used query results keeping in cache disc I/O must be decrease and cause to fast reading of results in memory. In default mysql configuration query cache is off. You can open it in /etc/my.cnf with query_cache_type=1 command. If you use so much queries like forums etc. you must increase your query_cache_size as much as posible. Third variable is "max_connections".
Max_connections setting is used to limit how much users are connect to mysql in same time. If you have so much visitors you must increase this value. Default value is 500.
Small dedicated server like vps : 50~100 is enough
Medium dedicated servers: 250~500
Big dedicated servers: 500~1000
Important: If your max_connections setting is too high mysql will sucks your all memory. Be careful. Fourth variable is "table_cache".
Table_cache is limits the maximum numbers of tables that can be open at once. If you have much more tables you must increase this value in my.cnf.
Table_cache must be greater then your total tables. Other important variables are;
sort_buffer_size (sort_operations), join_buffer_size (joins), thread_cache_size (threads) and tmp_table_size (cache disk-based temporary tables)
Other variables will be controlled via mysql tuning primer script or mysqlreport script. Scripts shows us increase or decrease values of these variables. |
Mytop
| Mysqlreport
| tuning primer script
No reactions yet.
Please login or sign up to rate this intel.
Please login or sign up to add a comment.
The copyright for this content entitled "Mysql Optimization on Linux" has been specified by the contributor as:
Creative Commons Attribution-Share Alike 3.0
Details 
This content may be copied, distributed, and modified, as long as a) the original author is acknowledged with a link back to the content page, and b) if the work is modified, the result is distributed with this same license.
If you use this content according to the license specified, you must link to the following URL:
http://cash.qondio.com/
|
 |
|
This intel was contributed by cash
|
May, 2012
2008
January, February, March, April, May, June, July, August, September, October, November, December
2009
January, February, March, April, May, June, July, August, September, October, November, December
2010
January, February, March, April, May, June, July, August, September, October, November, December
2011
January, February, March, April, May, June, July, August, September, October, November, December
2012
January, February, March, April, May
|
|
Not a member yet?
Qondio is a powerful network for making it online. If you have a website to
promote, we can help.
Sign up and get in on the action.
|
|
Welcome to Qondio! Discover the awesome power this network can deliver by going to our About page. Or you could skip straight to the Sign Up form.
|
|
