Mysqld Optimization

Portal Home > Knowledgebase > Articles Database > Mysqld Optimization

Posted by zahirw, 05-14-2011, 05:32 AM
Hey guys, this is my current my.cnf config. The server is a cloud running 1.9gb RAM and a 3ghz cpu. [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock user=mysql log-error=/var/log/mysqld.log log-slow-queries = /var/log/mysql-slow-queries log_queries_not_using_indexes=1 query_cache_size=32M max_connections=100 long_query_time=5 old_passwords=1 table_cache=1024 #read_buffer_size=1M join_buffer_size=4194304 thread_cache_size=4 #additional test values under 1880MB ram query_cache_limit=16M read_buffer_size=4M key_buffer = 4M wait_timeout = 50 connect_timeout = 60 interactive_timeout = 50 max_connect_errors = 20 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid -- My resources are shared between various services(httpd,mysqld,sendmail) tuning-primer gave me the following JOINS Current join_buffer_size = 4.00 M You have had 1232 queries where a join could not use an index properly join_buffer_size >= 4 M This is not advised You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. -- Httpd seems fine but our DB is huge and its terribly slowing down the website. We've improved the indexes many fold but the site still runs slow. When the db is small in size, it runs really fast, a full db just pulls down the size. Thanks guys
Posted by relichost, 05-14-2011, 05:50 AM
Hi 2 things spring to mind, 1, optimize the database further / code (that uses it) 2, get a dedicated mysql server with more memory Thanks
Posted by hiabhilash, 05-14-2011, 05:56 AM
Already implemented Adodb cache ?
Posted by lynxus, 05-14-2011, 06:11 AM
Are you sure? Adding more indexes doesnt always speed things up. Infact more indexes can mean slower inserts and updates.. So be careful. Here are my suggestions ( that I do and these work! ) I've managed to bring servers from 40% load to 2% load from doing the following.. - Review your indexes ( Do you REALLLLLY need what you have?, Are you indexing the most used "where clause" columns ? Do you have too many? not enough? etc.. - Review your query's.. Use something like "mtop" to help find slow query's. - JOIN! Join tables where possible. Dont do multiple querys just to get the same data you could get from a join. - issue the "optimize table tablename" command from time to time. - Check your primary keys. You say your DB is "huge".. How huge is huge? Remember that a DB with millions of rows is not "big" Ive got databases with 50/60 million rows in them. The DB is GIG's in size. However it runs fine on a 512mb ram server.. - Employ a DBA if you realllly need to get stuff sorted. Mtop really is your friend and will really show slow querys. Copy the query, Then issue it manually on the CLI to check speeds and then start optimizing the query.
Posted by zahirw, 05-14-2011, 07:10 AM
Cant get a dedicated server, clients cost restrictions No, is it good and easy to deploy? know a good DBA?
Posted by zahirw, 05-15-2011, 09:03 AM
read_buffer_size=4M key_buffer = 4M join_buffer_size = 4M Are these correct for a machine of this size? tuningprimer gives an error for join buffer saying its not advised, Why?
Posted by david510, 05-16-2011, 08:50 AM
You need to turn on the slow query logging and run the query manually after setting the profiling from mysql prompt to see in which step it is taking more server resource. It can be during the time, the query is copied from the temp table or when the query is detached etc.. Profiling will work only if your mysql server is > 5.1.
Posted by zahirw, 05-16-2011, 10:05 AM
Slow query log is enabled. See above. Thanks dude