(Note) Note (2018)

Налаштування сервера для проєкту Bravo.

У данному випадку замовник мав бажання розмістити сервер у Росії. Це було пов'язано з тим, що люди в нього працювали з Росії, безпосередньо зі своїх кампутерів та через окремий RDP-логін, який я налаштував для них. Я взяв VDS з двома гігами пам'яті та додатковим окремим SSD-діском. На цьому VDS-сервері працює MySQL та кабінет для робітників цього замовника.



На скринах нище я ставлю у кабінет юзерів чергову версію моєї софтінки. Ця софтінка може ставитися й на кампутери юзерів, але передбачена можливість заходити з малопотужних та повільних кампутерів безпосередньо на сервер по RDP.



Зверніть увагу на третьому скрині вище, що було зручно розгортати увесь мій софт я додав у MSI-пакет усі необхідні залежності для роботи з MySQL за допомогою Entity Framework.



На цьому сервері у мене налаштований IIS з моїми сайтами.



А ось власне панель MySQL.



Але головне, що працює на цьому сервері - це MySQL.



Сам по собі сервер та My.INI розташовані на диску C:\, а бази я розмістив на диску E:\



Головну увагу прі налаштуванні цього MySQL-серверу я приділяв різноманітним таймаутам та розміру пакету для спілкування з клієнтами.


   1:  # Other default tuning values
   2:  # MySQL Server Instance Configuration File
   3:  # ----------------------------------------------------------------------
   4:  # Generated by the MySQL Server Instance Configuration Wizard
   5:  #
   6:  #
   7:  # Installation Instructions
   8:  # ----------------------------------------------------------------------
   9:  #
  10:  # On Linux you can copy this file to /etc/my.cnf to set global options,
  11:  # mysql-data-dir/my.cnf to set server-specific options
  12:  # (@localstatedir@ for this installation) or to
  13:  # ~/.my.cnf to set user-specific options.
  14:  #
  15:  # On Windows you should keep this file in the installation directory 
  16:  # of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y). To
  17:  # make sure the server reads the config file use the startup option 
  18:  # "--defaults-file". 
  19:  #
  20:  # To run the server from the command line, execute this in a 
  21:  # command line shell, e.g.
  22:  # mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
  23:  #
  24:  # To install the server as a Windows service manually, execute this in a 
  25:  # command line shell, e.g.
  26:  # mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
  27:  #
  28:  # And then execute this in a command line shell to start the server, e.g.
  29:  # net start MySQLXY
  30:  #
  31:  #
  32:  # Guidelines for editing this file
  33:  # ----------------------------------------------------------------------
  34:  #
  35:  # In this file, you can use all long options that the program supports.
  36:  # If you want to know the options a program supports, start the program
  37:  # with the "--help" option.
  38:  #
  39:  # More detailed information about the individual options can also be
  40:  # found in the manual.
  41:  #
  42:  # For advice on how to change settings please see
  43:  # https://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
  44:  #
  45:  #
  46:  # CLIENT SECTION
  47:  # ----------------------------------------------------------------------
  48:  #
  49:  # The following options will be read by MySQL client applications.
  50:  # Note that only client applications shipped by MySQL are guaranteed
  51:  # to read this section. If you want your own MySQL client program to
  52:  # honor these values, you need to specify it as an option during the
  53:  # MySQL client library initialization.
  54:  #
  55:  [client]
  56:   
  57:  # pipe=
  58:   
  59:  # socket=MYSQL
  60:   
  61:  port=3306
  62:  interactive_timeout = 28800
  63:  interactive_timeout = 28800
  64:   
  65:  [mysql]
  66:  no-beep
  67:   
  68:  # default-character-set=
  69:   
  70:  # SERVER SECTION
  71:  # ----------------------------------------------------------------------
  72:  #
  73:  # The following options will be read by the MySQL Server. Make sure that
  74:  # you have installed the server correctly (see above) so it reads this 
  75:  # file.
  76:  #
  77:  interactive_timeout = 28800
  78:  interactive_timeout = 28800
  79:  # server_type=2
  80:  [mysqld]
  81:   
  82:  # The next three options are mutually exclusive to SERVER_PORT below.
  83:  # skip-networking
  84:  # enable-named-pipe
  85:  # shared-memory
  86:   
  87:  # shared-memory-base-name=MYSQL
  88:   
  89:  # The Pipe the MySQL Server will use
  90:  # socket=MYSQL
  91:   
  92:  # The TCP/IP Port the MySQL Server will listen on
  93:  port=3306
  94:   
  95:  # Path to installation directory. All paths are usually resolved relative to this.
  96:  # basedir="C:/Program Files/MySQL/MySQL Server 8.0/"
  97:   
  98:  # Path to the database root
  99:  datadir=E:/DB/DATA
 100:   
 101:  # The default character set that will be used when a new schema or table is
 102:  # created and no character set is defined
 103:  # character-set-server=
 104:   
 105:  # The default authentication plugin to be used when connecting to the server
 106:  default_authentication_plugin=caching_sha2_password
 107:   
 108:  # The default storage engine that will be used when create new tables when
 109:  default-storage-engine=INNODB
 110:   
 111:  # Set the SQL mode to strict
 112:  sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
 113:   
 114:  # General and Slow logging.
 115:  log-output=FILE
 116:  general-log=0
 117:  general_log_file="WIN-OGLTGVN0FA0.log"
 118:  slow-query-log=1
 119:  slow_query_log_file="E:/DB/log.txt"
 120:  long_query_time=10
 121:   
 122:  # Binary Logging.
 123:  log-bin="E:/DB/Bin-log"
 124:   
 125:  # Error Logging.
 126:  log-error="E:/DB/Err.log"
 127:   
 128:  # Server Id.
 129:  server-id=1
 130:   
 131:  # Indicates how table and database names are stored on disk and used in MySQL.
 132:  # Value = 0: Table and database names are stored on disk using the lettercase specified in the
 133:  #            CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive.
 134:  #            You should not set this variable to 0 if you are running MySQL on a system that has
 135:  #            case-insensitive file names (such as Windows or macOS).
 136:  # Value = 1: Table names are stored in lowercase on disk and name comparisons are not
 137:  #            case-sensitive. MySQL converts all table names to lowercase on storage and lookup.
 138:  #            This behavior also applies to database names and table aliases.
 139:  # Value = 3, Table and database names are stored on disk using the lettercase specified in the
 140:  #            CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on
 141:  #            lookup. Name comparisons are not case sensitive. This works only on file systems
 142:  #            that are not case-sensitive! InnoDB table names and view names are stored in
 143:  #            lowercase, as for Value = 1.
 144:  # NOTE: lower_case_table_names can only be configured when initializing the server.
 145:  #       Changing the lower_case_table_names setting after the server is initialized is prohibited.
 146:  lower_case_table_names=1
 147:   
 148:  # Secure File Priv.
 149:  secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"
 150:   
 151:  # The maximum amount of concurrent sessions the MySQL server will
 152:  # allow. One of these connections will be reserved for a user with
 153:  # SUPER privileges to allow the administrator to login even if the
 154:  # connection limit has been reached.
 155:  max_connections=1000
 156:   
 157:  # The number of open tables for all threads. Increasing this value
 158:  # increases the number of file descriptors that mysqld requires.
 159:  # Therefore you have to make sure to set the amount of open files
 160:  # allowed to at least 4096 in the variable "open-files-limit" in
 161:  # section [mysqld_safe]
 162:  table_open_cache=2000
 163:   
 164:  # Maximum size for internal (in-memory) temporary tables. If a table
 165:  # grows larger than this value, it is automatically converted to disk
 166:  # based table This limitation is for a single table. There can be many
 167:  # of them.
 168:  tmp_table_size=42M
 169:   
 170:  # How many threads we should keep in a cache for reuse. When a client
 171:  # disconnects, the client's threads are put in the cache if there aren't
 172:  # more than thread_cache_size threads from before.  This greatly reduces
 173:  # the amount of thread creations needed if you have a lot of new
 174:  # connections. (Normally this doesn't give a notable performance
 175:  # improvement if you have a good thread implementation.)
 176:  thread_cache_size=10
 177:   
 178:  #*** MyISAM Specific options
 179:  # The maximum size of the temporary file MySQL is allowed to use while
 180:  # recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
 181:  # If the file-size would be bigger than this, the index will be created
 182:  # through the key cache (which is slower).
 183:  myisam_max_sort_file_size=100G
 184:   
 185:  # If the temporary file used for fast index creation would be bigger
 186:  # than using the key cache by the amount specified here, then prefer the
 187:  # key cache method.  This is mainly used to force long character keys in
 188:  # large tables to use the slower key cache method to create the index.
 189:  myisam_sort_buffer_size=75M
 190:   
 191:  # Size of the Key Buffer, used to cache index blocks for MyISAM tables.
 192:  # Do not set it larger than 30% of your available memory, as some memory
 193:  # is also required by the OS to cache rows. Even if you're not using
 194:  # MyISAM tables, you should still set it to 8-64M as it will also be
 195:  # used for internal temporary disk tables.
 196:  key_buffer_size=8M
 197:   
 198:  # Size of the buffer used for doing full table scans of MyISAM tables.
 199:  # Allocated per thread, if a full scan is needed.
 200:  read_buffer_size=64K
 201:   
 202:  read_rnd_buffer_size=256K
 203:   
 204:  #*** INNODB Specific options ***
 205:  # innodb_data_home_dir=
 206:   
 207:  # Use this option if you have a MySQL server with InnoDB support enabled
 208:  # but you do not plan to use it. This will save memory and disk space
 209:  # and speed up some things.
 210:  # skip-innodb
 211:   
 212:  # If set to 1, InnoDB will flush (fsync) the transaction logs to the
 213:  # disk at each commit, which offers full ACID behavior. If you are
 214:  # willing to compromise this safety, and you are running small
 215:  # transactions, you may set this to 0 or 2 to reduce disk I/O to the
 216:  # logs. Value 0 means that the log is only written to the log file and
 217:  # the log file flushed to disk approximately once per second. Value 2
 218:  # means the log is written to the log file at each commit, but the log
 219:  # file is only flushed to disk approximately once per second.
 220:  innodb_flush_log_at_trx_commit=1
 221:   
 222:  # The size of the buffer InnoDB uses for buffering log data. As soon as
 223:  # it is full, InnoDB will have to flush it to disk. As it is flushed
 224:  # once per second anyway, it does not make sense to have it very large
 225:  # (even with long transactions).
 226:  innodb_log_buffer_size=1M
 227:   
 228:  # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
 229:  # row data. The bigger you set this the less disk I/O is needed to
 230:  # access data in tables. On a dedicated database server you may set this
 231:  # parameter up to 80% of the machine physical memory size. Do not set it
 232:  # too large, though, because competition of the physical memory may
 233:  # cause paging in the operating system.  Note that on 32bit systems you
 234:  # might be limited to 2-3.5G of user level memory per process, so do not
 235:  # set it too high.
 236:  innodb_buffer_pool_size=8M
 237:   
 238:  # Size of each log file in a log group. You should set the combined size
 239:  # of log files to about 25%-100% of your buffer pool size to avoid
 240:  # unneeded buffer pool flush activity on log file overwrite. However,
 241:  # note that a larger logfile size will increase the time needed for the
 242:  # recovery process.
 243:  innodb_log_file_size=48M
 244:   
 245:  # Number of threads allowed inside the InnoDB kernel. The optimal value
 246:  # depends highly on the application, hardware as well as the OS
 247:  # scheduler properties. A too high value may lead to thread thrashing.
 248:  innodb_thread_concurrency=8
 249:   
 250:  # The increment size (in MB) for extending the size of an auto-extend InnoDB system tablespace file when it becomes full.
 251:  innodb_autoextend_increment=64
 252:   
 253:  # The number of regions that the InnoDB buffer pool is divided into.
 254:  # For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency,
 255:  # by reducing contention as different threads read and write to cached pages.
 256:  innodb_buffer_pool_instances=8
 257:   
 258:  # Determines the number of threads that can enter InnoDB concurrently.
 259:  innodb_concurrency_tickets=5000
 260:   
 261:  # Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before
 262:  # it can be moved to the new sublist.
 263:  innodb_old_blocks_time=1000
 264:   
 265:  # It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is 10.
 266:  innodb_open_files=300
 267:   
 268:  # When this variable is enabled, InnoDB updates statistics during metadata statements.
 269:  innodb_stats_on_metadata=0
 270:   
 271:  # When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table
 272:  # in a separate .ibd file, rather than in the system tablespace.
 273:  innodb_file_per_table=1
 274:   
 275:  # Use the following list of values: 0 for crc32, 1 for strict_crc32, 2 for innodb, 3 for strict_innodb, 4 for none, 5 for strict_none.
 276:  innodb_checksum_algorithm=0
 277:   
 278:  # The number of outstanding connection requests MySQL can have.
 279:  # This option is useful when the main MySQL thread gets many connection requests in a very short time.
 280:  # It then takes some time (although very little) for the main thread to check the connection and start a new thread.
 281:  # The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily
 282:  # stops answering new requests.
 283:  # You need to increase this only if you expect a large number of connections in a short period of time.
 284:  back_log=80
 285:   
 286:  # If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and
 287:  # synchronize unflushed data to disk.
 288:  # This option is best used only on systems with minimal resources.
 289:  flush_time=0
 290:   
 291:  # The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use
 292:  # indexes and thus perform full table scans.
 293:  join_buffer_size=256K
 294:   
 295:  # The maximum size of one packet or any generated or intermediate string, or any parameter sent by the
 296:  # mysql_stmt_send_long_data() C API function.
 297:  max_allowed_packet=64M
 298:   
 299:  # If more than this many successive connection requests from a host are interrupted without a successful connection,
 300:  # the server blocks that host from performing further connections.
 301:  max_connect_errors=100
 302:   
 303:  # Changes the number of file descriptors available to mysqld.
 304:  # You should try increasing the value of this option if mysqld gives you the error "Too many open files".
 305:  open_files_limit=4161
 306:   
 307:  # If you see many sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the
 308:  # sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization
 309:  # or improved indexing.
 310:  sort_buffer_size=256K
 311:   
 312:  # The number of table definitions (from .frm files) that can be stored in the definition cache.
 313:  # If you use a large number of tables, you can create a large table definition cache to speed up opening of tables.
 314:  # The table definition cache takes less space and does not use file descriptors, unlike the normal table cache.
 315:  # The minimum and default values are both 400.
 316:  table_definition_cache=1400
 317:   
 318:  # Specify the maximum size of a row-based binary log event, in bytes.
 319:  # Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256.
 320:  binlog_row_event_max_size=8K
 321:   
 322:  # If the value of this variable is greater than 0, a replication slave synchronizes its master.info file to disk.
 323:  # (using fdatasync()) after every sync_master_info events.
 324:  sync_master_info=10000
 325:   
 326:  # If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk.
 327:  # (using fdatasync()) after every sync_relay_log writes to the relay log.
 328:  sync_relay_log=10000
 329:   
 330:  # If the value of this variable is greater than 0, a replication slave synchronizes its relay-log.info file to disk.
 331:  # (using fdatasync()) after every sync_relay_log_info transactions.
 332:  sync_relay_log_info=10000
 333:   
 334:  # Load mysql plugins at start."plugin_x ; plugin_y".
 335:  # plugin_load
 336:   
 337:  # The TCP/IP Port the MySQL Server X Protocol will listen on.
 338:  loose_mysqlx_port=33060
 339:   
 340:  interactive_timeout = 28800
 341:  connect_timeout = 28800
 342:  net_write_timeout = 28800
 343:  net_read_timeout = 28800
 344:   
 345:   
 346:   


Comments ( )
<00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17>  <18>  <19>  <20>  <21>  <22>  <23
Link to this page: //www.vb-net.com/BravoServer/index.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>