Clear Memory Cache on Linux centos RESET QUERY CACHE will clear out the query cache, but needs RELOAD privileges. RESET QUERY CACHE; or FLUSH QUERY CACHE does NOT clear out the query cache, it simply defrags it leaving the cached query results in place. FLUSH QUERY CACHE; [xyz-ihs snippet=”Discuss”]
In this post we will see how can we optimize where clause. Where clause restrict the result set to a range. Basically this is used to access the range of a table/view. The range access method uses a single index to retrieve a subset of table rows that are contained within one or several index … Read more
Some of the optimizations performed by MySQL on where clauses as follow: Removal of unnecessary parentheses: ((a AND b) AND c OR (((a AND b) AND (c AND d)))) To (a AND b AND c) OR (a AND b AND c AND d) Constant folding: (a<b AND b=c) AND a=5 To b>5 AND b=c AND … Read more
The core logic of a database application is performed through SQL statements, whether issued directly through an interpreter or submitted behind the scenes through an API. The tuning guidelines of this post will help to speed up all kinds of MySQL applications. The guidelines cover SQL operations that read and write data, the behind-the-scenes overhead … Read more
Database performance depends on several factors at the database level, such as tables, queries, and configuration settings. These software constructs result in CPU and I/O operations at the hardware level, which you must minimize and make as efficient as possible. As you work on database performance, you start by learning the high-level rules and guidelines … Read more
If you have to restore MyISAM tables that have become corrupt, try to recover them using REPAIR TABLE or myisamchk -r That should work in 99.9% of all cases. In this article we will cover to repair/recover MyISAM tables using myisamchk. MyISAM tables have .MYD and .MYI files for storing data and indexes. You can use … Read more
MySQL Server has several logs that can help you find out what activity is taking place. By Default , No Logs are enabled in MySQL. By default, the server writes files for all enabled logs in the data directory. Logs can be flush by Issuing FLUSH LOGS statement. Binary log is flushed when its size … Read more
To learn more about what is binary log and how to setup Click here MySQL binary log : mysqlbinlog utility mysqldump – MySQL Database Backup and restore program Purge Binary log MySQL Master-Master-Slave-Slave Replication Database Recover from MySQL binary log: Binary logs store all the queries (in STATEMENT format) or row changes (in ROW format) … Read more
Mysql binary log is a special kind of logging facility provided by MySql by which we can record the log of the database changes statement along with the moment when statement get executed. We can log all insert, update and delete statement of the database in the mysql binary log. What is MySQL binary log? … Read more
A Shell script for killing slow MySQL queries: #!/bin/sh # Credentials for a MySQL user with PROCESS, SUPER permissions USERNAME= PASSWORD= # MySQL Server location HOST= PORT=3306 TIMEOUT=60 # 1 minute TARGET_USER= # MySQL user to monitor MYSQL=”mysql -u $USERNAME –password=$PASSWORD -h $HOST -P $PORT -B” $MYSQL -N -e ‘SHOW FULL PROCESSLIST’ | cut -f … Read more