RE: my.cnf setup
-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
RE: my.cnf setup
-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 | 0| | Handler_update | 0| | Handler_write | 154 | | Key_blocks_used| 51 | | Key_read_requests | 13370| | Key_reads | 41 | | Key_write_requests | 67 | | Key_writes | 0| | Max_used_connections | 4| | Not_flushed_key_blocks | 0| | Not_flushed_delayed_rows | 0| | Open_tables| 18 | | Open_files | 38 | | Open_streams | 0| | Opened_tables | 24 | | Questions | 9971 | | Qcache_queries_in_cache| 173 | | Qcache_inserts | 173 | | Qcache_hits| 9665 | | Qcache_lowmem_prunes | 0| | 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 | 0| | Select_range | 66 | | Select_range_check | 0| | Select_scan| 84 | | Slave_open_temp_tables | 0| | Slave_running | OFF | | Slow_launch_threads| 0| | Slow_queries | 0| | Sort_merge_passes | 0| | Sort_range | 0| | Sort_rows | 723 | | Sort_scan | 16 | | Ssl_accepts| 0| | Ssl_finished_accepts | 0| | Ssl_finished_connects | 0| | Ssl_accept_renegotiates| 0| | Ssl_connect_renegotiates | 0| | Ssl_callback_cache_hits| 0| | Ssl_session_cache_hits | 0| | Ssl_session_cache_misses | 0| | Ssl_session_cache_timeouts | 0| | Ssl_used_session_cache_entries | 0| | Ssl_client_connects| 0| | Ssl_session_cache_overflows| 0| | Ssl_session_cache_size | 0| | Ssl_session_cache_mode | NONE | | Ssl_sessions_reused| 0| | Ssl_ctx_verify_mode| 0| | Ssl_ctx_verify_depth | 0| | Ssl_verify_mode| 0| | Ssl_verify_depth | 0| | Ssl_version| | | Ssl_cipher | | | Ssl_cipher_list| | | Ssl_default_timeout| 0| | Table_locks_immediate | 239 | | Table_locks_waited | 0| | 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
Re: my.cnf setup
[EMAIL PROTECTED] wrote: 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. Conner: The problem is very unlikely your my.cnf, and very likely your application. You've done 184 selects and scanned 63575 rows. So your average select scans 345 rows, and you do have some good ones (66 in Select_range, and probably not all 84 of Select_scan are scanning large tables). It seems that you also have some really bad ones ( 8 in Select_full_join). Enable log-slow-queries and log-long-format in my.cnf, then police the slow log fixing the queries/table schema as you go along. Start with the ones that examine most rows. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: my.cnf Setup!!
Hello List: I have setup MySQL and it is working fine. I tested connection to MySQL. Now I have gone one step futher and added a password for mysql, as follows: shell ./mysqladmin -u root password mysqlpw Now I want to setup myc.cnf. Our SQL server is a standalone server (RH9, Dual CPU, 1.5GB Memory, RAID1, etc.), so I copied the my-large.cnf to /etc/my.cnf. After looking into my.cnf, I decided to leave it as it is (no change). I do want to add the (1) name of the server and (2) password to the my.cnf. Here I am struck for last couple of days. Finally, here is my question; I want to execute the command: shell mysql -h data -u mysql password=mysqlpw Where (from above command), mysql server is data and password is mysqlpw. What lines do I need to enter in my.cnf for the above command to work? I have tried everything I know! HELP!! KIrti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf Setup!!
Kirti S. Bajwa wrote: Hello List: I have setup MySQL and it is working fine. I tested connection to MySQL. Now I have gone one step futher and added a password for mysql, as follows: shell ./mysqladmin -u root password mysqlpw Now I want to setup myc.cnf. Our SQL server is a standalone server (RH9, Dual CPU, 1.5GB Memory, RAID1, etc.), so I copied the my-large.cnf to /etc/my.cnf. After looking into my.cnf, I decided to leave it as it is (no change). I do want to add the (1) name of the server and (2) password to the my.cnf. Here I am struck for last couple of days. For this, you want the .my.cnf file in your home directory. See http://www.mysql.com/doc/en/Option_files.html for details. Finally, here is my question; I want to execute the command: shell mysql -h data -u mysql password=mysqlpw Where (from above command), mysql server is data and password is mysqlpw. What lines do I need to enter in my.cnf for the above command to work? I have tried everything I know! HELP!! This should work without anything special in an option file, assuming that * mysqld server is running on the machine named data. * your hostname lookup will properly resolve data. * user [EMAIL PROTECTED], where client.machine is the name of the computer on which you are running the mysql client, has permission to access the mysqld server on data. If you could provide the exact error message you get when you try this, someone should be aable to diagnose the problem. Finally, I should point out that putting the password on the command line is insecure. It would be better to use shell mysql -h data -u mysql -p in which case you'll be prompted for the password. KIrti Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
