- In addition, many of the configuration options can be controlled at server runtime.. - Another function of the binary log is to enable replication. - The contents of the binary log are any statements that occur in the server that could potentially modify the databases. - The binary log index file is a plain text file that keeps track of the current binary logs. - To set the filename and path of the binary logs and binary log index file, specify the following options:. - The operation of the mysqlbinlog tool is pretty straightforward.. - In the following example the entire contents of the mysql-bin.00001 binary log are converted into text format and copied into a new file called output.sql. - In addition to the relay logs there is the relay-log.index file, which is used to keep track of the currently used relay log. - Relay logs are automatically deleted by the SQL thread of the slave server when it has executed all events in the log and it is no longer needed.. - Specifies the location of the general query log file. - Specifies the location of the slow query log file. - The long_query_time server variable is used to control the granularity of the logging of slow queries. - If the logs are disabled, no logging occurs regardless of the value of log_output . - When this is done the server closes the binary log currently in use and creates a new binary log by incrementing the sequence number of the file by one (in relation to the previous log). - With the flushing of the error log the server:. - The option name is a bit of a misnomer because the binary logs are rotated after the size is reached, so the size of the file may be slightly larger than max_binlog_size . - It was ported to version 6.0 of the server as plugin components. - You need to install the libsemisync_master* files in the plugin directory of the master server and the libsemisync_slave* files in the plugin directory of each slave server. - If you do not know the location of the plugin directory, the command SHOW GLOBAL VARIABLES LIKE. - ’plug% should tell the location of the plugin directory. - On the master:. - Upon execution of the INSERT DELAYED statement.. - Often administrators will use the last octet of the server’s IP address because this will typically be unique from server to server. - To use more unique integers, the serial number of the system or the entire IP address could be used, such as or even the IP address plus the port, such as . - You will need to replace host with the hostname or IP address of the slave server.. - Most of the configuration on a slave server is done dynamically using the CHANGE MASTER command.. - The basic idea is that you need to take a consistent backup of the master, noting down at what point in the master binary logs the backup was taken.. - The --master-data option to mysqldump will automatically store a CHANGE MASTER TO statement at the end of the export. - Master_Host The hostname or IP address of the master server.. - Relay_Log_Pos The current position of the SQL thread in the relay log.. - Last_Errno The error number of the last replication error that occurred.. - Last_Error A description of the last replication error that occurred. - Exec_Master_Log_Pos The position of the last event executed by the SQL thread from the master binary log.. - Seconds_Behind_Master Shows how far the SQL thread is behind the IO thread in execution of the relay logs. - Last_IO_Error A description of the last error encountered by the IO thread.. - Last_SQL_Error A description of the last error encountered by the SQL thread.. - Almost all of the fields listed in Table 16-3 can be used in the CHANGE MASTER TO statement. - Each of the following sections briefly describes an elaborate setup you can use as needed.. - As pointed out before, each server must have a unique server-id entry in the [mysqld] directive of the configuration file.. - The difference between this configuration and the standard configuration is the addition of the log-slave-updates and log-bin statements. - There are other reasons to enable the binary logs, including incremental backup, point-in-time recovery, and consistent configuration setups in the event that one of the slaves on the bottom is promoted to be a relay slave.. - This will improve the overall performance of the server. - At this point replication will break on one of the servers because it can not insert a row with the same ID.. - Two entries in the [mysqld] section of the configuration file are needed to resolve this issue:. - Like master-master replication, circular replication offers no performance gains for writes — even though the writes can be balanced among three or more servers, each server will have to perform all of the writes eventually, because the writes will come through replication. - Circular replication introduces a very large problem: A problem with one server can affect the rest of the servers. - This means that if a problem occurs — for example, a network issue causes Server A not to be able to connect to Server D to read the binary logs — the rest of the servers are affected.. - replication to be synchronous — the only reason mysqld has lasted so long without syn- chronous replication is that ordinarily statements are processed very quickly. - Most of the time, there is very little delay from the time a statement is executed on the master to the time it is executed on the slave.. - The premise of the mk-table-checksum tool is that it should calculate checksums for tables on both the master and slave servers. - Using a smaller chunksize may improve concurrency because less of the table is locked, but actually ends up slowing the process down.. - The use of the checksum table does help to minimize these effects.. - Benchmarking is the execution of a standard suite of programs in order to assess the performance of the system or systems being tested in a methodical manner relative to performance of other systems. - In the end, using benchmarks provides a better experience for the end user of the application.. - Many of the same techniques used to profile a server are used in benchmarking.. - Some of the tools that can be used to benchmark mysqld include mysqlslap , downloadable from http://dev.mysql.com/doc/refman/6.0/en/mysqlslap.html and sysbench , downloadable from http://sysbench.sourceforge.net. - --detach=num Each of the connections closes and reopens after execution of num statements.. - --host=name Specifies the hostname (name) of the mysqld that mysqlslap connects to for conducting test runs.. - --number-blob-cols=num Determines the number of BLOB fields used for. - --ssl-cert=file_name The filename of the SSL certificate used for SSL-based connections.. - --ssl-key=file_name The filename of the SSL key file used for SSL-based connections.. - --ssl-verify-server-cert When using this option mysqlslap verifies that the Common Name value of the server’s certificate is verified against the hostname when connecting to the server.. - Specifies the number of seconds to run each test. - Average number of queries per client: 2. - This is an important consideration, as it will allow for a more accurate view of the server’s actual ability. - The mysqlslap tool is one of the most flexible (and easiest to use) tools available for benchmarking servers.. - --max-requests Limits the total number of requests. - --num-threads Determines the total number of threads created.. - --percentile Display the average execution time of some percentile of the requests. - With the default of 95%, sysbench will drop 5 percent of the longest-running queries.. - The CPU test mode runs a calculation of the prime numbers up to the value specified by the --cpu-max-prime options. - This test focuses on the speed of the CPU and is useful for com- paring different processors. - The following example shows a run of the cpu test with a maximum prime number calculated of 20000:. - Number of threads: 1. - The I/O performance test mode is one of the most complex and comprehensive test modes avail- able in SysBench. - --file-fsync-end Performs a fsync() call at the end of the test. - Merges up to this number of I/O requests. - --file-num=num Specifies the number of files created for the test run.. - Determines the total size of the files generated for the test run.. - Number of threads: 2. - Number of threads: 32. - --memory-block-size=value Determines the size of the memory block to use for test.. - Number of threads: 64. - --thread-locks=num Specifies the number of locks for each thread. - 8 --thread-yields=num Specifies the number of lock/yield/unlock loops. - Specifies the number of DISTINCT range queries in a transaction.. - Specifies the number of UPDATE queries using an index in a transaction.. - Specifies the number of UPDATE queries that do not use indexes in a transaction.. - Specifies the number of ORDER range queries in transaction.. - Specifies the number of point select queries in a transaction.. - --oltp-simple-ranges Specifies the number of range queries in a transaction.. - --oltp-sp-name=value Specifies the name of the stored procedure sysbench should use when called in sp test mode.. - Determines the name of the table used by sysbench.. - --oltp-table-size=num Determines the number of rows in the test table.. - Number of threads: 16 Doing OLTP test.. - Regardless of the database you choose to use, it must already be created before the test run begins and the user must have the appropriate permissions on the database. - When performing benchmarks, regardless of the program used there are several principles that you should keep in mind. - Alternatively, if the performance is severely impacted or gets a lot better, you have no idea if it was because one variable, the other, or the combination of the two.. - Just as mysqld has a BENCHMARK() function that can benchmark based on one expression, there is a SHOW PROFILE statement that gives a description of the profile of a particular query. - Their values are the current values of the system counter for each variable
Xem thử không khả dụng, vui lòng xem tại trang nguồn hoặc xem
Tóm tắt