GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL Optimal Settings

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL Optimal Settings

    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.

    Comment


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

      Comment


      • #4
        Are you sure your indexes are on the correct columns? indexes on columns with high null counts or low cardinality can often worsen your performance. also, if you're creating an order by on a large body of results, you'll see slow downs as sometimes the sorts will result in disk based creations of MyISAM tmp tables to order your data before sending it to your app. Make sure your query only includes the columns you need (not * if you dont need everything) and eliminate large bodies of info when possible by showing only the most recent results. If you're getting a lot of traffic you might try farming out all your select statements to a clustered server with MyISAM tables instead of INNODB table types.

        Comment


        • #5
          Thanks JGilbert for the information and replay
          1. I have only indexed the fields which is used mostly for search and these fields are NOT NULL, the orderno and customerno fields.

          2. As i mentioned in earlier post,
          There are other sub applications which checks the 'order master' and 'order detail' tables for changes, regularly for every 5 secs.
          These regular queries in this applications are slower and takes larger CPU time.

          Iam using MyISAM type Tables ,
          Is there any other setting , that i can work on.

          thanks and reply
          mimayls

          Comment


          • #6
            Hello,

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

            Also what is the size of your database?

            Comment


            • #7
              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'

              Comment


              • #8
                What are your stats for table locks? are you seeing long table lock times in your slow query log?

                Comment


                • #9
                  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.

                  Comment


                  • #10
                    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'

                    Comment


                    • #11
                      Hi JGilbert
                      Thank You

                      I checked the slowquery log , The Lock time is 0 , even for slow queries.

                      thank you
                      mimayls

                      Comment


                      • #12
                        The Lock Time field in the slow query log represents how long it had to wait to obtain a lock, not how long it held a lock. What's important is how long the query execution took and how many rows it had to look at to return the result.

                        Comment


                        • #13
                          Thank You
                          for the information linuxrunner,
                          Hoping more help

                          Thank You
                          mimayls

                          Comment


                          • #14
                            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.

                            Comment


                            • #15
                              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

                              Comment

                              Working...
                              X