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

This article is to share with you the content of the sample analysis of the database of Java interview questions. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

What would you do if the 1:MySQL database cpu soared to 500%?

When cpu soars to 500%, first use the operating system command top command to see if it is caused by mysqld occupancy. If not, find out the processes with high occupancy and deal with them.

If it is caused by mysqld, show processlist, check to see if there is a resource-consuming sql running in it. Find out the expensive sql and see if the index is not used or the IO is too large.

Mysql > show processlist +- -+ | Id | User | Host | db | Command | Time | State | Info | +- -+-+-- +-+ | 1 | event_scheduler | localhost | NULL | Daemon | 313 | Waiting for next activation | NULL | | 239896 | root | 192.168.1.21 NULL 55050 | finance | Sleep | 1160 | | | NULL | | 239898 | root | 192.168.1.21 Sleep 58118 | NULL | Sleep | 397 | NULL | 239899 | root | 192.168.1.21 NULL 58127 | csjdemo | Sleep | 393 | NULL | 239901 | root | 192.168.1.21 NULL 58135 | csjdemo | Sleep | 387 | NULL | 239901 | root | 192.168.1.21 root | csjdemo | Query | 1044 | | select * from T like `name` like'% Chen %'| 239904 | root | | Localhost | NULL | Query | 0 | starting | show processlist | +- -+-+ 6 rows in set (0.00 sec)

Show full processlist can see all the links, but the state of most links is actually Sleep, which is idle and does not have much viewing value. What we want to observe is problematic, so we can filter it:

-- query links with non-Sleep status, display them in reverse order in elapsed time, filter select id, db, user, host, command, time, state, infofrom information_schema.processlistwhere command! = 'Sleep'order by time descmysql > select id, db, user, host, command, time, state, infofrom information_schema.processlistwhere command! =' Sleep'order by time desc\ g + -+ | id | db | user | host | command | time | state | info | +- -+ | 1 | NULL | event_scheduler | localhost | Daemon | 515 | Waiting for next activation | NULL | | 239904 | NULL | root | localhost | Query | 1044 | executing | select * from T like `name` like'% Chen %'| + -+-- +-+ 2 rows in set (0.00 sec)

In this way, you can filter out what is working, and then show it in flashback according to the elapsed time. The most likely link in the list is the problematic link, and then you can see what specific SQL statements are executed in the info column, for analysis.

In general, kill these threads (and see if cpu usage drops), and then rerun the SQL after making appropriate adjustments (such as indexing, changing sql, changing memory parameters).

It is also possible that each sql does not consume much resources, but all of a sudden, a large number of session connections cause the cpu to soar, so you need to work with the app to analyze why the number of connections has surged, and then make adjustments accordingly, such as limiting the number of connections.

Interview question 2: what is a stored procedure? What are the advantages and disadvantages?

A stored procedure (Procedure) is one or more precompiled SQL statements, a set of SQL statements that are stored in the database in order to perform a specific function and are permanently valid after compilation. The user executes the stored procedure by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters).

Advantages centralize business logic in the database

We can use stored procedures to implement business logic that can be used by multiple SQL, and stored procedures help reduce the need to repeat the same logic in many applications.

Make the database more secure

Database administrators can grant appropriate privileges to applications that access only specific stored procedures without granting any privileges on the underlying table.

Faster execution speed

If an operation contains a large amount of Transaction-SQL code or is executed multiple times separately, the stored procedure executes much faster than batch processing. Because the stored procedure is precompiled. When a stored procedure is run for the first time, the optimizer analyzes and optimizes it, and gives the execution plan that is eventually stored in the system table. Batch Transaction-SQL statements are compiled and optimized each time they are run, which is relatively slow.

Disadvantages are not portable

The stored procedures for each database are different, and if MySQL uses a large number of stored procedures, you will find it impractical when you want to switch to Oracle.

Complex stored procedures consume a lot of resources

If the stored procedure is logically complex and contains multiple SQL, the memory usage of each connection may be greatly increased and the execution time will be long, so be prepared.

Troubleshooting is difficult

Debugging stored procedures is difficult. Unfortunately, MySQL does not provide any debug stored procedures like other enterprise database products such as Oracle and SQL Server. Stored procedures may encapsulate many business details, which may make it difficult for developers to understand the business. Imagine a stored procedure with hundreds of lines left behind by your predecessors. Are you confused when the boss suddenly asks you to change the implementation logic?

High maintenance cost

Developing and maintaining stored procedures may not be handled by non-professionals, and it is easy for beginners to stay in the hole or waste a lot of time.

General business logic should not use stored procedures as far as possible. Real-time ETL tasks or report statistics functions can be processed by stored procedures according to team resources. Stored procedures can quickly solve problems, but portability, maintainability and expansibility are not good. It sometimes restricts the architecture of the software and speeds up the thinking of programmers. It is not recommended to use stored procedures when your system has no performance problems. Stored procedures can also come in handy if you only need to complete one or a limited number of tasks, such as data correction, data migration, and so on.

If your system is small and 50% of the developers are proficient in PL/SQL and have a stable personnel structure, stored procedures can reduce a lot of code and perform well. When the system becomes more complex and there are more developers, the disadvantages of stored procedures will appear, and you need to make up your mind.

Interview question 3: for example, if there is a user table and the ID number field is unique, then if you build an index based on this field, what will you consider in terms of efficiency?

The answer is based on Lin Xiaobin's 45 lectures on MySQL.

If the business code has ensured that duplicate ID numbers will not be written, then both choices are logically correct. In terms of efficiency, the main focus is on SELECT and UPDATE operations.

For a SELECT query:

Suppose that the statement that executes the query is select id from T where id=5. The query statement looks up the process on the index tree, first starts from the root of the tree through the B+ tree, searches for the leaf node by layer, then takes out the data page where the leaf node is located (first determines whether there is the page in the changebuffer memory, and reads the memory from the disk first), and finally locates the row data of id=5 in the data page by dichotomy.

For a normal index: look up the first id=5, and then continue searching until the first record of idled indexing 5 is reached.

For a unique index: because the index defines uniqueness, it ends directly after finding the first record that meets the criteria.

What is the performance gap between the two? very little. For an ordinary index, because it is read into memory in terms of data pages, and the data page size defaults to 16KB (about 1000 rows), the extra "find and judge the next record" operation requires only a pointer search and a calculation.

For a UPDATE query:

When a data page needs to be updated, the data page is updated directly in memory, and if the data page is not already in memory, InnoDB caches these update operations in change buffer without affecting data consistency, so that the data page does not need to be read from disk. The next time the query needs to access the data page, read the data page into memory, and then perform the actions related to the page in change buffer. In this way, the correctness of the data logic can be guaranteed. To be clear, although it is called change buffer, it is actually data that can be persisted. In other words, the change buffer is copied in memory and is written to disk.

The process of applying the operations in change buffer to the original data page and getting the latest results is called merge. In addition to (SELECT) access to this data page will trigger merge, the system has background threads will periodically merge. Merge operations are also performed during a normal database shutdown (shutdown).

Obviously, if you can record the update operation in change buffer first and reduce the read disk, the execution speed of the statement will be significantly improved. Moreover, data reading into memory needs to take up buffer pool, so this approach can also avoid memory consumption and improve memory utilization.

So, under what conditions can you use change buffer? For unique indexes, all update operations must first determine whether the operation violates the uniqueness constraint. For example, to insert the id=5 record, it is necessary to determine whether the id=5 record already exists in the table, which must be read into memory. If you have already read into memory, it will be faster to update memory directly, so there is no need to use change buffer.

Therefore, updates to unique indexes cannot use change buffer, and in fact only ordinary indexes can be used.

Change buffer uses memory in buffer pool, so it cannot be increased indefinitely. The size of the change buffer can be dynamically set by the parameter innodb_change_buffer_max_size. When this parameter is set to 50, it means that the size of the change buffer can only occupy up to 50% of the buffer pool.

So if you want to insert a new record (5, "Chen ") into this table (id,name), what is the processing flow of InnoDB?

In the first case, the target page to be updated for this record is in memory. At this point, the processing flow of InnoDB is as follows:

For a unique index, find the position between 3 and 5, determine that there is no conflict, insert this value, and the statement execution ends

For a normal index, find the position between 3 and 5, insert this value, and the statement execution ends. From this point of view, the difference in the impact of a normal index and a unique index on the performance of update statements is only a judgment and consumes only a small amount of CPU time.

In the second case, the target page to be updated for this record is not in memory. At this point, the processing flow of InnoDB is as follows:

For a unique index, you need to read the data page into memory, determine that there is no conflict, insert this value, and the statement execution ends.

For a normal index, the update is recorded in change buffer, and the statement execution ends.

Reading data from disk to memory involves random IO access, which is one of the most expensive operations in the database. Because change buffer reduces random disk access, the performance improvement for updates will be significant.

In an incident that happened to me before, a classmate of DBA told me that the library memory hit rate of a business he was in charge of had suddenly dropped from 99% to 75%, the whole system was blocked, and all update statements were blocked. After exploring the reasons, I found that the business had a large number of operations to insert data, and he had changed one of the ordinary indexes to a unique index the day before.

Thank you for reading! This is the end of the article on "sample analysis of the database of Java interview questions". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.