> -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Tuesday, May 11, 2004 7:42 AM > To: Dathan Vance Pattishall > Subject: RE: my.cnf setup > > Thanks for the feedback! > > I have made the changes you suggested. I do have a question about the > slow query log though. I added it to my my.cnf file as > > ===================== > [mysqld] > > port = 3306 > log-slow-queries = /usr/local/mysql/slowlog > socket = /tmp/mysql.sock > skip-locking > key_buffer = 384M > max_allowed_packet = 1M > table_cache = 512 > sort_buffer_size = 2M > read_buffer_size = 2M > read_rnd_buffer_size = 2M > myisam_sort_buffer_size = 64M > thread_cache = 8 > query_cache_size = 32M > # Try number of CPU's*2 for thread_concurrency > thread_concurrency = 4
Increase your tmp_table_size to 32M Additionally ensure that your indexes on your tables are correct. If you need some help with that send the query + table structure to the list. > > ============================= > > Is this correct? Will it just make the file called slowlog? So far it > hasnt done anything. And i did restart the server fyi. > > Anything you can clear up? > > Conner > > > > > >> -----Original Message----- > >> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > >> Sent: Monday, May 10, 2004 2:23 PM > >> To: [EMAIL PROTECTED] > >> Subject: my.cnf setup > >> > >> I am running a mysql server off a Dell 2650. > >> Dual 2.8Ghz Intel Xeon processors > >> 1 Gig of RAM > >> > >> The MySQL data comes up rather slowly. Would like some suggestions on > >> my > >> my.cnf file if you had a minute. > >> > >> here is what I have > >> | Handler_read_first | 2 | > >> | Handler_read_key | 9080 | > >> | Handler_read_next | 35 | > >> | Handler_read_prev | 1764 | > >> | Handler_read_rnd | 723 | > >> | Handler_read_rnd_next | 63575 | > > > > You are not indexing your table right. The read random next is to high. > > Turn > > on the slow query log to find out what query is the problem. > > > > > > > >> | Handler_rollback | | > >> | Handler_update | | > >> | Handler_write | 154 | > >> | Key_blocks_used | 51 | > >> | Key_read_requests | 13370 | > >> | Key_reads | 41 | > >> | Key_write_requests | 67 | > >> | Key_writes | | > >> | Max_used_connections | 4 | > >> | Not_flushed_key_blocks | | > >> | Not_flushed_delayed_rows | | > >> | Open_tables | 18 | > >> | Open_files | 38 | > >> | Open_streams | | > >> | Opened_tables | 24 | > >> | Questions | 9971 | > >> | Qcache_queries_in_cache | 173 | > >> | Qcache_inserts | 173 | > >> | Qcache_hits | 9665 | > >> | Qcache_lowmem_prunes | | > >> | Qcache_not_cached | 8 | > >> | Qcache_free_memory | 32908680 | > >> | Qcache_free_blocks | 1 | > >> | Qcache_total_blocks | 365 | > >> | Rpl_status | NULL | > >> | Select_full_join | 8 | > >> | Select_full_range_join | | > >> | Select_range | 66 | > >> | Select_range_check | | > >> | Select_scan | 84 | > >> | Slave_open_temp_tables | | > >> | Slave_running | OFF | > >> | Slow_launch_threads | | > >> | Slow_queries | | > >> | Sort_merge_passes | | > >> | Sort_range | | > >> | Sort_rows | 723 | > >> | Sort_scan | 16 | > >> | Ssl_accepts | | > >> | Ssl_finished_accepts | | > >> | Ssl_finished_connects | | > >> | Ssl_accept_renegotiates | | > >> | Ssl_connect_renegotiates | | > >> | Ssl_callback_cache_hits | | > >> | Ssl_session_cache_hits | | > >> | Ssl_session_cache_misses | | > >> | Ssl_session_cache_timeouts | | > >> | Ssl_used_session_cache_entries | | > >> | Ssl_client_connects | | > >> | Ssl_session_cache_overflows | | > >> | Ssl_session_cache_size | | > >> | Ssl_session_cache_mode | NONE | > >> | Ssl_sessions_reused | | > >> | Ssl_ctx_verify_mode | | > >> | Ssl_ctx_verify_depth | | > >> | Ssl_verify_mode | | > >> | Ssl_verify_depth | | > >> | Ssl_version | | > >> | Ssl_cipher | | > >> | Ssl_cipher_list | | > >> | Ssl_default_timeout | | > >> | Table_locks_immediate | 239 | > >> | Table_locks_waited | | > >> | Threads_cached | 1 | > >> | Threads_created | 5 | > >> | Threads_connected | 4 | > >> | Threads_running | 1 | > >> | Uptime | 1444 | > >> +--------------------------------+----------+ > >> > >> > >> my.cnf as follows: > >> > >> > >> [client] > >> password = password > >> port = 3306 > >> socket = /tmp/mysql.sock > >> > >> # Here follows entries for some specific programs > >> > >> [EMAIL PROTECTED] bin]# cat /etc/my.cnf > >> > >> [client] > >> password = guest > >> port = 3306 > >> socket = /tmp/mysql.sock > >> > >> > >> > >> # The MySQL server > >> [mysqld] > >> port = 3306 > >> socket = /tmp/mysql.sock > >> skip-locking > >> key_buffer = 384M > >> max_allowed_packet = 1M > > > > > > Raise higher if your replicating blobs. > > > > > >> table_cache = 512 > >> sort_buffer_size = 8M > > This is too high, you might run out of memory. This is a per thread > > allocation. Set it to 2M. > > > >> read_buffer_size = 2M > > > > add read_rnd_buffer_size = 2M > > > > since your indexes are bad. > > > > > >> myisam_sort_buffer_size = 64M > >> thread_cache = 8 > >> query_cache_size = 32M > >> # Try number of CPU's*2 for thread_concurrency > >> thread_concurrency = 4 > > > > This is not needed. It only works for Solaris. > > > > > > There are some other tweaks for allocating more memory for ranges etc. > but > > they are a small boost in perf, next to fixing your table structure. > > > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
