Настройки MySQL на локалке



  • Собственно есть локальная база на OpenServer
    120 коннектов нормально держит, с моим конфигом, в котором только колличество коннектов подправил. Но при 150 потоках подвисает сама база.
    Как оптимизировать настройки ?
    знаю тут есть прошареные люди.
    Мой конфиг:

    [client]
    
    port		                    = %mysqlport%
    character_sets_dir              = "%dprogdir%\\modules\\database\\%mysql_driver%\\share\\charsets"
    
    [mysql]
    
    no-auto-rehash
    no-beep
    character_sets_dir              = "%dprogdir%\\modules\\database\\%mysql_driver%\\share\\charsets"
    
    [mysqld]
    
    # Required Settings
    
    basedir                         = "%dprogdir%\\modules\\database\\%mysql_driver%"
    bind-address                    = %ip%
    character_sets_dir              = "%dprogdir%\\modules\\database\\%mysql_driver%\\share\\charsets"
    character_set_server            = utf8mb4
    collation_server                = utf8mb4_0900_ai_ci
    datadir                         = "%dprogdir%\\userdata\\%mysql_driver%"
    default_authentication_plugin   = mysql_native_password
    default_storage_engine          = InnoDB
    explicit_defaults_for_timestamp = 1
    ft_min_word_len                 = 3
    local_infile                    = 0
    lower_case_table_names          = 1
    max_allowed_packet              = 256M
    mysqlx                          = 0
    pid_file                        = "%dprogdir%\\userdata\\temp\\mysql.pid"
    port                            = %mysqlport%
    secure-file-priv                = "%dprogdir%\\userdata\\temp\\upload"
    #skip_name_resolve              = 1
    skip-ssl
    tmpdir                          = "%dprogdir%\\userdata\\temp"
    
    # Buffer Settings
    
    bulk_insert_buffer_size         = 8M
    join_buffer_size                = 2M
    read_buffer_size                = 2M
    read_rnd_buffer_size            = 4M
    sort_buffer_size                = 2M
    
    # Connection Settings
    
    max_connections                 = 640
    max_connect_errors              = 320
    back_log                        = 128
    thread_cache_size               = 8
    interactive_timeout             = 180
    wait_timeout                    = 180
    
    # InnoDB Settings
    
    innodb_adaptive_hash_index      = 0
    innodb_buffer_pool_instances    = 1
    innodb_buffer_pool_size         = 128M
    innodb_data_file_path           = ibdata1:10M:autoextend
    innodb_data_home_dir            = "%dprogdir%\\userdata\\%mysql_driver%"
    innodb_file_per_table           = 1
    #innodb_force_recovery          = 1
    innodb_log_file_size            = 64M
    innodb_read_io_threads          = 8
    #innodb_thread_concurrency      = 4
    innodb_write_io_threads         = 8
    
    # Logging
    
    %log%general_log                = 1
    %log%general_log_file           = "%dprogdir%\\userdata\\logs\\%mysql_driver%_queries.log"
    log_error                       = "%dprogdir%\\userdata\\logs\\%mysql_driver%_error.log"
    skip-log-bin
    #log_queries_not_using_indexes  = 1
    #long_query_time                = 5
    #slow_query_log                 = 1
    #slow_query_log_file            = "%dprogdir%\\userdata\\logs\\%mysql_driver%_slow.log"
    
    # MyISAM Settings
    
    key_buffer_size                 = 32M
    myisam_max_sort_file_size       = 256M
    myisam_recover_options          = backup,force
    
    # Table Settings
    
    table_definition_cache          = 10000
    table_open_cache                = 10000
    open_files_limit                = 60000
    max_heap_table_size             = 128M
    tmp_table_size                  = 128M
    
    [mysqldump]
    
    quick
    quote_names
    max_allowed_packet              = 16M
    
    [myisamchk]
    
    key_buffer_size                 = 64M
    sort_buffer_size                = 64M
    read_buffer                     = 2M
    write_buffer                    = 2M
    
    [mysqld_safe]
    
    open_files_limit                = 60000
    
    

    Запросы вроде не очень тяжелые, типо where ... and ... and ... rnd limit 1


Log in to reply
 

  • 8
  • 17
  • 1
  • 3
  • 33
  • 15
  • 11
  • 3