Alibabacloud.com offers a wide variety of articles about mysql query performance tuning, easily find your mysql query performance tuning information here online.
15 useful MySQL/MariaDB performance tuning and Optimization Techniques
MySQL is a powerful open-source Relational Database Management System (RDBMS ). It was released in 1995 (20 years ago ). It uses Structured Query Language (SQL), which may be the most popular choice in da
some keys, the values can be retrieved from the index tree for faster.Assume that you issue the following SELECT statement:Mysql> SELECT * from Tbl_name WHERE col1=val1 and Col2=val2; If a multi-column index exists on col1 and col2, the appropriate row can be fetched directly. If separate single-row column indexes exist on col1 and col2, the optimizer tries to find a more restrictive index by deciding which index will find fewer rows and using that index to fetch rows.When setting the Tmp_table
increase the default value Innodb_buffer_pool_size InnoDB buffer pool, here's his duty scene. * Data Caching – the most important purpose* Index Cache – Using the same buffer pool* Buffering – Changed data (often called dirty data) is stored in the buffer before being flushed to the hard disk* Storage internal structure – some structures such as adaptive hash indexes or row locks are also stored in the InnoDB buffer pool Fortunately our server is separate to
1m-2m size, and then set it individually on demand in each session. 4, Read_rnd_buffer_size For sorting and order by operations, it is best to set it to 1M and then set it as a session variable to a larger value in the session. (v) Slow query log A slow query log is a useful feature of MySQL. 1, Log_slow_queries MySQL
5.6 version of this upgrade is very large, so in MySQL5.5 you may be conservatively set innodb_buffer_pool_instances=4, in MySQL In 5.6 and 5.7 you can set to 8-16 buffer pool instances.You'll see a little performance improvement after you set it up, but it should be a good performance in most high load situations.By the right, don't expect this setting to reduc
memory disk
Mkdir-p/mnt/ramdisk
sudo mount-t tmpfs-o size=4000m tmpfs/mnt/ramdisk/
mv/var/lib/mysql/mnt/ramdisk/mysql
9. Use MYSQL in a NOSQL way
B-tree is still one of the most efficient indexes, and all MYSQL is still not obsolete.
Using Handlersocket to skip MySQL's SQL parsing layer,
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
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 In
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 quer
Original addressMySQL is a powerful open source relational database management system (RDBMS). It was posted on 1995 (20 ago). It uses Structured Query Language (SQL), which may be the most popular choice in database content management. The latest MySQL version is 5.6.25, released on May 29, 2015.An interesting fact about MySQL is that its name comes from the dau
Buffer is often used to buffer the SELECT results and will not directly return results during the next same query. # enabling the Query Buffer can greatly improve the server speed. If you have a large number of identical queries and rarely modify tables. # Check the "Qcache_lowmem_prunes" status variable to check whether the current value is high enough for your load. # Note: Your table often changes If th
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
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/li
Tuning mysql Network Performance Debugging Tool article bitsCN.com this article is an article about Linux system network performance management skills, this section describes how to use route, netstat, and tcpdump network management testing tools and their functions.
Route
When configuring the network, you must specify
Debugging Tool for tuning mysql Network Performance: This article describes the network performance management skills of Linux systems, this section describes how to use route, netstat, and tcpdump network management testing tools and their functions.
Route
When configuring the network, you must specify the path throu
always pay attention to it in the design, remember. MySQL database is so hot in the internet industry, in addition to its open source features, easy to use, there is a very important factor is the scalability of a greater advantage. Each of its different storage engines has features that can be used to address a variety of different scenarios. Its Replication and Cluster features are a very effective means of enhancing extensibility.Excerpt from: "
This article will show you some basic but useful tips on how to optimize the performance of MySQLMariaDB. Note: This document assumes that you have installed MySQL or Maria
This article will show you some basic but useful tips on how to optimize MySQL/MariaDB performance. Note: This document assumes that you have insta
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 Con
Tags: bottleneck affects LAN connectivity and so on addition query framework staticReprinted from Http://www.cnblogs.com/jiaosq/p/5843437.html Today, database operations are increasingly becoming a performance bottleneck for the entire application, which is especially noticeable for web applications. It's not just about the performance of the database that DBAs n
MySQL from getting started to mastering (performance testing and tuning)Course View Address: HTTP://WWW.XUETUWUYOU.COM/COURSE/33Courses from the School of worry-free network : http://www.xuetuwuyou.com/Starting from the basics, all the way up to the advanced level, including but not limited to SQL statements, SQL Tuning
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.