Database performance optimization
Oracle features: Partitioning technology
Partition Index
More record tables and indexes.
Create an index or partition. Partition Index
Basic principle: the physical location of the created table must be different from that of the created index!Equivalent to: index the dictionary text before the dictionary on two hard disks respectively!
Query Information:
Index: An ordered
Talking about linux performance tuning 3: before partitioning and formatting, there is a special situation that may occur in the production environment: an ext3 file partition of the system, when a user writes a file to this partition, the system prompts that the disk space is full. However, when using the df-h command to view the file, the disk usage in this partition is found to be... talking about linux
In the relational database area, PostgreSQL is a very popular open source database software. Since its release in 1996, it has accumulated nearly 20 years of practical experience, both in PostgreSQL itself and in community ecology. Not only small and medium-sized enterprises, many large industry customers will also use PostgreSQL to build their own database syste
1, Testing purpose
By running the standard test program TPC-B and TPC-C, determine the performance differences between different parameters and different versions (8.2.14 vs 8.4.2, it provides reference for parameter configuration and version selection of PostgreSQL databases on ECOP cm.
The test platform is:
Hardware configuration: CPU e4600 dual-core 2.4g 2g RAM 160g SATA * 2
Operating System: Ubuntu 9.10
The amount of data will be huge in the future/you cannot predict the size of the data. We recommend that you change to PostgreSQL.
After a certain amount of data is stored in MySQL (generally, the performance of a single MySQL table is the best-, and the performance starts to decline when there are more data), the performance
Tags: size arrangement SDN Technology share ext ODBC. NET Analysis tool downPostgreSQL Hardware Performance Tuning
Translated from the article "PostgreSQL Hardware performance Tuning"
PostgreSQL is an object-relational database developed by developers all over the world on the Internet. She is an open so
, supports C/C + + or any compatible language, and starts with the 5.7.3 version to support full-text search, PostgreSQL has a similar system but supports more languages, including C + +, Java,. Net, Perl, Python, Ruby, TCL, ODBC, and so on, it can even run user-supplied code in a separate process; In addition to the general information about databases, tables, and columns contained in all relational databases, the
3051 millisecondspostgresql:4060579 Records Add indexSelect region_id, Avg ((data-> ' F1 '):: integer) from Fact_data_hstoreGROUP BY region_idResult set: Returns 9 Records, 2 fields, time-consuming millisecondsIndexes have no effect on the performance of this SQL statement.2.2 Test results (eight fields 4 million records)sqlserver:4060579 RecordsSelect Region_id,avg (F1) as AF1From Fact_dataGROUP BY region_idResult set: Returns 9 Records, 2 fields,
records from a query result;Prepared Query );Use analyze to maintain accurate optimization statistics;Regular use of vacuum or pg_autovacuumDelete the index (then re-create the index) When making a large amount of data changes)2. Procedural experienceCheck whether the connection pool is used. If not, use it as soon as possible;Check the program again. After the connection is used, check whether the connection pool is returned;3. Server parameter configurationMany configurations in the configura
1. Use explain:
PostgreSQL generates a query plan for each query, because selecting the correct query path has a critical impact on performance. PostgreSQL itself contains a scheduler for optimal planning. We can use the explain command to view the scheduler's query plan generated for each query.The query plan generated by P
First, the use of explain:
PostgreSQL generates a query plan for each query because the performance impact of selecting the correct query path is critical. PostgreSQL itself already contains a planner for finding the optimal plan, and we can view the query plan generated by the planner for each query by using the explain command.The query plan generated in
PostgreSQL Performance Optimization considerations caused by connection to IPVs that consumes cpu resources in particular. bitsCN.com
Because it is in the development stage, the postgres parameter is not configured, and the default configuration during installation is used,In the past, nothing was abnormal, but my cpu usage suddenly increased a few days ago.Check the process and find that the CPU usage of o
Tips for improving PostgreSQL Performance
In a (poor) PostgreSQL query, only a small change is required (ANY (ARRAY [...]). to ANY (VALUES (...))) the query time can be reduced from 20 s to 0.2 s. From the simplest use of explain analyze, to the use of EXPLAIN s community, there will be a hundred times of learning time investment to return.
Use ipvs to monitor sl
statements to reduce the cost of committing a transaction;Use cluster when extracting multiple records from an index;Use limit when extracting part of a record from a query result;Use pre-compiled queries (Prepared query);Use analyze to maintain accurate optimization statistics;Regular use of VACUUM or pg_autovacuumDelete indexes first when making large amounts of data changes (then rebuilding indexes)2, program ExperienceCheck the program, whether the use of connection pool, if not used, as so
PostgreSQL supports hstore to store data such as Key->value, which is in fact similar to array or JSON type. Efficient indexing is essential to the efficient use of this type of data. Let's take a look at the performance issues of two different types of indexes for the same retrieval request today.If we had such an original table. There is a Btree index based on the Str1 field.t_girl=# \d Status_check;
PostgreSQL provides some functions to help improve performance. There are several main aspects. 1. Use the explain command to view the execution plan. In the previous blog
PostgreSQL provides some functions to help improve performance. There are several main aspects. 1. Run the EXPLAIN command to view the execution pla
1. Use EXPLAIN:PostgreSQL generates a query plan for each query, because selecting the correct query path has a critical impact on performance. PostgreSQL itself contains a scheduler for optimal planning. We can use the EXPLAIN command to view the scheduler's query plan generated for each query.The query plan generated by PostgreSQL is a planning tree consisting
PostgreSQL Performance Monitoring ToolsHttps://github.com/CloudServer/postgresql-perf-toolsThis package includes three useful scripts aimed to help to pinpoint performance issues on systems with PostgreSQL as data Base backend.All Scritps is written in Python. Requirements:
In a (bad) PostgreSQL query, just a little to change (any (array[...)) to any (VALUES (...)) will be able to reduce the query time from 20s to 0.2s. From the simplest learning to use EXPLAIN analyze, to learn to use the Postgres community a lot of learning time input will have a hundredfold time to return.
Using Postgres to monitor slow Postgres queries
Earlier this week, a primary key query for the small table (10gb,1500) on our graphics editor had
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.