Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you which database is better to use PHPCMS. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

It is better for PHPCMS to use MySQL database because PHPCMS is written in PHP and PHP supports MySQL better, so it is recommended to use MySQL, which is an open source relational database management system.

MySQL optimization

Select InnoDB as the storage engine

Databases of large products have higher requirements for reliability and concurrency. InnoDB, as the default MySQL storage engine, is a better choice than MyISAM.

Optimize database structure

Organize the schema, tables, and fields of the database to reduce the cost of iCandle O, keep related items together, and plan ahead so that performance can be maintained at a high level as the amount of data grows.

The design of the data table should minimize the space it takes up and the primary key of the table should be as short as possible. For InnoDB tables, the column in which the primary key resides is replicable in each secondary index entry, so if there are many secondary indexes, a short primary key can save a lot of space.

Create only indexes that you need to improve query performance. The index facilitates retrieval, but increases the execution time of insert and update operations.

ChangeBuffering characteristics of InnoDB

InnoDB provides a configuration of changebuffering to reduce the disk Icano required to maintain the secondary index. Large-scale databases may encounter a large number of table operations and a large number of I hand O to keep secondary indexes up-to-date. When the relevant page is not in the buffer pool, the changebuffer of InnoDB will change the cache to the secondary index entry, thus avoiding the time-consuming IUnio operation caused by not being able to read the page from disk immediately. When the page is loaded into the buffer pool, the buffered changes are merged and the updated page is then flushed to disk. This improves performance and is suitable for MySQL5.5 and later versions.

InnoDB page compression

InnoDB supports page-level compression of tables. When a data page is written, there is a specific compression algorithm to compress it. The compressed data is written to disk, and the punching mechanism releases the empty blocks at the end of the page. If compression fails, the data is written as is. Both tables and indexes are compressed, because indexes are usually a large part of the total database size, and compression can significantly save memory, Imax O, or processing time, thus improving performance and scalability. It also reduces the amount of data transferred between memory and disk. This feature is supported by MySQL5.1 and later.

Note that page compression does not support tables in shared tablespaces. Shared table spaces include system table spaces, temporary table spaces, and regular table spaces.

Use bulk data Import

Importing bulk data using sorted data sources on the primary key speeds up the data insertion process. Otherwise, you may need to insert rows between other rows to maintain sorting, which can cause disk Imax O to become higher, which in turn affects performance and increases page splitting. Turning off the autocommit mode is also beneficial because it flushes the log to disk for each insert. Temporary transfer of unique keys and foreign key checks during bulk insertion can also significantly reduce disk Imax O. For newly created tables, it is best to create foreign key / unique key constraints after bulk import.

Once your data has reached a stable size, or the growing table has increased by tens or hundreds of megabytes, you should consider using the OPTIMIZETABLEstatement to reorganize the table and compress the wasted space. A full table scan of the reorganized table requires less I _ hand O.

Optimize InnoDB disk iDUBO

Increasing the size of the InnoDB buffer pool allows queries to be accessed from the buffer pool rather than through the disk Ihop O. The index of clearing buffer is adjusted to reach the best level by adjusting the system variable innodb_flush_method.

Memory allocation of MySQL

Before allocating enough memory for MySQL, consider the memory requirements for MySQL in different areas. The key area to consider is concurrent connections-for a large number of concurrent connections, sorting and temporary tables will require a lot of memory. At the time of this writing, 16GB to 32GB RAM is sufficient for databases that handle 3000 + concurrent connections.

Memory fragmentation can consume about 10% or more of memory. Caches and buffers such as innodb_buffer_pool_size, key_buffer_size, and query_cache_size consume about 80% of the allocated memory.

Daily maintenance

Periodically check the slow query log and optimize the query mechanism to effectively use the cache to reduce disk Imax O. Optimize them to scan the minimum number of rows instead of doing a full table scan.

Other logs that can help DBA check and analyze performance include error logs, regular query logs, binary logs, and DDL logs (metadata logs).

Periodically flush caches and buffers to reduce fragmentation. Use the OPTIMIZETABLEstatement to reorganize the table and compress any space that may be wasted.

The above is the editor for you to share the use of PHPCMS with which database is better, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.