Nezha:fasten database

專案計畫執行(OpenvVew)
1

If you have a WordPress database with a large number of posts (40,000 in your case) and you want to optimize its performance, there are several steps you can take to speed up your database and improve overall site performance. Here are some recommendations:

1. Use a caching plugin:

Install and configure a caching plugin like WP Super Cache or W3 Total Cache. These plugins generate static HTML files of your pages, reducing the need to query the database for every request.

2. Optimize your database tables:

Use a plugin like WP-Optimize or WP-Sweep to clean up and optimize your database tables. These plugins can remove unnecessary data, optimize table structures, and improve database performance.

3. Enable database query caching:(object caching.)

WordPress uses a database query caching mechanism called object caching. By enabling a plugin like Redis Object Cache or Memcached, you can store database query results in memory, reducing the need to query the database repeatedly.

4. Optimize images:

Large images can significantly slow down your website. Resize and compress your images before uploading them to WordPress. You can use plugins like Smush or EWWW Image Optimizer to automatically optimize your images.
5### . Limit the number of revisions:
By default, WordPress stores multiple revisions of each post, which can bloat your database. Limit the number of revisions by adding the following line to your wp-config.php file: define('WP_POST_REVISIONS', 5); This will keep a maximum of 5 revisions per post.

6. Clean up unused plugins and themes:

Remove any unnecessary plugins and themes from your WordPress installation. Unused plugins and themes can still consume server resources, affecting overall performance.

7. Utilize a content delivery network (CDN):

A CDN caches your website’s static content and serves it from servers located around the world, reducing the load on your hosting server and improving page load times. Popular CDN services include Cloudflare, MaxCDN, and Akamai.

8. Upgrade your hosting plan:

If you’re on a shared hosting plan, consider upgrading to a more powerful hosting solution like a virtual private server (VPS) or dedicated server. These options provide more resources and can handle larger databases more efficiently.

9. Consider database sharding:

If your database continues to experience performance issues, you might want to explore database sharding, which involves splitting your database into smaller, manageable parts. This can distribute the load across multiple servers and improve performance.

It’s important to back up your database before making any changes and test the impact of each optimization step to ensure compatibility with your site and plugins.

The feature will perform the following maintenance tasks once per week:

  • Perform Database Optimization for MyISAM tables
  • Delete all automatically created post and page drafts
  • Delete all page and post revisions
  • Delete all posts and pages in your Trash
  • Delete all comments marked as Spam
  • Delete all expired Transients

Create a backup

Site Tools > Site → MySQL > PHPMyAdmin – Access . From the tool, select your database from the menu on the left and click Export > Go .

Check which are the largest database tables

To check the largest database tables, navigate to the PHPMyAdmin tool, select the respective database from the menu on the left and click on the Size button at the top. The database tables will be sorted by size.
This information may be helpful to determine if the table is part of the core WordPress database tables or it is added to your website by a plugin that you are using.

size
size1177×308 95.4 KB

The “Meta” database tables

These types of tables contain additional fields that are required by your main database tables. Usually, these fields are added by plugins. The so-called “meta” database tables are the following:

wp_postmeta
wp_commentmeta
wp_usermeta
wp_termmeta

Under certain circumstances, some information may remain in these database tables although it is no longer required. This can lead to the growth of the database and it is safe to delete such information as it is not used by any of the posts, comments, users or terms.

There are some other database tables that may contain information that your website doesn’t really need. Such tables are:

wp_term_relationships

Similar to the “meta” tables, the wp_term_relationships table can also contain information that is no longer needed. It is recommended to delete this information.

wp_comments

The easiest way to clear these database tables is via a third-party plugin. There are plenty of free plugins that can get the job done like Advanced Database Cleaner or WP-Optimize.

The issue is with another database table

In case the largest table is not part of the WordPress core tables, it is most likely associated with some of the website’s plugins.

To verify which plugin is associated with a particular table, check the Internet for relevant information by searching for the respective database table name.

When you find the plugin that creates and manages the table, you can contact the plugin’s support for further information and assistance.

What should I do if there are problems with my website after a database optimization?

Always check your website for potential problems after applying changes to its database or after installing/removing various plugins or modules. If you notice anything not working properly, you could restore the website to the state prior to the performed actions from the backup that you already have created.

2

Database sharding is a technique used to horizontally partition a database into smaller, more manageable parts called shards. Each shard contains a subset of the data, allowing for distributed storage and improved performance. Here are the general steps to implement database sharding:

  1. Plan your sharding strategy: Determine how you want to partition your data. Common sharding strategies include:
  • Range-based sharding: Data is divided based on a specific range of values (e.g., customer IDs or timestamps).
  • Hash-based sharding: Data is distributed based on a hash function, which evenly distributes records across shards.
  • Key-based sharding: Data is partitioned based on a specific key or attribute.Choose a strategy that aligns with your data distribution and access patterns.
  1. Set up infrastructure: Prepare the necessary infrastructure to support sharding. This may involve setting up multiple database servers or nodes that will host the shards. You can choose to deploy these servers on separate physical machines or use virtualization technologies.
  2. Define shard schema: Design the schema for each shard. The schema should be identical across all shards and contain the necessary tables and relationships required for your application.
  3. Establish shard keys: Identify the key or attribute that will be used for sharding. This key determines which shard a particular record will belong to. Ensure that the selected key distributes the data evenly across shards to avoid data imbalance.
  4. Implement data distribution: Develop a mechanism to distribute incoming data to the appropriate shards based on the shard key. This can be done through your application code or a middleware layer that sits between the application and the database.
  5. Handle cross-shard queries: If your application needs to perform queries that span multiple shards, you’ll need to implement a strategy to handle these cross-shard queries. This might involve querying multiple shards and aggregating the results at the application layer.
  6. Monitor and manage shards: As your database grows, monitor the performance and health of each shard. Implement tools and processes to manage shard distribution, rebalancing, and data migrations when necessary.

It’s worth noting that implementing database sharding can be complex and requires careful planning and consideration. It’s recommended to consult with database experts or consider utilizing sharding frameworks or tools that provide built-in sharding capabilities for your specific database technology.

3

Yes, it is possible to

Migrate your WordPress MySQL database

to a different database system that offers better performance.
However, the process can be complex and requires careful planning and execution. Here’s a general overview of the steps involved:

  1. Choose a new database system: Research and select a database system that suits your needs and offers better performance.

Common options include MariaDB, PostgreSQL, or even NoSQL databases like MongoDB.

Consider factors such as compatibility, scalability, and the specific requirements of your WordPress installation.
2. Set up the new database system: Install and configure the chosen database system on your server or hosting environment. Ensure that the necessary drivers and extensions are installed to support WordPress.

3. Export your WordPress MySQL database:

Use a tool like phpMyAdmin or the command-line utility mysqldump to export your WordPress MySQL database as an SQL file. This file will contain the database schema and data.

4. Convert the SQL file (if needed):

Depending on the target database system, you may need to convert the SQL file exported from MySQL to a format compatible with the new database system. This step might involve modifying data types, syntax, or other specific requirements of the target database system.
5. Create the database in the new system: Set up the new database in the chosen database system. This involves creating the database itself and any necessary tables or schemas.
6. Import the SQL file into the new database: Use the appropriate tools or commands provided by the new database system to import the modified SQL file into the newly created database. This will populate the database with the data from your WordPress MySQL database.

7. Update WordPress configuration:

Modify the WordPress configuration file (wp-config.php) to reflect the new database connection details. This includes updating the database name, username, password, and host information to match the new database system.
8. Test and troubleshoot: After the migration, thoroughly test your WordPress site to ensure that everything is functioning as expected. Check for any database-related errors or issues and resolve them as needed.

Note that this process may require technical expertise, and it’s recommended to have a backup of your existing MySQL database before proceeding with the migration.

Additionally, some plugins or themes may have MySQL-specific code or dependencies, so it’s essential to ensure compatibility with the new database system or make any necessary adjustments during the migration process.

4

Both MariaDB and PostgreSQL

are known for their performance and offer features that can provide better performance compared to traditional MySQL in certain scenarios. Here’s a brief comparison:

1. MariaDB:

  • MariaDB is a community-developed fork of MySQL and is designed to be a drop-in replacement for MySQL. It aims to provide enhanced performance, stability, and additional features.
  • MariaDB incorporates optimizations and performance improvements over MySQL, including better query optimization, enhanced thread pool management, and improved storage engines.
  • MariaDB also includes features like Parallel Replication, which can significantly improve replication performance in certain configurations.
  • In many cases, MariaDB can offer similar or slightly better performance compared to MySQL, especially for read-heavy workloads.

2. PostgreSQL:

  • PostgreSQL is a powerful, feature-rich, open-source object-relational database system. It is known for its robustness, extensibility, and advanced features.
  • PostgreSQL uses a multi-version concurrency control (MVCC) model, which allows for excellent concurrency and performance in high-transaction environments.
  • PostgreSQL offers advanced indexing options, sophisticated query optimization, and support for complex data types, making it suitable for handling complex data and queries.
  • PostgreSQL provides features like table partitioning, which can improve query performance on large datasets by dividing them into smaller, more manageable pieces.
  • PostgreSQL’s performance shines particularly in scenarios involving complex queries, advanced data types, and heavy write workloads.

The performance of any database system depends on various factors such as hardware configuration, workload characteristics, indexing, query optimization, and database design. It’s essential to benchmark and test your specific use case to determine which database system performs better for your specific requirements.

In general, both MariaDB and PostgreSQL offer performance improvements and additional features compared to traditional MySQL. However, the choice between them should be based on your specific needs, compatibility with existing systems, the complexity of your data, and the expertise available to manage and maintain the chosen database system.

5

Determining which database system offers better performance, PostgreSQL or MariaDB, depends on various factors and specific use cases. Both databases are highly capable and optimized for different scenarios. Here are some considerations:

  1. Workload Type: The nature of your workload plays a significant role.

PostgreSQL

excels in handling complex queries, analytical workloads, and scenarios that require advanced data types,

MariaDB

while MariaDB (a MySQL fork) is often favored for simpler, read-heavy workloads or applications with a large number of concurrent connections.

(PsSQL)

  1. Concurrency: PostgreSQL’s multi-version concurrency control (MVCC) model is known for handling high-concurrency scenarios effectively. If your application requires extensive write operations and concurrent access, PostgreSQL may provide better performance.

Both

  1. Scalability: Both databases are capable of scaling horizontally and vertically. However, MariaDB’s support for clustering and replication features, such as Galera Cluster, can make it more suitable for scaling out with high availability requirements.

PsSQL

  1. Indexing and Query Optimization: PostgreSQL offers advanced indexing options and a sophisticated query optimizer, which can be beneficial for complex queries and data models. MariaDB also provides effective indexing mechanisms, but the query optimizer may have more limitations compared to PostgreSQL.

PsSQL

  1. Data Integrity and ACID Compliance: PostgreSQL has a strong emphasis on data integrity and ACID compliance. If data integrity is crucial for your application, PostgreSQL’s strict adherence to these principles may provide better performance and reliability.

maDB Compatibility

  1. Ecosystem and Compatibility: Consider the existing ecosystem and compatibility requirements of your application. If your application relies on specific MySQL features or requires compatibility with MySQL-based systems, MariaDB (MySQL-compatible) may be a more seamless choice.

It’s important to benchmark and test your specific workload and application requirements to determine which database system offers better performance for your use case. Factors such as hardware configuration, database optimization, indexing strategies, and query design can also significantly impact performance.

6

Currently, the official WordPress distribution only supports the MySQL and MariaDB

But…

https://codex.wordpress.org/Using_Alternative_Databases

Basic Install for MariaDB

USE -postgresql

WP4PG

https://medium.com/@shoaibhassan_/install-wordpress-with-postgresql-using-apache-in-5-min-a26078d496fb

Hevo data connect?..

7
8
  • Stop MySQL.
  • Install MariaDB

Yes, that’s it!

For instance, if you’re on Ubuntu 18.04, the first step is to add MariaDB sources to your system like this:

sudo apt-get install software-properties-common
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://suro.ubaya.ac.id/mariadb/repo/10.3/ubuntu bionic main'
sudo apt-get update

Copy

Now it’s time to stop MySQL:

sudo service mysql stop

Copy

and then install MariaDB, which will launch upon completion:

apt install mariadb-server

Copy

And next comes . . . Nothing!

import/export

export

mysqldump -uUSERNAME -p DATABASE > backup.sql

import

mysql -uUSERNAME -p DATABASE < backup.sql

example nezha

mysqldump -uroot -p nezhapro > nezhabackup.sql

9
10

d1
d11475×498 75.5 KB

d2
d21296×964 32.4 KB

d3
d3925×419 16.5 KB

11 
mysqldump -uUSERNAME -p DATABASE > backup.sql

mysqldump --user=your-name --password="your-pass" wordpress > wordpress.sql
12

1則貼文被合併到一個已存在的話題：Ssh-commands

13

installation

14

: Install MariaDB Database Server on U20

Last Updated: August 10th, 2022
MariaDB is a drop-in replacement for MySQL. It is developed by former members of MySQL team who are concerned that Oracle might turn MySQL into a closed-source product. Enter the following command to install MariaDB on Ubuntu 20.04.

sudo apt install mariadb-server mariadb-client
After it’s installed, MariaDB server should be automatically started. Use systemctl to check its status.

systemctl status mariadb
Output:


● mariadb.service - MariaDB 10.3.22 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2020-04-10 14:19:16 UTC; 18s ago
       Docs: man:mysqld(8)
             https://mariadb.com/kb/en/library/systemd/
   Main PID: 9161 (mysqld)
     Status: "Taking your SQL requests now..."
      Tasks: 31 (limit: 9451)
     Memory: 64.7M
     CGroup: /system.slice/mariadb.service
             └─9161 /usr/sbin/mysqld

If it’s not running, start it with this command:

sudo systemctl start mariadb
To enable MariaDB to automatically start at boot time, run

sudo systemctl enable mariadb
Now run the post-installation security script.

sudo mysql_secure_installation

By default, the MariaDB package on Ubuntu uses unix_socket to authenticate user login, which basically means you can use username and password of the OS to log into MariaDB console. So you can run the following command to login without providing MariaDB root password.

sudo mariadb -u root

To exit, run

exit;

Check MariaDB server version information.

mariadb --version

As you can see, we have installed MariaDB 10.3.22.

mariadb Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

June 28th, 2022

sudo apt update

Ubuntu repositories already provide MariaDB packages. The installation is straightforward using the PAT package manager as follows.

sudo apt install mariadb-server mariadb-client -y

First, install the prerequisite package.

sudo apt install -y software-properties-common

Next, import the GPG signing key.

sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'

Once the GPG key is in place, add the MariaDB APT repository. The following repository is enabled for the MariaDB version 10.6. You may replace this value with the version that you intend to install.

sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://mariadb.mirror.liquidtelecom.com/repo/10.6/ubuntu focal main'

Finally, refresh the local repositories and install the MariaDB server and client by using the APT package manager.

sudo apt update && sudo apt install -y mariadb-server mariadb-client

The command installs the specified version of the MariaDB database engine and client, alongside other additional packages and dependencies.

You can confirm the installed version of MariaDB as shown.

mariadb --version

sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://mariadb.mirror.liquidtelecom.com/repo/10.6/ubuntu focal main'

Finally, refresh the local repositories and install the MariaDB server and client by using the APT package manager.

sudo apt update && sudo apt install -y mariadb-server mariadb-client

The command installs the specified version of the MariaDB database engine and client, alongside other additional packages and dependencies.

You can confirm the installed version of MariaDB as shown.

mariadb --version

From the above output, you can see that the MariaDB service is running. If for whatever reason, you find that the service is inactive or not running, you can start it as follows.

sudo systemctl start mariadb

Additionally, consider enabling MariaDB to start every time on system startup as shown.

sudo systemctl enable mariadb

The above command ensures that MariaDB starts automatically every time the server is rebooted or powered on.

#Step 3: Configure MariaDB

MariaDB ships with default settings that are considered weak and present loopholes which can potentially be exploited by nefarious parties leading to data breaches.

To remediate this issue, you need to go an extra step and harden your MariaDB instance. To improve the security of the MariaDB database engine, you need to run the mysql_secure_installation shell script as shown.

sudo mysql_secure_installation

Next, you will be prompted for the database root password. This ensures that no one can log in as the root user without authentication. So, type ‘Y’ and provide the database root password and confirm it.

Then press ‘Y’ for the subsequent prompts in order to configure the database engine according to the best security practices. This does the following:

  • Removes anonymous users from the database server
  • Disables remote root login. This ensures that the root user can only log in to the database server from “localhost”
  • Remove the test database which comes with MariaDB by default.
  • Reloads privilege tables for the changes t take effect immediately.

Step 4: Configure A Password-authenticated Administrative User

In this section, you are going to create a new user account in the database server with password authentication and later assign administrative privileges to the user. So, login as the root user as follows:

sudo mariadb -u root -p

Next, create a regular user. Here, we are creating a user called admin_user. Be sure to replace secret_password with your preferred user’s password.

CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'secret_password';

Output

Query OK, 0 rows affected (0.000 sec)

Next, grant all privileges to admin_user. This effectively assigns all the database root user’s permissions to the user.

GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost';

The *.* wildcard notation implies that the user has permission to execute any task on any database in the database server.

To apply the changes, flush the privileges.

FLUSH PRIVILEGES;

Finally, exit the database server.

EXIT;
  • September 1, 2022
    Follow the steps given below for installing and configuring MariaDB.

Step 1: Install the MariaDB server & Client

Update the server

sudo apt update -y

As a recommended practice, we will add the official MariaDB apt repository using the following script.

curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash

Now, install MariaDB server and client.

sudo apt-get install mariadb-server mariadb-client -y

Step 2: Validate MariaDB Installtion

First, let’s check the installed version of MariaDB using the following command.

mysql -V

Now, let’s check MariaDB Service Status.

sudo systemctl status mariadb
15

Out With MySQL

Start by creating a backup of your target database in MySQL. Start your MySQL server and log in to your MySQL database using phpMyAdmin
…

OR

Alternatively, if you don’t have phpMyAdmin or prefer using commands, use the following command to dump your database into an SQL file. Replace your-name and your-pass with your database username and password.

 $ mysqldump --user=your-name --password="your-pass" wordpress > wordpress.sql

In this case, the command creates a backup file named wordpress.sql containing the SQL code from the WordPress database.

Finally, uninstall MySQL.

In With MariaDB

First, ensure you have MariaDB installed on your local machine. You won’t need to do this manually

If you want to use the command line instead, follow these steps.

Log in to your MariaDB server and create the new database as follows:

$ mysql --user=your-name --password="your-pass" -e  "CREATE DATABASE wordpress";

Load the backup file into MariaDB.

$ mysql --user=your-name --password="your-pass" --database=wordpress < wordpress.sql

Once you have successfully imported the files from MySQL to MariaDB, the cursor will become active again

How To Update Your WordPress Site

After moving from MySQL to MariaDB, it’s time to get your WordPress site to start using the new database. To achieve this, you just have to update your site’s wp-config.php file with the new database details:

// ** MariaDB settings** //

define('DB_NAME', 'database_name_here');
define('DB_USER', 'database_username_here');
define('DB_PASSWORD', 'database_password_here');
define('DB_HOST', 'localhost');
define('DB_CHARSET', 'utf8');

/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', '');

After you save the file, the WordPress site will start pulling data from your new database.

another

Steps to Migrate Data From MySQL to MariaDB

Step 1: Create MySQL Database Backup

  • The existing Databases of MySQL need to be backed up to be transferred from MySQL to MariaDB.
  • The command mysqldump can be used to export all the existing databases in the form of files.
  • Binary Logging is required for this command to work, and this can be enabled in the MySQL configurations by modifying my.cnf file and adding the following option under the [mysqld] section.
log-bin=mysql-bin
  • Restart the MySQL by using the following command:
$ sudo service mysql restart
  • After that enter the command to use mysqldump.
$ mysqldump --all-databases --user=root --password --master-data > backupdb.sql
  • A backup of my.cnf can be created to be on the safer side, but this step is optional. To create the backup use the following command:
$ sudo cp /etc/mysql/my.cnf /opt/my.cnf.bak

Step 2: Uninstall MySQL Database Packages

  • Stop the MySQL service by using the following command
$ sudo service mysql stop

or:

$ sudo systemctl stop mysql

or:

$ sudo /etc/init.d/mysql stop

install mariaDB

$ sudo apt-get install software-properties-common
$ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
$ sudo add-apt-repository 'deb http://mirror.mephi.ru/mariadb/repo/5.5/ubuntu trusty main'
$ sudo apt-get update
$ sudo apt-get install mariadb-server

Step 4: Load MySQL Backup File to MariaDB

Now import the MySQL backup file into MariaDB using the following command:

$ mysql -u root -p < backupdb.sql

Enter the password and the import process for MySQL to MariaDB will start. As soon as the import of files from MySQL to MariaDB is complete the cursor will return to the command prompt.

To verify the success of import from MySQL to MariaDB, run the following commands:

$ mysql -u root -p

MariaDB [(none)]> show databases;
MariaDB [(none)]> use test01;
MariaDB [test01]> select * from pet;

Start the MariaDB service and verify its version:

/etc/init.d/mysql start
Starting MySQL… SUCCESS!

You can also load your WordPress sites in a web browser and confirm their functionality.

Installing a fresh WordPress

Since MariaDB is a drop-in replacement for MySQL, installing WordPress with MariaDB does not differ from the standard method with MySQL. Basically, you download the latest WordPress package, extract the archive in a directory on your server, then access the corresponding location with your web browser to complete the installation procedure. During the first step of the setup you are prompted to provide the database details. Assuming you’ve installed MariaDB already as shown above, you can create the new database and assign a user to it from the command-line interface:

mysql MariaDB [(none)]> create database new_wp; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> grant all privileges on new_wp.* to wpuser@localhost identified by ‘myp@Ssw0Rd’; Query OK, 0 rows affected (0.01 sec)

16

How to migrate from MySQL to MariaDB?

MySQL to MariaDB is one of the most ridiculously easy migrations you’ll ever do in your life. There’s nothing to be done, and you’ll be left thinking “That’s it?!” after it’s over. Essentially, all you have to do is:

  • Make sure your package manager has access to MariaDB.
  • Stop MySQL.
  • Install MariaDB

Yes, that’s it!

For instance, if you’re on Ubuntu 18.04, the first step is to add MariaDB sources to your system like this:

sudo apt-get install software-properties-common
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://suro.ubaya.ac.id/mariadb/repo/10.3/ubuntu bionic main'
sudo apt-get update

Copy

Now it’s time to stop MySQL:

sudo service mysql stop

Copy

and then install MariaDB, which will launch upon completion:

apt install mariadb-server

Copy

And next comes . . . Nothing!

17

1則貼文被分割成新的話題：Nextcloud backup