my.cnf setup

[Shamim Shaik]

I have lot of update/insert queries as well as select queries with a lot of 
sorts. Please let me know what settings should I use for optimal performance. I 
am using MyISAM tables. 
The db size is 30 G. 
 
Thanks 
 
 


-
Do you Yahoo!?
 Yahoo! Small Business - Try our new resources site! 

RE: my.cnf setup

[Dathan Vance Pattishall]

> -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

Re: my.cnf setup

[Sasha Pachev]

[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

[Dathan Vance Pattishall]

> -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_packe

my.cnf setup

[rconner]

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

++--+
| Variable_name  | Value|
++--+
| Aborted_clients| 0|
| Aborted_connects   | 0|
| Bytes_received | 1083533  |
| Bytes_sent | 979790   |
| Com_admin_commands | 0|
| Com_alter_table| 0|
| Com_analyze| 0|
| Com_backup_table   | 0|
| Com_begin  | 0|
| Com_change_db  | 40   |
| Com_change_master  | 0|
| Com_check  | 0|
| Com_commit | 14   |
| Com_create_db  | 0|
| Com_create_function| 0|
| Com_create_index   | 0|
| Com_create_table   | 0|
| Com_delete | 0|
| Com_delete_multi   | 0|
| Com_drop_db| 0|
| Com_drop_function  | 0|
| Com_drop_index | 0|
| Com_drop_table | 0|
| Com_flush  | 0|
| Com_grant  | 0|
| Com_ha_close   | 0|
| Com_ha_open| 0|
| Com_ha_read| 0|
| Com_insert | 0|
| Com_insert_select  | 0|
| Com_kill   | 0|
| Com_load   | 0|
| Com_load_master_data   | 0|
| Com_load_master_table  | 0|
| Com_lock_tables| 0|
| Com_optimize   | 0|
| Com_purge  | 0|
| Com_rename_table   | 0|
| Com_repair | 0|
| Com_replace| 0|
| Com_replace_select | 0|
| Com_reset  | 0|
| Com_restore_table  | 0|
| Com_revoke | 0|
| Com_rollback   | 0|
| Com_savepoint  | 0|
| Com_select | 184  |
| Com_set_option | 28   |
| Com_show_binlog_events | 0|
| Com_show_binlogs   | 0|
| Com_show_create| 0|
| Com_show_databases | 0|
| Com_show_fields| 0|
| Com_show_grants| 0|
| Com_show_keys  | 0|
| Com_show_logs  | 0|
| Com_show_master_status | 0|
| Com_show_new_master| 0|
| Com_show_open_tables   | 0|
| Com_show_processlist   | 0|
| Com_show_slave_hosts   | 0|
| Com_show_slave_status  | 0|
| Com_show_status| 1|
| Com_show_innodb_status | 0|
| Com_show_tables| 0|
| Com_show_variables | 0|
| Com_slave_start| 0|
| Com_slave_stop | 0|
| Com_truncate   | 0|
| Com_unlock_tables  | 0|
| Com_update | 0|
| Connections| 43   |
| Created_tmp_disk_tables| 13   |
| Created_tmp_tables | 13   |
| Created_tmp_files  | 0|
| Delayed_insert_threads | 0|
| Delayed_writes | 0|
| Delayed_errors | 0|
| Flush_commands | 1|
| Handler_commit | 0|
| Handler_delete | 0|
| 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|
| 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   

Re: my.cnf Setup!!

[Michael Stassen]

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 
 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]

RE: my.cnf Setup!!

[Kirti S. Bajwa]

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]