mysql tuning script

performance by analyzing these special SQL statements. By default, the MySQL database does not start the slow query log, we need to manually set this parameter, of course, if not tuning needs, it is generally not recommended to start this parameter, because there is a certain performance impact. Of course, what kind of statement can be called a slow statement, so there needs to be a threshold to define, on

mysql-e "insert into mydb.students values ($i, ' stu$i ', $[$[random%90] +18], ' ${gender[$[random%2]]} ', $[$[random%9]+1]) ";d one [[emailprotected] mydb] #ls db.opt students.frm Students.par students#p#p0.ibd students#p#p1.ibd Students#p#p2.ibd6, SQL mode: Defines the settings for the response behavior of mysqld for violations such as constraints; commonly used MO De:traditional strict_trans_tables strict_all_tables Modification method:

the number of empty means the cache is used unreasonable (always run out)2.4 MySQL Log type(1) Error log: Log information generated by the database service during startup and operation, enabled by defaultThe Log-error=/var/log/mysqld.log in/ETC/MY.CNF(2) Binlog log: Default is not enabled.(3) query log: Record all executed SQL commands, not enabled by defaultVim/etc/my.cnf[Mysqld]General-log #默认存储位置/var/lib/mysqlDefault name: Host name. log(4) Slow q

generated in the development environment. MySQL backup process: 87. Back up data from the secondary replication server. 88. Stop copying during backup to avoid inconsistency between data dependency and foreign key constraints. 89. Stop MySQL and back up the database files. 90. If you use MySQL dump for backup, back up the binary log file at the same time to ensu

log Log_error[=name]//Log error messages for the Enable/run/stop processQuery log general_log,general_log_file=//Record client connection and query operationsSlow query log slow_query_log,slow_query_log_file=,long_query_time=//record query operations that take a long time or do not use indexesLog Slow query:Slow-query-log//Enable slow querySlow-query-log-file//Specify slow query log fileLong-query-time//queries that exceed a specified number of seconds (default 10 seconds) are loggedLog-queries

to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # slow_query_log = 1 # slow_query_log_file = Slow.log # long_query_time = 1 # log_queries_not_using_indexes # log-bin = mysql-bin # Server-id = 1 # Innodb_flush_lo G_at_trx_commit = 1 # sync_binlog = 1 sql_mode=no_engine_substitution,strict_trans_tables Attach MySQL 5.6.10 Compile parameters: Tar zxvf

Tags: MySQL performance tuning three-table designA First introduce several concepts: 1. Full function dependency: In the relationship mode R (U) on the attribute set U, if x->y, and for any one true subset X ' of X ', there is X ' ≠>y, then Y is dependent on the X full function. 2. Transfer function dependency: in R (U), if x->y,y->z, the Z-to-X transfer function is called dependent. Two Paradigm There ar

Tags: MySQL performance tuning four quick modify table structure1. Modifying the varchar type To meet the requirements, the varchar (6) is now converted to varchar (8). The better approach is not to use altertable modify, but instead take the following steps: A Create a temporary table and set the varchar to varchar (8) as follows: B. Replace the. FRM table structure file C. Then check it out. Unde

MySQL backup script and mysql script. MySQL backup script, mysql script mysqlbackup. php :? Php backup mysqlset_time_limit (0); date_default

database is just starting up and needs to be warmed up to cache all the data on the disk into memory. Data preheating can improve reading speed.For InnoDB databases, you can use the following methods to preheat data:1. Save the following script as Makeselectqueriestoload.sqlSELECT DISTINCTCONCAT (' SELECT ', ndxcollist, ' from ', db, '. ', TB,' ORDER by ', ndxcollist, '; ' SelectquerytoloadcacheFrom(SELECTEngine,table_schema Db,table_name TB,Index_na

Mysql Tomcat C3p0 System Performance Tuning personal summary System Information The application logic is to use c3p0 to query data in the database and return Json data over http. 1. the initial test result JMeter test result before optimization No. Type Original 1000 data bigger 1 500 Connection 250 query/S 63q/S70q/S 2 1000 connections 255q/S 57q/S65 q

'. ' Create_next_partitions ' (Schemanamevarchar (+),tablename varchar) Begindeclarenextclocktimestamp;declarepartitionnamevarchar (+);D Eclare CLOCKint; set @totaldays =7; Set@i=1;createloop:loopsetnextclock=date_add (now (), INTERVAL@i day); Setpartitionname=date_format (NEXTCLOCK, ' p%y%m%d ' ); Setclock=unix_timestamp (Date_format (Date_add (nextclock,interval1day) , '%y-%m-%d00:00:00 ')); Callzabbix.create_partition (SCHEMANAME,TABLENAME,PARTITIONNAME,CLOCK); set@[emailprotected]+1;if@i>

Problem:A system, MySQL database, after the data is large. MySQL CPU utilization is very high, a Test side access to the server when MySQL CPU utilization is 15%, 6 test end of the server when the MySQL CPU occupancy rate is 50%~60%.PS 1: Each Test side of the thing is to insert records, but before inserting will first

MySQL backup script, MySQL script mysqlbackup.php: Php//backup MySQL Set_time_limit(0); Date_default_timezone_set (' PRC '); //Configuration $configs=Array( ' Host1 ' =Array( ' localhost ', ' root ', ' root ',Array(),//back up all databases as

efficient results. Http://bbs.linuxtone.org/thread-5152-1-1.html3. Operating System-level optimizationA> A 64-bit system can allocate more memory and service tuning to a single process, disable unnecessary services, modify file descriptor restrictions, and reserve more resources for MySQL;B> File System Optimization: XFS is recommended for a separate file system for the data warehouse, which is generally m

size limit (butis Innodb do not know), there is another reason is the convenience of self-maintenance. In addition, INNODB can not only use the textthe original block device, which is what we often call a bare device. when our file table space is about to run out, we have to add data files for it, of course, only sharetable space has this action. Adding a data file to a shared table space is a simple operation, justthe Innodb_data_file_path parameter is followed by a standard format to set the

Tags: mysq tuning target considerations level MySQL performance efficiency transaction1. The leftmost principle notice encounters > 2. Invalid left-most index encountered for like query3.SQL Performance Optimization Goal: at least the range (range lookup to index) level is required to be a ref (normal index) level, preferably consts (up to one matching row in a single table = PRIMARY key or unique index)4.

MySQL Database SQL statement tuning 、Index Design principles:Index columns are generally columns in a WHERE clause or a column in a join sentenceTry not to index columns with small cardinality, such as the gender columnUse a short index whenever possible: If you try to specify the minimum length for the character column index.(Short Keys is Better,integer best)Create index CityName on the city (city (10));C

Mysql Performance Check script-mysql Performance Check script #! /Bin/sh# Ocpyang@126.com Export black = '\ 033 [0m'Export boldblack = '\ 033 [1; 0m'Export red = '\ 033 [31m'Export boldred = '\ 033 [1; 31m'Export green = '\ 033 [32m'Export boldgreen = '\ 033 [1; 32m'Export yellow = '\ 033 [33m'Export boldyellow = '\ 03

MySQL backup script and mysql script Mysqlbackup. php: Mysqlbackup. bat: D: \ xampp \ php \ php.exe-q D: \ wamp \ www \ php_lib \ basic \ mysqlbackup. phpPause; Linux shell backup MySQL: #! /Bin/sh # sed-I's/^ M // G'/home/taskschd/backup. sh # Note: ^ The input method of