MariaDB Crash Course - Ben Forta [86]
• If you delete large amounts of data from a table, OPTIMIZE TABLE should be used to reclaim previously used space, thus optimizing the performance of the table.
Diagnosing Startup Problems
Server startup problems usually occur when a change has been made to MariaDB configuration or the server itself. MariaDB reports errors when this occurs, but because most MariaDB servers are started automatically as system processes or services, these messages might not be seen.
When troubleshooting system startup problems, try to manually start the server first. The MariaDB server itself is started by executing mysqld on the command line. Here are several important command mysqld line options:
• --help displays help, a list of options.
• --safe-mode loads the server minus some optimizations.
• --verbose displays full text messages (use in conjunction with --help for more detailed help messages).
• --version displays version information and then quits.
Several additional command line options (pertaining to the use of log files) are listed in the next section.
Review Log Files
MariaDB maintains a series of log files that administrators rely on extensively. The primary log files are
• The error log contains details about startup and shutdown problems and any critical errors. The log is usually named hostname.err in the data directory. This name can be changed using the --log-error command line option.
• The query log logs all MariaDB activity and can be useful in diagnosing problems. This log file can get large quickly, so it should not be used for extended periods of time. The log is usually named hostname.log in the data directory. This name can be changed using the --log command line option.
• The binary log logs all statements that updated (or could have updated) data. The log is usually named hostname-bin in the data directory. This name can be changed using the –-log-bin command line option.
• As its name suggests, the slow query log logs any queries that execute slowly. This log can be useful in determining where database optimizations are needed. The log is usually named hostname-slow.log in the data directory. This name can be changed using the --log-slow-queries command line option.
When logging is being used, the FLUSH LOGS statement can be used to flush and restart all log files.
Summary
In this chapter, you learned some basic MariaDB database maintenance tools and techniques.
30. Improving Performance
In this chapter, you review some important points pertaining to the performance of MariaDB.
Improving Performance
Database administrators spend a significant portion of their lives tweaking and experimenting to improve DBMS performance. Poorly performing databases (and database queries, for that matter) tend to be the most frequent culprits when diagnosing application sluggishness and performance problems.
What follows is not, by any stretch of the imagination, the last word on MariaDB performance. This is intended to review key points made in the previous 29 chapters, as well as to provide a springboard from which to launch performance optimization discussion and analysis.
So, here goes:
• First and foremost, MariaDB (like all DBMSs) has specific hardware recommendations. Using any old computer as a database server is fine when learning and playing with MariaDB. But production servers should adhere to all recommendations.
• As a rule, critical production DBMSs should run on their own dedicated servers.
• MariaDB is preconfigured with a series of default settings that are usually a good place to start. But after a while you might need to tweak memory allocation, buffer sizes, and more. (To see the current settings use SHOW VARIABLES; and SHOW STATUS;.)
• MariaDB is a multiuser multithreaded DBMS; in other words, it often performs multiple tasks at the same time. And if one of