February 23, 2020
Disable mySQL Strict Mode cPanel
November 25, 2019
October 3, 2017
September 29, 2015
mysqltuner
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod 755 mysqltuner.pl
./mysqltuner.pl > mysqlturner.txt
January 12, 2015
November 22, 2013
MySQL error ./eximstats/smtp’ is marked as crashed
Mysql error log:
[ERROR] /usr/sbin/mysqld: Table './eximstats/smtp' is marked as crashed and should be repaired
Perbaiki dengan
myisamchk -r /var/lib/mysql/eximstats/smtp.MYI
Nanti akan muncul pesan
- recovering (with sort) MyISAM-table '/var/lib/mysql/eximstats/smtp.MYI' Data records: 461204 - Fixing index 1 - Fixing index 2 - Fixing index 3 - Fixing index 4 - Fixing index 5 - Fixing index 6 - Fixing index 7
April 5, 2012
‘./eximstats/sends’ is marked as crashed
MySql Error
[ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and last (automatic?) repair failed
Repair from command line ssh
myisamchk -r /var/lib/mysql/eximstats/sends.MYI
March 23, 2012
Tuning MySQL Performance with MySQLTuner
You can download the MySQLTuner script as follows:
wget http://mysqltuner.com/mysqltuner.pl
In order to run it, we must make it executable:
chmod +x mysqltuner.pl
Afterwards, we can run it. You need your MySQL root password for it:
./mysqltuner.pl
MySql Configuration
[mysqld]
safe-show-database
skip-locking
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
skip-networking
##used in replication to give each master and slave a unique identity
server-id=1
##amount of seconds during inactivity that MySQL will wait before it will close a connection on a non-interactive connection.
wait_timeout=5
##amount of seconds during inactivity that MySQL will wait before it will close a connection on interactive connection, interactive is mysql shell sessions
interactive_timeout=28800
##The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake
connect_timeout=5
##If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections
max_connect_errors=10
#The maximum number of simultaneous connections permitted to any given MySQL user account
max_user_connections=15
##This value indicates how many maximum concurrent connections mysql server can handle. If mysql reaches to it maximum (max) limit then you can see errors like "too many connections".
max_connections=200
##key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.
key_buffer_size=128M
#The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.
join_buffer_size=1M
##Each session that needs to do a sort allocates a buffer of this size
sort_buffer_size=2M
##Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans.
read_buffer_size=131072
#The maximum size of one packet or any generated/intermediate string.
max_allowed_packet=2M
##The number of open tables for all threads
table_open_cache=2048
##which sets the number of threads to hold open in memory to service new connections
thread_cache_size=16
##enables applications to give the threads system a hint about the desired number of threads that should be run at the same time.
thread_concurrency=10
##Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE.
query_cache_type=1
##Do not cache results that are larger than this number of bytes
query_cache_limit=1M
##The amount of memory allocated for caching query results
query_cache_size=128M
##The number of open tables for all threads
table_cache=5120
tmp_table_size=32M
max_heap_table_size=64M
innodb_buffer_pool_size=64M
innodb_additional_mem_pool_size=8M
innodb_log_file_size=8M
##If a query takes longer than this many seconds, the server increments the Slow_queries status variable
long_query_time=1
##to enable or disable the slow query log
slow_query_log=1
slow-query-log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
February 11, 2012
MySQL slow queries
1. Activate the logging of mysql slow queries.
mysqladmin var |grep log_slow_queries
| log_slow_queries | OFF |
If log_slow_queries is ON then we already have it enabled. This setting is by default disabled – meaning that if you don’t have log_slow_queries defined in the mysql server config this will be disabled.
The mysql variable long_query_time (default 1) defines what is considered as a slow query. In the default case, any query that takes more than 1 second will be considered a slow query.
Ok, now for the scope of this article we will enable the mysql slow query log. In order to do to do this in your mysql server config file (/etc/my.cnf RHEL/Centos or /etc/mysql/my.cnf on Debian, etc.) in the mysqld section we will add:
[mysqld]
long_query_time = 1
log-slow-queries = /var/log/mysql/mysql-slow.log
This configuration will log all queries that take more than 1 sec in the file /var/log/mysql/mysql-slow.log. You will probably want to define these based on your particular setup (maybe you will want the logs in a different location and/or you will consider a higher value than 1 sec to be slow query).
Once you have done the proper configurations to enable mysql to log slow queries you will have to reload the mysql service in order to activate the changes.
2. Investigate the mysql slow queries log.
After we enabled slow query logging we can look inside the log file for each slow query that was executed by the server. Various details are logged to help us understand how was the query executed:
Time: how long it took to execute the query
Lock: how long was a lock required
Rows: how many rows were investigated by the query (this can help see quickly queries without indexes)
Host: the actual host that launched the query (this can be localhost, or a different one in multiple servers setup)
The actual mysql query.
This information allows us to see what queries need to be optimized, but on a high traffic server and with lots of slow queries this log can grow up very fast making it very difficult to find any relevant information inside it.
In this case we have two choices:
We increase the long_query_time and we focus on the queries that take the most time to complete, and we gradually decrease this once we solve the queries.
We use some sort of tool to parse the slow query log file and have it show us the most used queries.