In MySQL 5 a new table type called the archive table type was introduced to deal with common requirements for web applications like access logs where only INSERTS and SELECTS were done.MyISAM exclusive table locks for updates before selects versus InnoDB's row level locking can mean MyISAM blocks reads if there are many writes. Performance sites should use InnoDB for most tables particularly tables such as node that get a lot of writes and reads. MySQL supports many different engine types including Archive (MySQL 5+), MyISAM, and InnoDB.If you have complete control of the database server you can use: mysqlcheck -o -A -p You can use the DB Maintenance module or use the mysqlcheck commands below in a cronjob.Įcho "OPTIMIZE TABLE accesslog,cache,comments,node,users,watchdog FLUSH TABLES " |mysql -u user -ppasswd.The biggest performance boosts can come from identifying and tuning the slowest queries using the MySQL slow query logs.Be sure to have enough cached threads or you will launch too many new threads as described in this story about a Yahoo site.Note: This is no longer recommended as query cache is deprecated as of MySQL 5.7.20, and was removed as of MySQL 8.0. In sites running MySQL 5.7.1 or older, if a query is called at least twice with no modifications to the queried tables a significant performance improvement may be gained by avoiding the processing of the query and the execution of the query by reading the query from the MySQL query cache.To reduce the overhead of creating connections we recommend using persistent DB connections.For servers with a lot of writes, we recommend my-innodb.cnf instead of the default MyISAM engine type. For servers with at least 2GB RAM dedicated to MySQL we recommend my-huge.cnf. Start with an appropriate MySQL option file.For a general overview read the High Performance MySQL book and/or the MySQL Performance Blog, especially the InnoDB performance basics article.
0 kommentar(er)
