Mysql My. ini

Mysql My. ini detailed description of client section: [client] port = 3306 Port = 3306 [mysql] default-character-set = latin1 default character encoding = latin1 server section Server chapter [mysqld] # The TCP/IP port the MySQL SERVER will listen on TCP/IP Port listens to mysql port = 3306 # Path to installation directory. all paths are usually resolved relative to this. installation directory. All paths are relative paths of the path. Basedir = "E:/programfile/MySql5.1/" # Path to the root Path of the database root database data datadir = "C: /ProgramData/MySQL Server 5.1/Data/"# The default character set that will be used when a new schema or table is # created and no character set is defined default character set encoding the character set is not specified when a new schema or table is created. Default-character-set = latin1 # The default storage engine that will be used when create new tables when default storage engine acts on when creating a new table default-storage-engine = INNODB # Set the SQL mode to strict settings of the SQL model SQL-mode = "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION "# The maximum amount of concurrent sessions the MySQL server will # allow. one of these connections will be reserved for a user # SUPER privileges to allow the administrator to login even if the # connection limit has been reached. the maximum number of concurrent connections allowed by the Mysql server. One of the connections is left to the user to connect to the database with super permissions, even if the maximum number of connections has been reached max_connections = 100 # Query cache is used to cache SELECT results and later return them # without actual executing the same query once again. having the query # cache enabled may result in significant speed improvements, if your # have a lot Of identical queries and rarely changing tables. see the # "Qcache_lowmem_prunes" status variable to check if the current value # is high enough for your load. # Note: In case your tables change very often or if your queries are # textually different every time, the query cache may result in a # slowdown instead of a performance improvement. the query cache is used to SELECT the results in the cache and returns results that are not actually queried from the database. The query results from the cache will significantly increase the query speed. If you have many identical queries and rarely change the table structure. Check the "Qcache_lowmem_prunes" status variable to check whether the current value is high enough for your load. NOTE: If your table structure is changed frequently or you have different queries each time, using the query cache results may not be a performance improvement. Query_cache_size = 15 M # The number of open tables for all threads. increasing this value # increases the number of file descriptors that mysqld requires. # Therefore you have to make sure to set the amount of open files # allowed to at least 4096 in the variable "open-files-limit" in # section [mysqld_safe] All threads open the number of tables. Table_cache = 256 # Maximum size for internal (in-memory) temporary tables. if a table # grows larger than this value, it is automatically converted to disk # based table This limitation is for a single table. there can be used # of them. maximum memory size of the temporary table. If a table grows larger than this value, it is automatically converted to a disk, which only limits the size of a single table, there can be many tmp_table_size = 18 M # How many threads we shoshould keep in a cache for reuse. when a client # disconnects, the client's threads are put in the cache if there aren't # more than thread_cache_size threads from before. this greatly reduces # the amount of thread creations needed if you have a lot of new # connections. (Normally this doesn' t give a notable variable C E # improvement if you have a good thread implementation.) There are many threads that we should keep in a cache for reuse. Before the thread cache size exceeds, when a client is disconnected, put the client thread in the cache. If you have many new connections, this greatly reduces the number of threads required. (If you have a good thread improvement implementation solution, this will not provide a significant performance) thread_cache_size = 8 # *** special MyISAM Specific options MYISAM configuration # The maximum size of the temporary file MySQL is allowed to use while # recreating the index (during REPAIR, alter table or load data infile. # If the file-size wocould be bigger than this, the index will be created # through the key cache (which is slower ). temporary Mysql files allow you to use the maximum size of indexes (when restoring, modifying the table structure, and importing data files. If the file size is larger than this value, the index will be created through the key cache (very slow) myisam_max_sort_file_size = 100G # If the temporary file used for fast index creation wocould be bigger # than using the key cache by the amount specified here, then prefer the # key cache method. this is mainly used to force long character keys in # large tables to use the slower key cache method to create the index. if the temporary file is used to quickly create an index, it will be larger than using the key cache, then the key cache method is good. This is mainly used to create an index using long strings Based on the slow usage of large tables. Myisam_sort_buffer_size = 35 M # Size of the Key Buffer, used to cache index blocks for MyISAM tables. # Do not set it larger than 30% of your available memory, as some memory # is also required by the OS to cache rows. even if you're not using # MyISAM tables, you shoshould still set it to 8-64 M as it will also be # used for internal temporary disk tables. used to cache index blocks in the MyISAM table. Do not set it to 30% greater than your available memory, because some memory still needs to be cached by the OS, even if you do not use the MyISAM table, you should still set it to 8-64 m, because it will also be used for the internal temporary disk table key_buffer_size = 8 M # Size of the buffer used for doing full table scans of MyISAM tables. # Allocated per thread, if a full scan is needed. the buffer size of the MyISAM table used for full table scan. If you need a complete scan, it is allocated to every thread read_buffer_size = 64 K read_rnd_buffer_size = 256 K # This buffer is allocated when MySQL needs to rebuild the index in # REPAIR, OPTIMZE, ALTER table statements as well as in load data infile # into an empty table. it is allocated per thread so be careful with # large settings. this buffer is allocated to: when MYSQL needs to repair, OPTIMZE, modify the table structure statement, and import and load data to an empty table, re-index. It is assigned to every thread, so be careful when using large settings. Sort_buffer_size = 256 K # *** INNODB Specific options *** INNODB special item INNODB save data directory innodb_data_home_dir = "E: /MySQL Datafiles/"# Use this option if you have a MySQL server with InnoDB support enabled # but you do not plan to use it. this will save memory and disk space # and speed up some things. # skip-innodb # Additional memory pool that is used by InnoDB to store metadata # information. if InnoDB requ Ires more memory for this purpose it will # start to allocate it from the OS. as this is fast enough on most # recent operating systems, you normally do not need to change this # value. show innodb status will display the current amount used. the additional memory pool when InnoDB stores metadata information. If more memory is needed, InnoDB allocates the operating system memory for it. This is fast enough in most operating systems, so you do not need to modify this value. Show innodb status displays the current innodb_additional_mem_pool_size = 2 M # If set to 1, InnoDB will flush (fsync) the transaction logs to the # disk at each commit, which offers full ACID behavior. if you are # willing to compromise this safety, and you are running small # transactions, you may set this to 0 or 2 to reduce disk I/O to the # logs. value 0 means that the log is only written to the log file and # The log file flushed to disk approximately once per second. value 2 # means the log is written to the log file at each commit, but the log # file is only flushed to disk approximately once per second. if it is set to 1, InnoDB will fl transaction logs on each committed disk, which provides complete ACID behavior. if you want to be secure and are performing some small operations, you can set it to 0 or 2 to reduce disk I/O logs. # Innodb_flush_log_at_trx_commit = 1 # The size of the buffer InnoDB uses for buffering log data. as soon as # it is full, InnoDB will have to flush it to disk. as it is flushed # once per second anyway, it does not make sense to have it very large # (even with long transactions ). this size is used to buffer InnoDB logs. Once it is full, InnoDB will release it on the disk. In any case, it will buffer once a second, which is meaningless for very large logs. (Even if there is a long transaction) innodb_log_buffer_size = 1 M # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. the bigger you set this the less disk I/O is needed to # access data in tables. on a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. do not set it # too large, though, because competition of the physical memory may # cause paging In the operating system. note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. innoDB, unlike MyISAM, uses a buffer pool to cache two indexes and row data. The disk I/O settings are larger and fewer data tables need to be accessed. On a dedicated database server, you can set the physical memory size of machines with parameters up to 80%. You don't need to set it too large. However, because the physical memory competing in the operating system may cause insufficient memory points. Note: For 32-bit systems, you may be limited to 2-GB user-level memory, so do not set it too high. Innodb_buffer_pool_size = 83 M # Size of each log file in a log group. you shoshould set the combined size # of log files to about 25%-100% of your buffer pool size to avoid # unneeded buffer pool flush activity on log file overwrite. however, # note that a larger logfile size will increase the time needed for the # recovery process. the size of each log file in the log group. You should merge the size of the log file about 25%--100% buffer pool to avoid overwriting the log file by unnecessary buffer pool buffering. However, a large log file will increase the time required for the recovery process. Innodb_log_file_size = 17 M # Number of threads allowed inside the InnoDB kernel. the optimal value # depends highly on the application, hardware as well as the OS # scheduler properties. A too high value may lead to thread thrashing. the InnoDB kernel allows multithreading. The highest performance depends on applications, hardware, and operating systems. A high value may cause thread jitter innodb_thread_concurrency = 8