mysql tuning script

MySQL Performance tuning: The speed of the insert query: The time to insert a record consists of the following: Connection: (3) Send query to server: (2) Analysis of the query: (2) Insert Record: (1 x record size) Insert Index: (1 x index) Off: (1) The numbers here are a bit proportional to the overall time. This does not take into account the initial cost of opening a table (it is done once for ea

you to optimize the SQL statement for join. Also, the fields that are used for join should be of the same type. For example, if you want to join a DECIMAL field with an INT field, MySQL cannot use its index. For those string types, you also need to have the same character set. (The character set of two tables may be different) "Avoid select *" "always set an ID for each table" We should set an ID for each table in the database as its primary key, a

|+-----------------------+-------------+| Handler_read_first | 5803750 || Handler_read_key | 6049319850 || Handler_read_next | 94440908210 || Handler_read_prev | 34822001724 || Handler_read_rnd | 405482605 || Handler_read_rnd_next | 18912877839 |+-----------------------+-------------+ Number of query requests paged out by the server: mysql> show global status like ' Com_select '; +---------------+-----------+| variable_name | Value |+---------------

MySQL choose the best index to use. Maintaining only the desired index is advantageous for query optimization. If you want to add an index to an indexed table, you should consider whether the index you want to increase is the leftmost index of an existing multi-column index. If so, don't bother to increase the index because it's already there.-------------------------------------------------primary key index, a primary key index is automatically adde

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

= 100GB# These-settings help warm up the buffer pool, keeping server speed up after restarts. Increases shutdown and startup time.Innodb_buffer_pool_dump_at_shutdown = OnDefault is OffInnodb_buffer_pool_load_at_startup = OnDefault is Off# This setting makes changes to data = Get committed to disk (Fsync) once per second, reducing disk I/OInnodb_flush_log_at_trx_commit = 0# This setting skips unnecessary buffering when writingInnodb_flush_method = O_directInnodb_lock_wait_timeout = 3600Default i

MySQL Management: Performance tuning, high availability and monitoring mini-bookmysql5.5.x Main Improvements1. Use InnoDB storage engine by default2. Make full use of CPU multi-core processing capability3, improve the number of brush write dirty pages and merge the number of inserts, improve I/O4, let Innodb_buffer_pool buffer pool of heat data to survive longer, pollution problems5, InnoDB Data recovery ti

Hardware preparation environment: Hard Disk: 16 SAS15KRAID5 with 512 MCacheCPU: AMD4 core two memory: 16 GB software environment: Operating System: RedHatAS4.62.6.9 Hardware preparation environment: Hard Disk: 16 SAS 15 K RAID5 with 512 MCacheCPU: AMD 4-core two-core memory: 16 GB software environment: Operating System: RedHat AS4.6 2.6.9 Hardware preparation environment: Hard drive: 16 SAS 15 k raid 5 with 512 MCache CPU: AMD 4-core two Memory: 16 GB Software environment: Operating System:

Hardware preparation environment: Hard drive: 16 SAS 15 k raid 5 with 512 MCache CPU: AMD 4-core two Memory: 16 GB Software environment: Operating System: RedHat AS4.6 2.6.9-67. ELsmp Mysql: 5.0. Performance Tuning based on different scenarios is as follows: Scenario: Write operations (insert, query, and delete) are intensive, and the data capacity far exceeds the memory size (minimum is more than GB, or ev

Tags:/etc/serve linux show via experience performance help insertionMySQL restricts the size of packets accepted by the server according to the configuration file.Sometimes large inserts and updates are limited by the Max_allowed_packet parameter, resulting in failure.View current configurationShow VARIABLES like '%max_allowed_packet% ';The results shown are:+--------------------+---------+| variable_name | Value |+--------------------+---------+| Max_allowed_packet | 1048576 |+-----------------

referencing the official MySQL document: The optimizer is more mature for joins than for subqueries, so in many cases a statement that uses a Subquery can executed more efficiently if you rewrite it as a join.[is familiar with the business, then most of the situation can be changed to join the way to optimize]optimized post-sql:Select COUNT (*) from cms_article CA right JOIN cms_article_tag cat on cna.id = cnat.article_id WHERE cnat.tag_id= a TagIDEx

add the following line to the boot auto mount in the/etc/fstab file: /dev/sdb1/ssd ext3 defaults00 Now we move MySQL to the new diskFirst stop the MySQL service: #service mysqld stop I suggest you?? Stop Apache/nginx at the same time to prevent any attempts to write to the database: #service httpd stop #service nginx stop Now copy the entire

MySQL tuning can be done in several ways:1. Architecture Layer:Do from the library, to achieve read and write separation;2. System level:Increased memory;RAID0 or RAID5 the disk to increase the read and write speed of the disk;You can re-mount the disk and add the Noatime parameter, which reduces the I/O to the disk;3. mysql itself

MyBatisRemoving all the columns will make the optimizer unable to complete such optimizations as overwriting index scans, such as HibernateHowever, retrieving the query cache for all columns is more beneficial than multiple independent query caches that only get partial columnsEvery time I see a SELECT * Please wonder if you really need to take it all outDuplicate query for the same data: Please cache this data, for example, in the session.The simplest of three metrics to measure query overhead

, where sending data accounts for 99% of the time. Still more than the initial page load time. Finally, instead of innerjoin the table with the select method to remove the relevant data, leftjoin the table data, by looping the select result set, the data is obtained separately. After the modified development environment page load time is 0.2~0.3s, the server environment is 0.4~0.5s. Although not ideal, performance is acceptable for small systems within the community. 2,

By default, MySQL does not have the Query_cache feature turned on.MariaDB [(None)]> show variables like '%query_cache% ';+------------------------------+---------+| variable_name | Value |+------------------------------+---------+| Have_query_cache | YES | -----indicates that the current MySQL version supports the query cache feature| Query_cache_limit |1048576 | -----| Query_cache_min_res_unit | 4096 || Q

Cache Fragmentation rate = 20.46%, query cache utilization = 62.26%, query cache Hit Ratio = 1.94%, hit ratio is poor, probably write more frequently, and possibly some fragments.======================================================================================== Sort Usage mysql> Show Global status like ' sort% ';Sort_merge_passes 2Sort_range 18528Sort_rows 390838Sort_scan 3439 MySQL first tries to so

The Linux file system is a bit like the MySQL storage engine, which supports a wide variety of file systems. The top layer of the virtual files system is provided as an abstraction interface layer to provide calls externally. Then the various file systems on the lower level implement these calling interfaces.1. log file system and non-log file system in LinuxThe modification of the contents of the file involves two parts: the modification of the actua

fillinghelptables ... OK tostartmysqldatboottimeyouhavetocopy support-files/ Mysql.servertotherightplaceforyoursystem Startup script PLEASEREMEMBER tosetapasswordfornbSp themysqlrootuser! todoso,starttheserver,thenissuethefollowing Commands:/application/mysql//bin/mysqladmin-urootpassword ' New-password '/application/ mysql//bin/mysqladmin-uroot-hqbpcpassword '

1. Macro-tuning can be considered three parts, respectively, hardware, network, software, the main consideration here is software tuning(1) Software tuning includes: table Design (Paradigm, field type, data storage engine), SQL statement Index, configuration file parameters, file system, operating system, MySQL version