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

I. Production environment MySQL deadlock monitoring and how to reduce the probability of deadlock occurrence

First, deadlocks are not "deadlocks"; deadlocks are caused by two or more session-lock waiting loops.

(1) Deadlock monitoring and handling methods

For deadlock monitoring, each version provides innodb_print_all_deadlocks option, which will output deadlock logs to MySQL error logs, so you can monitor deadlock by monitoring error logs. MariaDB is even simpler. MariaDB provides a counter for Innodb_deadlocks, which can be monitored for deadlock by monitoring the growth of the counter.

If deadlocks occur on the line and the frequency is high, be sure to pay attention to them. Because the deadlock log records only the last two SQL entries that caused the deadlock, it is not possible to locate the deadlock log immediately

Deadlock out of the reasons, should be timely collaborative development simulation out of the deadlock process, analysis of deadlock causes, modify program logic.

How to reduce the probability of deadlock

1. Try to use short transactions and avoid large transactions

2. When adding FOR UPDATE/LOCK IN SHARE MODE lock, it is best to lower the transaction isolation level, for example, RC level, to reduce the probability of deadlock occurrence, and also to reduce the locking granularity.

3. When multiple tables or rows of records are involved in a transaction, the operation order of each transaction should be consistent.

4. Optimize SQL efficiency through index, reduce deadlock probability, and avoid locking all data caused by full table scan.

5. The program should have transaction failure detection and automatic repeat submission mechanism

6. In high-concurrency scenarios, turn off innodb_deadlock_detect to reduce deadlock detection overhead and improve concurrency efficiency.

II. What are the excellent features of MongoDB and what are the suitable scenarios

(i) Excellent characteristics

1. Practicality: json-like rich document data model, naturally friendly to developers

Availability: Automatic high availability based on raft protocol, easily providing 99.999% availability

Scalability: Support for fragmentation clusters, providing friendly horizontal scalability for services

4, high performance: nested model design support, reduce discrete write, full physical memory utilization, avoid disk read

5, strong compression: WiredTiger engine provides a variety of data compression strategies, 2~7 times the compression ratio, greatly saving disk resources

(ii) Appropriate scene

1. No multi-document transaction and multi-table association query requirements

2. Rapid iteration of business and frequent changes in demand

3. Single cluster concurrency is too large to support business growth

4. Expected data volume growth TB and above storage demand

5. Expectations require 99.999% database high availability scenarios

What are the advantages of GO over other programming languages? How to choose the actual production environment?

1. Innate support for high concurrency, strong consistent language, high development efficiency and stable online operation

2. Garbage collection, regardless of memory allocation and recycling

3, powerful GMP model, asynchronous processing, support high concurrency, Xiaobai can also easily write high concurrency code

In the actual production environment, it is recommended to consider the following aspects:

1. Looking at business scenarios, e-commerce, big data processing has ready-made solutions, which are not suitable for use. In addition, mathematical operations, CPU-intensive also do not use.

2. GO is good at rapid business prototyping, iterative development efficiency is high, start-up companies push hard

3. Look at the technology stack developed by the company. If the difference is large, then GO is faster to get started, and the programming style can be unified.

Fourth, a big transaction, there are many updates, now rolled back, but anxious to shut down and restart, what should I do?

1. First, try to avoid executing large transactions in MySQL, because large transactions will bring problems such as master-slave replication delay.

2. If a large transaction is killed, MySQL will automatically roll back the operation. You can see the ROLLING BACK transaction through TRANSACTIONS of show engine innodb status, and the corresponding row lock will still be held during the rollback operation.

3. If MySQL is forcibly closed at this time, the rollback action will still be performed after MySQL is started again.

Therefore, in order to ensure data security, it is recommended to wait patiently for the rollback to complete before shutting down and restarting. Before shutdown and restart, you can turn down innodb_max_dirty_pages_pct to refresh dirty pages as much as possible, and turn off innodb_fast_shutdown.

5, if there is no way to shut down the case, you can kill -9 to close MySQL first, provided that you need to set double one to ensure transaction security, otherwise you may lose more transaction data. After restarting the instance, innodb will crash recovery and roll back the previous transaction.

PS, kill -9 is a high-risk operation, which may cause unpredictable problems such as MySQL not starting. Please use it carefully.

5. How to reduce the impact of incorrect WHERE conditions or no WHERE conditions when UPDATE/Delete

1, try not to manually execute any SQL commands online, it is easy to make mistakes. Execute SQL commands directly online preferably with the help of a second inspector to confirm

2. It is best to execute SQL in the test environment after confirming that there is no error, and then execute it in the production environment, or edit and confirm it in the local text environment in advance.

3. It is recommended to turn on the sql_safe_updates option to prohibit UPDATE/Delete commands without WHERE conditions or LIMIT or index conditions from being executed. You can also add the--safe-updates option when connecting to the server with mysql client, for example: mysql --safe-updates -h xx -u xx

4. When performing DML operations manually on the line, turn on transaction mode first, and roll back in case of misoperation. For example: mysql> begin; update xxx; rollback;

5. Execute DML operations through DB management platform, and add judgment on this kind of dangerous SQL on the platform, and directly refuse the execution of dangerous SQL.

6. Configure the delayed slave library. After discovering the data deleted by mistake, quickly recover the data from the delayed slave library.

How does MySQL control the number of times users try to enter the wrong password?

(i) Plug-in assistance

Starting from official MySQL 5.7.17, CONNECTION_CONTROL and CONNECTION_CONTROL_FAILS_LOGIN_ATTEMPTS plug-ins are provided. The plug-in also provides three parameters: connection_control_failed_connections_threshold, connection_control_min_connection_delay and connection_control_max_connection_delay.

1、connection_control_failed_connections_threshold

The meaning of this parameter is to control the number of times after landing failure to start delayed landing

2、connectioncontrolminconnectiondelay

This parameter represents the minimum delay time for each reconnection after the number of failures exceeds. The delay calculation formula is (total number of failures at present-failure threshold

value)connectioncontrolminconnection_delay, so the more false attempts, the greater the delay

3、connection_control_max_connection_delay

Maximum latency beyond which clients can reconnect

4. After installing the plug-in, you can monitor the Connection_control_delay_generated status value and the table under INFORMATION_SCHEMA.

CONNECTION_CONTROL_FAILS_LOGIN_ATTEMPTS to monitor for incorrect login attempts

(2) Error log monitoring

Scan MySQL error logs regularly to capture the number of account password errors. After reaching a certain threshold, the corresponding host IP can be blocked in the system firewall to achieve the purpose of blocking the account (the specific operation depends on the situation)

For example: Error log will display 2019-05-10T13:04:41.232259Z 5 [Note] Access denied for user 'xucl'@'127.0.0.1' (using password: YES)

(iii) Other explanations

1. Some students may mistakenly think that max_connection_errors can control the number of attempts of wrong passwords. In fact, this parameter can only prevent port probes such as telnet, that is, record the number of protocol handshake errors.

2. Finally, in the production environment, you must pay attention to the status of aborted_clients and aborted_connections. If an exception occurs, you must pay attention in time.

summary

The above is a small series to introduce MySQL control users to enter the wrong password attempt times, I hope to help you, if you have any questions please give me a message, small series will reply to you in time. Thank you very much for your support!

If you think this article is helpful to you, welcome to reprint, please indicate the source, thank you!

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.