ysql is a superior open source database. With immense and intense database driven applications people are pushing Mysql beyond its limits. Running MySQL at desirable settings for specific resources helps to handle large server loads and prevents server slowdown. In case you want your server to perform optimally, the best solution is to get tons of memory and big, rapid drives. When we are enlisted for a MySQL execution review, we are relied upon to survey the MySQL setup and to propose enhancements. MySQL exposes a large number of variables whose values can be modified to fulfill custom necessities.
Given below are some of the MySQL performance tuning settings that you should always look at.
The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk as soon as after which having access to the records from memory at some point of subsequent reads. Typical values are 5-6GB(8GB Ram),20-25GB(32GB Ram), 100-120GB(128GB Ram).
Click here to get about more info : linode cloud support
This is the size of the redo logs. The redo logs are used to ensure writes are fast and desirable and also during crash recovery. Fortunately, crash recovery performance has stepped forward a lot since Mysql5.5. So you can now have good write overall performance and rapid crash recovery. Until Mysql5.5 the entire redo log size was restricted to 4GB. This has been lifted in Mysql5.6.
If you get “too many connections” error in MySQL that means the max connection is too low or the number of available connections is in use by other clients on the server. So in this case, you need much more than the default 151 connections. The main drawback of high values for maxconnection is that server turns into unresponsive if for any motive it has to run 1000 or more active transactions.
when tuning server variables for maximum performance, the MySQL manual recommends that you first look at the keybuffersize and tablecache variables.
This variable determines the size of the index buffers held in memory, which affects the speed of index reads. Typically, you would want to keep this value near 25 to 30% of the total available memory on the server.
This indicates the maximum number of tables that the server can keep open in any one table cache instance. If the tableopencache value is set to high, MariaDB may start to refuse connections as the operating system runs out of file descriptors.
All InnoDB tables and indexes are stored in the system tablespace. innodbfilepertable tablespaces provide an alternative, where InnoDB table and its indexes are stored in a separate .ibd data file. Each .ibd data file represents an individual tablespace. This feature is controlled by the innodbfilepertable configuration option, which is enabled by default. innodbfilepertable tells InnoDB if it should store data and indexes in shared tablespace(innodbfilepertable=off) or in a separate .ibd file for each table(innodbfilepertable =on). One of the advantages is that it may improve the chances of a successful recovery and save time when corruption occurs.
To get about more info click here : How to Secure WordPress Websites
If innodbflushlogattrxcommit=0 then InnoDB writes the modified data to log file and flush the log file every second, but it will not do anything at transaction commit. If in case the system crashes, all the unflushed data will not be recoverable, since it is not written to log file or stored disk. If innodbflushlogattrxcommit=1 then InnoDB writes the log buffer to the transaction log and flush to durable storage for every transaction. If innodbflushlogattrxcommit=2 then InnoDB writes the log buffer to a log file at every commit but don’t write data to disk. InnoDB flush data once every second. Even if the system crashes, data in the log file will be available and can be recoverable.
If you want the server to act as replication master then enabling binary logging is essential and also don’t forget to set serverid to a unique value. If binary logging is enabled, the server logs all statements that change data to the binary log, which is used for backup and replication.
To set the size of query cache, set query cache size system variable. Setting querycachesize=0 disables the query cache. Zero is the default size, so query cache is disabled by default. To increase the performance of the MySQL server, query cache may be turned on. It specifies the size of the cache. The query cache needs a size of 40KB to allocate its structure.
If the system variable querycachetype is set to 1, all queries fitting the size constraints will be stored in the cache unless they contain an SQLNOCACHE clause. The querycachetype server variable must be set to 2, or demand in order to specify that no queries must be stored in the cache unless the query requires it. Then, only queries with the SQLCACHE attribute are cached. To set the size of the query cache, set the querycachesize system variable. Setting it to 0 disables the query cache, as does setting querycachetype=0. The query cache is disabled by default.
0 
Star