Invision Community 4: SEO, prepare for v5 and dormant account notifications By Matt Monday at 02:04 PM
SecondSight Posted May 12, 2011 Posted May 12, 2011 Hello ! :) I'm going to have the ibf_core_item_markers_storage table changed to InnoDB. I keep MyISAM for the other tables. I don't know what to add in the my.cnf file. Here is what I use so far :[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-slow-queries = /var/log/mysql-slow.log long_query_time = 5 log-long-format #log-queries-not-using-indexes log-warnings=2 skip-locking skip-innodb query_cache_limit=6M query_cache_size=512M query_cache_type=1 query_cache_min_res_unit=2K tmp_table_size=256M max_heap_table_size=256M max_user_connections=250 max_connections=500 interactive_timeout=100 wait_timeout=100 connect_timeout=60 thread_cache_size=512 key_buffer_size=3072M max_allowed_packet=32M table_cache=3072 #net_buffer_length=1M join_buffer_size=4M sort_buffer_size=6M read_buffer_size=2M read_rnd_buffer_size=4M max_connect_errors=10 thread_concurrency=8 myisam_sort_buffer_size=1024M low_priority_updates=1 concurrent_insert=2 server-id=1 #[mysql.server] #user=mysql #basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/lib/mysql/mysql.pid #open_files_limit=8192 open_files_limit=9000 #[mysqldump] #quick #[mysqldump] #quick #max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [myisamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [mysqlhotcopy] interactive-timeout Can you give me pieces of advice ? I think I will certainly have to remove this : skip-innodb, but what else should I do ? Thank you ! :)
SecondSight Posted May 12, 2011 Author Posted May 12, 2011 I also add what tuning-primer returns, perhaps this may help :MySQL Version 5.0.92-community-log x86_64 Uptime = 42 days 7 hrs 39 min 20 sec Avg. qps = 74 Total Questions = 272204832 Threads Connected = 1 Server has been running for over 48hrs. It should be safe to follow these recommendations SLOW QUERIES The slow query log is enabled. Current long_query_time = 5 sec. You have 15277 out of 272204853 that take longer than 5 sec. to complete Your long_query_time seems to be fine BINARY UPDATE LOG The binary update log is NOT enabled. You will not be able to do point in time recovery See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html WORKER THREADS Current thread_cache_size = 512 Current threads_cached = 235 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 500 Current threads_connected = 1 Historic max_used_connections = 236 The number of used connections is 47% of the configured maximum. Your max_connections variable seems to be fine. No InnoDB Support Enabled! MEMORY USAGE Max Memory Ever Allocated : 7.25 G Configured Max Per-thread Buffers : 7.93 G Configured Max Global Buffers : 3.50 G Configured Max Memory Limit : 11.44 G Physical Memory : 15.70 G Max memory limit seem to be within acceptable norms KEY BUFFER Current MyISAM index space = 2.10 G Current key_buffer_size = 3.00 G Key cache miss rate is 1 : 2077 Key buffer free ratio = 13 % Your key_buffer_size seems to be too high. Perhaps you can use these resources elsewhere QUERY CACHE Query cache is enabled Current query_cache_size = 512 M Current query_cache_used = 297 M Current query_cache_limit = 16 M Current Query cache Memory fill ratio = 58.01 % Current query_cache_min_res_unit = 2 K MySQL won't cache query results that are larger than query_cache_limit in size SORT OPERATIONS Current sort_buffer_size = 6 M Current read_rnd_buffer_size = 4 M Sort buffer seems to be fine JOINS Current join_buffer_size = 4.00 M You have had 694 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. OPEN FILES LIMIT Current open_files_limit = 6654 files The open_files_limit should typically be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_cache value = 3072 tables You have a total of 415 tables You have 2024 open tables. The table_cache value seems to be fine TEMP TABLES Current max_heap_table_size = 256 M Current tmp_table_size = 256 M Of 157752 temp tables, 22% were created on disk Created disk tmp tables ratio seems fine TABLE SCANS Current read_buffer_size = 2 M Current table scan ratio = 344 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 17 You may benefit from selective use of InnoDB.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.