{"id":3023,"date":"2014-09-11T03:35:57","date_gmt":"2014-09-11T03:35:57","guid":{"rendered":"http:\/\/www.deuzebranaweb.com.br\/?p=3023"},"modified":"2014-09-11T03:35:57","modified_gmt":"2014-09-11T03:35:57","slug":"mysql-optimization","status":"publish","type":"post","link":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/2014\/09\/11\/mysql-optimization\/","title":{"rendered":"MySQL optimization"},"content":{"rendered":"

MySQL optimization<\/h2>\n\n\n\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
products:<\/span><\/td>\n<\/tr>\n
\u00a0\u00a0\u00a0TGS<\/a><\/td>\n<\/tr>\n
\u00a0\u00a0\u00a0TTS<\/a><\/td>\n<\/tr>\n
\u00a0\u00a0\u00a0TSE<\/a><\/td>\n<\/tr>\n
\u00a0\u00a0\u00a0TES<\/a><\/td>\n<\/tr>\n
\u00a0\u00a0\u00a0TPS<\/a><\/td>\n<\/tr>\n
\u00a0\u00a0\u00a0TCS<\/a><\/td>\n<\/tr>\n
\u00a0\u00a0\u00a0TSELA<\/a><\/td>\n<\/tr>\n
\u00a0\u00a0\u00a0Nitro<\/a><\/td>\n<\/tr>\n
<\/td>\n<\/tr>\n
services:<\/span>
\nprogramming<\/a>
\nsystem administration<\/a>
\nsecurity<\/a>
\nsearch engine optimization<\/a><\/td>\n<\/tr>\n
<\/td>\n<\/tr>\n
tutorials:<\/span>
\nperl & mysql<\/a>
\nphp & mysql<\/a>
\nmysql optimization<\/a><\/td>\n<\/tr>\n
<\/td>\n<\/tr>\n
Contact us<\/a><\/td>\n<\/tr>\n
<\/td>\n<\/tr>\n
T Scripts home<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/td>\n\n

MySQL optimization<\/b>. Here we will give some quick help on optimizing MySQL database performance.<\/span><\/p>\n

Requirements<\/b>: MySQL.<\/span><\/p>\n

One of the factors with the biggest impact on database performance is not the MySQL settings, but your queries! Make sure you have optimized all your queries first, and have created the right indexes on your tables for MySQL to use.<\/span><\/p>\n

A useful MySQL command for this is EXPLAIN. If you use EXPLAIN SELECT * FROM table WHERE field=”something” , MySQL will tell you how many rows it needs to search, and what index it can use for this, among other things.<\/span><\/p>\n

After you have optimized your queries and created indexes, you can start tweaking your my.cnf MySQL configuration file. Keep in mind that if you optimize or change something you should be able to benchmark it to make sure it will actually increase the performance.<\/span><\/p>\n

Here is a sample my.cnf file (MySQL 4.1) for use on a dual processor server with 2GB of RAM memory:<\/span><\/p>\n

# The following options will be passed to all MySQL clients\n[client]\nsocket=\/tmp\/mysql.sock\n\n\n# The MySQL server\n[mysqld]\ntmpdir=\/tmp\nsocket=\/tmp\/mysql.sock\nskip-locking\nskip-networking\nskip-name-resolve\n\nserver-id=1\n\nmax_connections=500\nkey_buffer_size=384M\nmax_allowed_packet=16M\ntable_cache=256\nsort_buffer_size=2M\nread_buffer_size=2M\njoin_buffer_size=2M\n\n# if you are performing GROUP BY or ORDER BY queries on tables that\n# are much larger than your available memory, you should increase\n# the value of read_rnd_buffer_size to speed up the reading of rows\n# following sorting operations. \n# but: change the session variable only from within those clients \n# that need to run large queries\nread_rnd_buffer_size=2M\n\nmax_heap_table_size=256M\ntmp_table_size=256M\n\nmyisam_sort_buffer_size=64M\n\n# increase until threads_created doesnt grow anymore\nthread_cache=256\n\nquery_cache_type=1\nquery_cache_limit=1M\nquery_cache_size=32M\n\n# Try number of CPU's*2 for thread_concurrency\nthread_concurrency=4\n\n\n[mysqldump]\nquick\nmax_allowed_packet=16M\n\n[mysql]\nno-auto-rehash\n\n[isamchk]\nkey_buffer=128M\nsort_buffer_size=128M\nread_buffer=2M\nwrite_buffer=2M\n\n[myisamchk]\nkey_buffer=128M\nsort_buffer_size=128M\nread_buffer=2M\nwrite_buffer=2M\n\n[mysqlhotcopy]\ninteractive-timeout\n<\/span><\/pre>\n
You can use the SHOW STATUS MySQL command to monitor some of the variables (like Threads_created , Created_tmp_disk_tables , Created_tmp_tables and so on).<\/span><\/p>\n
If you would like more information, feel free to contact us<\/a>.<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
 <\/p>\n
MySQL optimization<\/a><\/p><\/blockquote>\n