mysql Performance

**Aatif** · Aug 9, 2002 21:21

Hi,
I want to know how we can improve performance of mySql, we can get different variables and status by
show variables; and show status; But what do each of these variables mean, can any body tell me tutorial for that.

Basically I am facing connection problems, currently max_connections is set to 14, I think that needs to be increased, but what is the maximam limit, we have enough RAM to support.

Thanx
Aatif Malik

**DR_LaRRY_PEpPeR** · Aug 10, 2002 00:24

i love tweaking MySQL!

the manual pages for SHOW VARIABLES and SHOW STATUS do a pretty good job of explaining what each thing means. however, knowing what to look for and what to change is harder.

you say your max_connections variable is set to 14? that's weird since the default is 100. or did you mean that the Max_used_connections in SHOW STATUS is 14? how many connections would you like to be able to handle? MySQL should be able to handle 300-500 at least, depending on your server. the maximum limit should be 1000 or more depending, again, on your server and what queries are being run.

some key stats (from SHOW STATUS) to look at are:

Key_reads divided by Key_read_requests should be less than 0.01
Opened_tables -- the lower the better
Slow_queries, Threads_created, Created_tmp_tables, Created_tmp_disk_tables -- higher is worse
Max_used_connections is the most connections used at one time
Threads_connected is the amount of connections currently
Threads_running is how many of those connections are actually busy doing something (processing a query, etc.)

2 variables (from SHOW VARIABLES) that are most often adjusted (and the most important) are:

key_buffer_size -- where MySQL keeps table indexes in RAM. if this is too low, it will have to read indexes from disk rather than having them cached in RAM. if it's too high, you'll just be wasting RAM. the default is 8M. it should typically be set to about 16M.
table_cache -- tables are files. opening and closing files is expensive (one of the problems with flat-file DBs). to avoid this constant opening and closing for each query, MySQL keeps table_cache tables open continuously. each open table needs a file descriptor from the OS, so you can't go too huge on this. the default is a pathetic 64. you should be able to almost always set this to 1024, which will greatly reduce the Opened_tables stat.

depending on your RAM, you can probably increase the size of the record_buffer, join_buffer, and sort_buffer. having some value for thread_cache_size is better than the default of 0 too.

if you want me to recommend a my.cnf/my.ini file for your system, could you please post: CPU speed/type and number (single or dual), amount of RAM, operating system, output from SHOW VARIABLES, output from SHOW STATUS (after MySQL has been running for a while; at least a few hours, preferably a few days), and the current contents of your my.cnf/my.ini file, if you have one.

if you're using *nix, how are your load averages and swap usage?

hope that helps some.

**Aatif** · Aug 10, 2002 08:14

Hi,
Thanx a lot,
yes max_connections was 14, and I dont know why was it set to 14, now I have increased that to 300.
we have 1 GHz processors, dual processors and 1 GB of ram,
we are running at 60% free RAM mostly and 100% free swap space and load is 0.7.

About variables and status, these were before I increased max_connections to 14. Your suggestion please...

Show Status;
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 145 |
| Aborted_connects | 4 |
| Bytes_received | 29154091 |
| Bytes_sent | 221809143 |
| Connections | 45382 |
| Created_tmp_disk_tables | 345 |
| Created_tmp_tables | 3426 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 4902 |
| Handler_read_first | 5695 |
| Handler_read_key | 593551 |
| Handler_read_next | 21054205 |
| Handler_read_prev | 83176 |
| Handler_read_rnd | 2575519 |
| Handler_read_rnd_next | 3781652920 |
| Handler_update | 317458 |
| Handler_write | 186755 |
| Key_blocks_used | 15582 |
| Key_read_requests | 3768739 |
| Key_reads | 16820 |
| Key_write_requests | 20886 |
| Key_writes | 10203 |
| Max_used_connections | 14 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 124 |
| Open_streams | 0 |
| Opened_tables | 7419 |
| Questions | 340170 |
| Select_full_join | 1225 |
| Select_full_range_join | 0 |
| Select_range | 771 |
| Select_range_check | 0 |
| Select_scan | 87283 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 4 |
| Slow_queries | 501 |
| Sort_merge_passes | 0 |
| Sort_range | 544 |
| Sort_rows | 2642640 |
| Sort_scan | 50362 |
| Table_locks_immediate | 237620 |
| Table_locks_waited | 302 |
| Threads_cached | 0 |
| Threads_created | 45381 |
| Threads_connected | 2 |
| Threads_running | 2 |
| Uptime | 33573 |
+--------------------------+------------+
54 rows in set (0.02 sec)

Show Variables;

| Variable_name | Value |
+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| ansi_mode | OFF |
| back_log | 50 |
| basedir | /usr/local/mysql/ |
| binlog_cache_size | 32768 |
| character_set | latin1 |
| character_sets | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| datadir | /usr/local/mysql/var/ |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| have_bdb | NO |
| have_gemini | NO |
| have_innodb | NO |
| have_isam | YES |
| have_raid | NO |
| have_ssl | NO |
| init_file | |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 16773120 |
| language | /usr/local/mysql/share/mysql/english/ |
| large_files_support | ON |
| locked_in_memory | OFF |
| log | ON |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1047552 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 14 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_recover_options | OFF |
| myisam_max_extra_sort_file_size | 256 |
| myisam_max_sort_file_size | 2047 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| net_read_timeout | 30
| net_retry_count | 10 |
| net_write_timeout | 60 |
| open_files_limit | 0 |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 131072 |
| query_buffer_size | 0 |
| safe_show_database | OFF |
| server_id | 0 |
| slave_net_timeout | 3600 |
| skip_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer | 2097144 |
| table_cache | 64 |
| table_type | MYISAM |
| thread_cache_size | 0 |
| thread_stack | 131072 |
| transaction_isolation | READ-COMMITTED |
| timezone | PDT |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| version | 3.23.40-log |
| wait_timeout | 28800 |
+---------------------------------+-------------------------------------------------------------------------------------------
----------------------------------------------------------------+
80 rows in set (0.00 sec)

**DR_LaRRY_PEpPeR** · Aug 10, 2002 13:06

thanks. nice system.

you're using an older version of MySQL (3.23.40). i'd suggest upgrading to the latest version (3.23.51 right now) for bug fixes and possible performance improvments. and if you're using Linux, make sure you're using the 2.4 kernel since it runs MySQL the best.

i noticed in your variables that log is ON. this means that all queries are being logged. you shouldn't need this for any reason and it probably increases load, so it will be off with this new config file.

other than that, i increased max_connections to 450; table_cache from 64 to 1024; join_buffer and record_buffer from 128K to 1M; and thread_cache_size from 0 to 64 (this will make it so that a new thread isn't created for each connection. you may see more mysqld processes running, that's fine.)

replace your my.cnf file with the following and restart MySQL:

Code:

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = max_connections=450
set-variable = max_allowed_packet=16M
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=64
set-variable = wait_timeout=600

[safe_mysqld]
open-files-limit = 8192

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql] 
no-auto-rehash

[mysqlcheck]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

[mysqlhotcopy]
interactive-timeout

that should do pretty good for you. hope it helps.