Vesta Control Panel - Forum

Code: Select all

root@s165254:/var/log# ./mysqltuner.pl
bash: ./mysqltuner.pl: No such file or directory
root@s165254:/var/log#
root@s165254:~# ./mysqltuner.pl
 >>  MySQLTuner 1.7.15 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
[OK] Currently running supported MySQL version 5.7.27-0ubuntu0.18.04.1
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(7K)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[!!] /var/log/mysql/error.log contains 2 warning(s).
[!!] /var/log/mysql/error.log contains 1 error(s).
[--] 1 start(s) detected in /var/log/mysql/error.log
[--] 1) 2019-08-25T07:10:07.276274Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 1 shutdown(s) detected in /var/log/mysql/error.log
[--] 1) 2019-08-25T07:08:32.295588Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 1.3G (Tables: 510)
[--] Data in MyISAM tables: 685.1M (Tables: 59)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (IF(plugin='mysql_native_password', authentication_string, password) = '' OR IF(plugin='mysql_native_password', authentication_string, password) IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket', 'auth_pam_compat')
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE CAST(IF(plugin='mysql_native_password', authentication_string, password) as Binary) = PASSWORD(user) OR CAST(IF(plugin='mysql_native_password', authentication_string, password) as Binary) = PASSWORD(UPPER(user)) OR CAST(IF(plugin='mysql_native_password', authentication_string, password) as Binary) = PASSWORD(CONCAT(UPPER(LEFT(User, 1)), SUBSTRING(User, 2, LENGTH(User))))
[!!] FAIL Execute SQL / return code: 256
[!!] User 'admin_1@%' does not specify hostname restrictions.
[!!] User 'admin_default@%' does not specify hostname restrictions.
[!!] User 'club-pc_1@%' does not specify hostname restrictions.
[!!] User 'spinaz_1@%' does not specify hostname restrictions.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 58m 35s (457K q [130.048 qps], 4K conn, TX: 5G, RX: 66M)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is disabled
[--] Physical Memory     : 1.9G
[--] Max MySQL memory    : 264.5M
[--] Other process memory: 0B
[--] Total buffers: 177.0M global + 1.8M per thread (50 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 192.8M (9.67% of installed RAM)
[OK] Maximum possible memory usage: 264.5M (13.27% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/457K)
[OK] Highest usage of available connections: 18% (9/50)
[OK] Aborted connections: 0.02%  (1/4388)
[--] Skipped name resolution test due to skip_networking=ON in system variables.
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 408K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 62K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 45% (12K on disk / 27K total)
[OK] Thread cache hit rate: 99% (9 created / 4K connections)
[!!] Table cache hit rate: 0% (64 open / 114K opened)
[OK] Open file limit used: 0% (36/5K)
[OK] Table locks acquired immediately: 99% (106K immediate / 106K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 22.0% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/195.9M
[OK] Read Key buffer hit rate: 99.1% (9M cached / 82K reads)
[OK] Write Key buffer hit rate: 99.0% (10K cached / 10K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/1.3G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.21% (12689353 hits/ 12790696 total)
[!!] InnoDB Write Log efficiency: 67.63% (26789 hits/ 39610 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12821 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mysql/error.log file
    Control error line(s) into /var/log/mysql/error.log file
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: https://bit.ly/1mi7c4C
    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (5000) variable
    should be greater than table_open_cache (64)
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 64)
    innodb_buffer_pool_size (>= 1.3G) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

Code: Select all

2019-08-25T04:55:05.873718Z 342716 [Note] Aborted connection 342716 to db: 'ctokukol.ru_2' user: 'ctokukol.ru_2' host: 'localhost' (Got timeout reading communication packets)
2019-08-25T05:45:41.823900Z 345417 [Note] Aborted connection 345417 to db: 'ctokukol.ru_2' user: 'ctokukol.ru_2' host: 'localhost' (Got timeout reading communication packets)
2019-08-25T07:08:30.059347Z 0 [Note] Giving 0 client threads a chance to die gracefully
2019-08-25T07:08:30.059742Z 0 [Note] Shutting down slave threads
2019-08-25T07:08:30.060155Z 0 [Note] Forcefully disconnecting 0 remaining clients
2019-08-25T07:08:30.060431Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2019-08-25T07:08:30.061996Z 0 [Note] Binlog end
2019-08-25T07:08:30.095490Z 0 [Note] Shutting down plugin 'auth_socket'
2019-08-25T07:08:30.095525Z 0 [Note] Shutting down plugin 'ngram'
2019-08-25T07:08:30.095529Z 0 [Note] Shutting down plugin 'partition'
2019-08-25T07:08:30.095540Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2019-08-25T07:08:30.095854Z 0 [Note] Shutting down plugin 'ARCHIVE'
2019-08-25T07:08:30.095863Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2019-08-25T07:08:30.096646Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2019-08-25T07:08:30.096864Z 0 [Note] Shutting down plugin 'MyISAM'
2019-08-25T07:08:30.096882Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2019-08-25T07:08:30.097357Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2019-08-25T07:08:30.097366Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2019-08-25T07:08:30.097369Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2019-08-25T07:08:30.097379Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2019-08-25T07:08:30.097514Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2019-08-25T07:08:30.097522Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2019-08-25T07:08:30.097525Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2019-08-25T07:08:30.097836Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2019-08-25T07:08:30.097842Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2019-08-25T07:08:30.097845Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2019-08-25T07:08:30.097848Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2019-08-25T07:08:30.097858Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2019-08-25T07:08:30.097861Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2019-08-25T07:08:30.097864Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2019-08-25T07:08:30.097867Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2019-08-25T07:08:30.097870Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2019-08-25T07:08:30.097873Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2019-08-25T07:08:30.097877Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2019-08-25T07:08:30.097880Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2019-08-25T07:08:30.097882Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2019-08-25T07:08:30.097885Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2019-08-25T07:08:30.097888Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2019-08-25T07:08:30.097891Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2019-08-25T07:08:30.097894Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2019-08-25T07:08:30.097897Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2019-08-25T07:08:30.097900Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2019-08-25T07:08:30.097903Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2019-08-25T07:08:30.097906Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2019-08-25T07:08:30.097909Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2019-08-25T07:08:30.097912Z 0 [Note] Shutting down plugin 'InnoDB'
2019-08-25T07:08:30.353169Z 0 [Note] InnoDB: FTS optimize thread exiting.
2019-08-25T07:08:30.353688Z 0 [Note] InnoDB: Starting shutdown...
2019-08-25T07:08:30.455089Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2019-08-25T07:08:30.459982Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 190825 10:08:30
2019-08-25T07:08:32.281519Z 0 [Note] InnoDB: Shutdown completed; log sequence number 10868768984
2019-08-25T07:08:32.282426Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2019-08-25T07:08:32.282439Z 0 [Note] Shutting down plugin 'MEMORY'
2019-08-25T07:08:32.282445Z 0 [Note] Shutting down plugin 'CSV'
2019-08-25T07:08:32.282456Z 0 [Note] Shutting down plugin 'sha256_password'
2019-08-25T07:08:32.282459Z 0 [Note] Shutting down plugin 'mysql_native_password'
2019-08-25T07:08:32.286137Z 0 [Note] Shutting down plugin 'binlog'
2019-08-25T07:08:32.295588Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2019-08-25T07:10:07.037096Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-08-25T07:10:07.038215Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.27-0ubuntu0.18.04.1) starting as process 589 ...
2019-08-25T07:10:07.042638Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-08-25T07:10:07.042664Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-08-25T07:10:07.042668Z 0 [Note] InnoDB: Uses event mutexes
2019-08-25T07:10:07.042672Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-08-25T07:10:07.042676Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-08-25T07:10:07.042682Z 0 [Note] InnoDB: Using Linux native AIO
2019-08-25T07:10:07.043071Z 0 [Note] InnoDB: Number of pools: 1
2019-08-25T07:10:07.044138Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-08-25T07:10:07.045272Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-08-25T07:10:07.052093Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-08-25T07:10:07.054525Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-08-25T07:10:07.070027Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-08-25T07:10:07.157806Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-08-25T07:10:07.157881Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-08-25T07:10:07.177430Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-08-25T07:10:07.178057Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-08-25T07:10:07.178065Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-08-25T07:10:07.178431Z 0 [Note] InnoDB: Waiting for purge to start
2019-08-25T07:10:07.228591Z 0 [Note] InnoDB: 5.7.27 started; log sequence number 10868768984
2019-08-25T07:10:07.228754Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2019-08-25T07:10:07.228927Z 0 [Note] Plugin 'FEDERATED' is disabled.
2019-08-25T07:10:07.242750Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2019-08-25T07:10:07.276104Z 0 [Note] Event Scheduler: Loaded 0 events
2019-08-25T07:10:07.276274Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.27-0ubuntu0.18.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  (Ubuntu)
2019-08-25T07:10:08.451205Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190825 10:10:08

Code: Select all

[client]
port=3306
socket=/var/run/mysqld/mysqld.sock

[mysqld_safe]
socket=/var/run/mysqld/mysqld.sock

[mysqld]
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
port=3306
basedir=/usr
datadir=/var/lib/mysql
tmpdir=/tmp
lc-messages-dir=/usr/share/mysql
log_error=/var/log/mysql/error.log

symbolic-links=0
skip-name-resolve=1
skip-networking
  
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 32M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

#innodb_use_native_aio = 0
innodb_file_per_table

max_connections=50
max_user_connections=0
wait_timeout=10
interactive_timeout=50
long_query_time=5

!includedir /etc/mysql/conf.d/