MySQL Optimal Settings

MySQL & MariaDB Other MySQL® Questions
1

Hi all

I need all your advices regarding the performance of my Database. Our database was running fine and now it is lagging when there are more users.

Let me explain

We are using MySQL 4.0

1.We have tables having records more than 100,000

2.Our tables are growing by min 200 records per day

3.We have applied index for frequent usage and huge table.

4.We have modified the my.ini file to use the max resources

5.Our database server is running on a Windows 2000 server with 1 GB RAM

Still , the site is lagging and the MySql database residing server is showing 100% CPU usage.

So please, help me if our procedure are correct and any thing we are missing.

All advices are highly valued and please help

Thank you all
mimayla

2

I would start with the slow query log to see whats happening.

3

Thanks linuxrunner for the replay )

1.Yes i do checked slow queries,
2.I have few queries of 3-5 seconds execution time.
3.I have optimized some of them
4.The are 2 tables , one OrderMaster, and other OrderDetail
the SQL running on this tables are slower.

5.They have index for the search fields in this tables
6.The master tbl have 116,524 Recs,
and detail tbl have 453,465 recs
7.And also we have applications accessing these tables in
regular time interval
8. So these tables are busy.
9.Since these tables have all data , and we only use data not
older than 3 months ,
10.Is it a best idea to remove those unused data to archive DB.
11.Thus making these tables less weight
12.Will produce better response time
13 Or is there any other setting or consideration we have to change to get better response time and reduce CPU usage.

expecting the reply soon , pls

thank you
mimayls

4

Hello,

can you post output of SHOW VARIABLES and SHOW GLOBAL STATUS?

Also what is the size of your database?

5

Hai tanj

The database size is about 120 MB
The MySQL version is 4.0.17

expecting your reply
Thank You
mimayls

This are the variables

Show Variables Listing

‘Variable_name’,‘Value’

‘back_log’,‘50’
‘basedir’,‘C:\mysql'
‘binlog_cache_size’,‘32768’
‘bulk_insert_buffer_size’,‘8388608’
‘character_set’,‘latin1’
‘character_sets’,‘latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis 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’
‘convert_character_set’,’’
‘datadir’,‘C:\mysql\data'
‘default_week_format’,‘0’
‘delay_key_write’,‘ON’
‘delayed_insert_limit’,‘100’
‘delayed_insert_timeout’,‘300’
‘delayed_queue_size’,‘1000’
‘flush’,‘OFF’
‘flush_time’,‘1800’
‘ft_boolean_syntax’,’+ -><()~*:“”&|’
‘ft_min_word_len’,‘4’
‘ft_max_word_len’,‘254’
‘ft_max_word_len_for_sort’,‘20’
‘ft_stopword_file’,‘(built-in)’
‘have_bdb’,‘NO’
‘have_crypt’,‘NO’
‘have_innodb’,‘YES’
‘have_isam’,‘YES’
‘have_raid’,‘NO’
‘have_symlink’,‘YES’
‘have_openssl’,‘NO’
‘have_query_cache’,‘YES’
‘init_file’,‘’
‘innodb_additional_mem_pool_size’,‘1048576’
‘innodb_buffer_pool_size’,‘8388608’
‘innodb_data_file_path’,‘ibdata1:10M:autoextend’
‘innodb_data_home_dir’,‘’
‘innodb_file_io_threads’,‘4’
‘innodb_force_recovery’,‘0’
‘innodb_thread_concurrency’,‘8’
‘innodb_flush_log_at_trx_commit’,‘1’
‘innodb_fast_shutdown’,‘ON’
‘innodb_flush_method’,‘’
‘innodb_lock_wait_timeout’,‘50’
‘innodb_log_arch_dir’,‘.'
‘innodb_log_archive’,‘OFF’
‘innodb_log_buffer_size’,‘1048576’
‘innodb_log_file_size’,‘5242880’
‘innodb_log_files_in_group’,‘2’
‘innodb_log_group_home_dir’,’.'
‘innodb_mirrored_log_groups’,‘1’
‘innodb_max_dirty_pages_pct’,‘90’
‘interactive_timeout’,‘28800’
‘join_buffer_size’,‘131072’
‘key_buffer_size’,‘262144000’
‘language’,'C:\mysql\share\english'
‘large_files_support’,‘ON’
‘local_infile’,‘ON’
‘log’,‘OFF’
‘log_update’,‘OFF’
‘log_bin’,‘OFF’
‘log_slave_updates’,‘OFF’
‘log_slow_queries’,‘OFF’
‘log_warnings’,‘OFF’
‘long_query_time’,‘10’
‘low_priority_updates’,‘OFF’
‘lower_case_table_names’,‘ON’
‘max_allowed_packet’,‘1047552’
‘max_binlog_cache_size’,‘4294967295’
‘max_binlog_size’,‘1073741824’
‘max_connections’,‘100’
‘max_connect_errors’,‘10’
‘max_delayed_threads’,‘20’
‘max_heap_table_size’,‘16777216’
‘max_join_size’,‘4294967295’
‘max_relay_log_size’,‘0’
‘max_seeks_for_key’,‘4294967295’
‘max_sort_length’,‘1024’
‘max_user_connections’,‘0’
‘max_tmp_tables’,‘32’
‘max_write_lock_count’,‘4294967295’
‘myisam_max_extra_sort_file_size’,‘268435456’
‘myisam_max_sort_file_size’,‘2147483647’
‘myisam_repair_threads’,‘1’
‘myisam_recover_options’,‘OFF’
‘myisam_sort_buffer_size’,‘67108864’
‘named_pipe’,‘OFF’
‘net_buffer_length’,‘16384’
‘net_read_timeout’,‘30’
‘net_retry_count’,‘10’
‘net_write_timeout’,‘60’
‘new’,‘OFF’
‘open_files_limit’,‘0’
‘port’,‘3306’
‘protocol_version’,‘10’
‘query_alloc_block_size’,‘8192’
‘query_cache_limit’,‘1048576’
‘query_cache_size’,‘262144000’
‘query_cache_type’,‘ON’
‘query_prealloc_size’,‘8192’
‘range_alloc_block_size’,‘2048’
‘read_buffer_size’,‘2093056’
‘read_only’,‘OFF’
‘read_rnd_buffer_size’,‘262144’
‘rpl_recovery_rank’,‘0’
‘server_id’,‘0’
‘slave_net_timeout’,‘3600’
‘skip_external_locking’,‘ON’
‘skip_networking’,‘OFF’
‘skip_show_database’,‘OFF’
‘slow_launch_time’,‘2’
‘sort_buffer_size’,‘2097144’
‘sql_mode’,‘0’
‘table_cache’,‘512’
‘table_type’,‘MYISAM’
‘thread_cache_size’,‘8’
‘thread_stack’,‘196608’
‘tx_isolation’,‘REPEATABLE-READ’
‘timezone’,‘Arab Standard Time’
‘tmp_table_size’,‘33554432’
‘tmpdir’,'C:\WINNT\TEMP'
‘transaction_alloc_block_size’,‘8192’
‘transaction_prealloc_size’,‘4096’
‘version’,‘4.0.17-nt’
‘version_comment’,‘Source distribution’
‘wait_timeout’,‘28800’

6

Hello,

thanks for posting Status variables. Can you also post SHOW STATUS output so I would see where the performance bottlenecks could be?

120MB is quite small size for database and I’m used to play with tables having 4 million records or more. So it should be no problem for your config )

I suspect that your database usage may have bad joins or table scans. But I need output of SHOW STATUS or slow query log for this.

Thanks.

7

Hi tanj
Thank tanj
Below are the Status values , these values not at peak , took them at morning .

Query:

show status

‘Variable_name’,‘Value’
‘Aborted_clients’,‘101’
‘Aborted_connects’,‘5’
‘Bytes_received’,‘29409510’
‘Bytes_sent’,‘219532571’
‘Com_admin_commands’,‘2’
‘Com_alter_table’,‘0’
‘Com_analyze’,‘0’
‘Com_backup_table’,‘0’
‘Com_begin’,‘1’
‘Com_change_db’,‘113494’
‘Com_change_master’,‘0’
‘Com_check’,‘0’
‘Com_commit’,‘814’
‘Com_create_db’,‘0’
‘Com_create_function’,‘0’
‘Com_create_index’,‘0’
‘Com_create_table’,‘0’
‘Com_delete’,‘1’
‘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’,‘1738’
‘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’,‘95’
‘Com_savepoint’,‘0’
‘Com_select’,‘37405’
‘Com_set_option’,‘1743’
‘Com_show_binlog_events’,‘0’
‘Com_show_binlogs’,‘0’
‘Com_show_create’,‘36’
‘Com_show_databases’,‘2’
‘Com_show_fields’,‘72’
‘Com_show_grants’,‘0’
‘Com_show_keys’,‘36’
‘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’,‘10’
‘Com_show_innodb_status’,‘0’
‘Com_show_tables’,‘40’
‘Com_show_variables’,‘0’
‘Com_slave_start’,‘0’
‘Com_slave_stop’,‘0’
‘Com_truncate’,‘0’
‘Com_unlock_tables’,‘0’
‘Com_update’,‘2708’
‘Connections’,‘3600’
‘Created_tmp_disk_tables’,‘1222’
‘Created_tmp_tables’,‘3843’
‘Created_tmp_files’,‘0’
‘Delayed_insert_threads’,‘0’
‘Delayed_writes’,‘0’
‘Delayed_errors’,‘0’
‘Flush_commands’,‘1’
‘Handler_commit’,‘0’
‘Handler_delete’,‘1’
‘Handler_read_first’,‘1602’
‘Handler_read_key’,‘481833’
‘Handler_read_next’,‘1953645’
‘Handler_read_prev’,‘0’
‘Handler_read_rnd’,‘584567’
‘Handler_read_rnd_next’,‘3563059479’
‘Handler_rollback’,‘0’
‘Handler_update’,‘418271’
‘Handler_write’,‘540143’
‘Key_blocks_used’,‘5365’
‘Key_read_requests’,‘1158956’
‘Key_reads’,‘5337’
‘Key_write_requests’,‘222’
‘Key_writes’,‘143’
‘Max_used_connections’,‘51’
‘Not_flushed_key_blocks’,‘0’
‘Not_flushed_delayed_rows’,‘0’
‘Open_tables’,‘58’
‘Open_files’,‘94’
‘Open_streams’,‘0’
‘Opened_tables’,‘1377’
‘Questions’,‘238613’
‘Qcache_queries_in_cache’,‘299’
‘Qcache_inserts’,‘18108’
‘Qcache_hits’,‘76908’
‘Qcache_lowmem_prunes’,‘0’
‘Qcache_not_cached’,‘19298’
‘Qcache_free_memory’,‘261074480’
‘Qcache_free_blocks’,‘129’
‘Qcache_total_blocks’,‘771’
‘Rpl_status’,‘NULL’
‘Select_full_join’,‘606’
‘Select_full_range_join’,‘0’
‘Select_range’,‘0’
‘Select_range_check’,‘0’
‘Select_scan’,‘26134’
‘Slave_open_temp_tables’,‘0’
‘Slave_running’,‘OFF’
‘Slow_launch_threads’,‘44’
‘Slow_queries’,‘9’
‘Sort_merge_passes’,‘0’
‘Sort_range’,‘0’
‘Sort_rows’,‘199459’
‘Sort_scan’,‘4407’
‘Table_locks_immediate’,‘89172’
‘Table_locks_waited’,‘2177’
‘Threads_cached’,‘1’
‘Threads_created’,‘241’
‘Threads_connected’,‘35’
‘Threads_running’,‘1’
‘Uptime’,‘97491’

8

Hello mimayls

Thanks for the status values, it proves instructive.

‘Handler_read_key’,‘481833’
‘Handler_read_rnd_next’,‘3563059479’

Is showing that your queries are doing, most of the time, table scans instead of index reads. Looks like you’ll have to review most of your queries’ execution plans (i.e. take the queries and EXPLAIN on them). Maybe you will want to post some examples of query and EXPLAIN query so I could give you more advice.

About the configuration, you might want to increase the tmp_table_size to 64M (it shows that you have a lot of on-disk temporary tables created)

Also I see that your query cache size is 256M, but only 1M is used. It’s way too large. In regards to your database size I recommend that you set query_cache_size to 8M, so you will free unnecessary reserved memory.

Regards.

9

Hai tanj
Thank you for the suggections.

Can you please tell me
1.why query cache is used only 1MB,
Because query are small size Or There is another problem

2.Why there are large no. of tmp tables
This is the problem of the unoptimized queries?
Lack of indexes in the table

please guid me

Thank You
mimayls

10

I am wondering about the box… 1Gb, Windows 2000, 100% CPU!

100% CPU may happen when:

  • paging is used due to not enough RAM
  • a lot of calculations such as in-memory sort (bad database schema)

Database is small (possibly 100 Mb on hard drive, isn’t it?); 200 new records per day should not overload anything.

I’d check the box at first… And the schema. Everything must work with default ‘tuning parameters’. Do you run any other applications on the same Windows box? Is RAM enough? Can you check Windows Task Manager?

Thanks

11

3.We have applied index for frequent usage and huge table.

You should apply indexes as needed… and remove indexes not needed. Table size and usage frequency does not play any role; analyze all SQL statements, and check with EXPLAIN http://dev.mysql.com/doc/refman/5.0/en/explain.html

12

Thank you bambarbia
Sure we have to review our Schema and the Queries.
No other application running on the server
The Server is same as mentioned earlier
We have multilpe client applications accessing the server regularly for every 10 second.This applications checks ordermaster table.

Can u please check the system variables , if they are correct.

Thank you
mimayls