Différences

Ci-dessous, les différences entre deux révisions de la page.

--- mysqltuner_optimisation [2023/07/04 18:42] – [MariaDB] pascal
+++ mysqltuner_optimisation [2023/07/04 19:27] (Version actuelle) – [Sujets connexe] pascal
@@ Ligne 1: / Ligne 1: @@
 ====== Optimisation avec mysqltuner ======
-===== Commande =====
+===== Installation =====
 <code bash>
-~# apt-get install mariadb-server mariadb-client
+~# apt-get install mysqltuner
 </code>
-La version installée est la 1:10.11.3-1.
-===== Contrôle =====
-==== Lancement du client ====
-<code bash>
-root:~# mariadb
-Welcome to the MariaDB monitor.  Commands end with ; or \g.
-Your MariaDB connection id is 31
-Server version: 10.11.3-MariaDB-1 Debian 12
-Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
+===== Lancement =====
+<file bash>
+root:~# mysqltuner
+ >>  MySQLTuner 1.9.9
+	 * Jean-Marie Renouard <jmrenouard@gmail.com>
+	 * Major Hayden <major@mhtx.net>
+ >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
+ >>  Run with '--help' for additional options and output filtering
-Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
+[--] Skipped version check for MySQLTuner script
+[OK] Logged in using credentials from Debian maintenance account.
+[!!] Your MySQL version 10.11.3-MariaDB-1 is EOL software!  Upgrade soon!
+[OK] Operating on 64-bit architecture
+-------- Log file Recommendations ------------------------------------------------------------------
+[!!] Log file  doesn't exist
+-------- Storage Engine Statistics -----------------------------------------------------------------
+[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
+[--] Data in Aria tables: 32.0K (Tables: 1)
+[!!] InnoDB is enabled but isn't being used
+[OK] Total fragmented tables: 0
+-------- Analysis Performance Metrics --------------------------------------------------------------
+[--] innodb_stats_on_metadata: OFF
+[OK] No stat updates during querying INFORMATION_SCHEMA.
+-------- Views Metrics -----------------------------------------------------------------------------
+-------- Triggers Metrics --------------------------------------------------------------------------
+-------- Routines Metrics --------------------------------------------------------------------------
+-------- Security Recommendations ------------------------------------------------------------------
+[OK] There are no anonymous accounts for any database users
+[OK] All database users have passwords assigned
+[--] There are 620 basic passwords in the list.
+-------- CVE Security Recommendations --------------------------------------------------------------
+[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
+-------- Performance Metrics -----------------------------------------------------------------------
+[--] Up for: 1h 20m 25s (1K q [0.286 qps], 691 conn, TX: 285K, RX: 271K)
+[--] Reads / Writes: 99% / 1%
+[--] Binary logging is disabled
+[--] Physical Memory     : 3.9G
+[--] Max MySQL memory    : 3.2G
+[--] Other process memory: 0B
+[--] Total buffers: 417.0M global + 18.9M per thread (151 max threads)
+[--] P_S Max memory usage: 0B
+[--] Galera GCache Max memory usage: 0B
+[OK] Maximum reached memory usage: 435.9M (11.00% of installed RAM)
+[OK] Maximum possible memory usage: 3.2G (82.55% of installed RAM)
+[OK] Overall possible memory usage with other process is compatible with memory available
+[OK] Slow queries: 0% (0/1K)
+[OK] Highest usage of available connections: 0% (1/151)
+[OK] Aborted connections: 0.00%  (0/691)
+[!!] Name resolution is active: a reverse name resolution is made for each new connection and can reduce performance
+[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
+[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2 sorts)
+[OK] No joins without indexes
+[OK] Temporary tables created on disk: 16% (44 on disk / 260 total)
+[OK] Thread cache hit rate: 99% (3 created / 691 connections)
+[OK] Table cache hit rate: 56% (1K hits / 3K requests)
+[OK] table_definition_cache (400) is greater than number of tables (292)
+[OK] Open file limit used: 0% (59/32K)
+[OK] Table locks acquired immediately: 100% (751 immediate / 751 locks)
+-------- Performance schema ------------------------------------------------------------------------
+[!!] Performance_schema should be activated.
+[--] Sys schema is installed.
+-------- ThreadPool Metrics ------------------------------------------------------------------------
+[--] ThreadPool stat is disabled.
+-------- MyISAM Metrics ----------------------------------------------------------------------------
+[--] No MyISAM table(s) detected ....
+-------- InnoDB Metrics ----------------------------------------------------------------------------
+[--] InnoDB is enabled.
+[!!] No tables are Innodb
+[OK] InnoDB File per table is activated
+[OK] InnoDB buffer pool / data size: 128.0M/0B
+[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 96.0M * 1/128.0M should be equal to 25%
+[--] Number of InnoDB Buffer Pool Chunk: 64 for 1 Buffer Pool Instance(s)
+[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
+[!!] InnoDB Read buffer efficiency: -2650.00% (-159 hits/ 6 total)
+[!!] InnoDB Write Log efficiency: 0% (0 hits/ 0 total)
+Illegal division by zero at /usr/bin/mysqltuner line 6099 (#1)
+    (F) You tried to divide a number by 0.  Either something was wrong in
+    your logic, or you need to put a conditional in to guard against
+    meaningless input.
+Uncaught exception from user code:
+	Illegal division by zero at /usr/bin/mysqltuner line 6099.
+	main::mysql_innodb() called at /usr/bin/mysqltuner line 6843
+root:~#
-MariaDB [(none)]>
+</file>
-</code>
+===== Sources =====
+ * [[https://github.com/major/MySQLTuner-perl#readme|MySQLTuner-perl]]
-==== Liste des bases ====
-<code bash>
-MariaDB [(none)]> show databases;
-+---------------------+
-| Database            |
-+---------------------+
-| #mysql50#lost+found |
-| information_schema  |
-| mysql               |
-| performance_schema  |
-| sys                 |
-+---------------------+
-rows in set (0,001 sec)
-MariaDB [(none)]>
-</code>
-==== Liste des utilisateurs ====
-<code bash>
-MariaDB [(none)]> show databases;
-MariaDB [mysql]> use mysql;
-Database changed
-MariaDB [mysql]> select Host, User, Password from user;
-+-----------+-------------+----------+
-| Host      | User        | Password |
-+-----------+-------------+----------+
-| localhost | mariadb.sys |          |
-| localhost | root        | invalid  |
-| localhost | mysql       | invalid  |
-+-----------+-------------+----------+
-rows in set (0,002 sec)
-MariaDB [mysql]>
-</code>
-===== Configuration =====
-<code bash file=/etc/mysql/mariadb.conf.d/99-local.cnf>
-[mysqld]
-# Instead of skip-networking the default is now to listen only on
-# localhost which is more compatible and is not less secure.
-bind-address            = 10.1.40.1
-</code>
-Object, autoriser la connexion depuis les autres machines du réseau local.
-===== Suite =====
+===== Sujets connexes =====
-  * [[mysqltuner_optimisation|Optimisation de mariaDB]]
+  * [[mariadb|Installation de mariaDB]]
-  * [[mysqltuner_sécurisation|Sécurisation de mariaDB]]
+  * [[mysqltuner_sécurisation|Sécurisation de mariaDB ou mySQL]]