mysql – “mysqld” vs “server” in my.cnf

I used to see server-related configuration in my.cnf located under [mysqld].

However, I now come across configurations that use [server] instead.

It looks like both of them are synonyms, at least on MySQL 8.0, I can see that options under both groups apply to the MySQL server.

Is this a new feature of MySQL 8.0? Is one of them preferred over the other? Is one of them deprecated?

crash – MySQL says “check that your my.cnf matches the ibdata files”. How?

After trying to copy some columns of big tables (500k records) into another one, my MySQL dockers (5.7) images stoped. Trying to start it again I get the following error:

(ERROR) InnoDB: Trying to access page number 16384 in space 0, space name innodb_system, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get t
    his error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
(ERROR) InnoDB: Server exits.

It suggests checking my.cnf file. But I’m not sure what needs to be checked exactly. Can you help to start it again?

Here is the full log file

2020-07-05 21:11:06+00:00 (Note) (Entrypoint): Entrypoint script for MySQL Server 5.7.30-1debian10 started.
2020-07-05 21:11:08+00:00 (Note) (Entrypoint): Switching to dedicated user 'mysql'
2020-07-05 21:11:08+00:00 (Note) (Entrypoint): Entrypoint script for MySQL Server 5.7.30-1debian10 started.
2020-07-05T21:11:09.338116Z 0 (Warning) TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-07-05T21:11:09.347350Z 0 (Note) mysqld (mysqld 5.7.30) starting as process 1 ...
2020-07-05T21:11:09.367117Z 0 (Note) InnoDB: PUNCH HOLE support available
2020-07-05T21:11:09.367215Z 0 (Note) InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-07-05T21:11:09.367229Z 0 (Note) InnoDB: Uses event mutexes
2020-07-05T21:11:09.367233Z 0 (Note) InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-07-05T21:11:09.367237Z 0 (Note) InnoDB: Compressed tables use zlib 1.2.11
2020-07-05T21:11:09.367240Z 0 (Note) InnoDB: Using Linux native AIO
2020-07-05T21:11:09.367540Z 0 (Note) InnoDB: Number of pools: 1
2020-07-05T21:11:09.367837Z 0 (Note) InnoDB: Using CPU crc32 instructions
2020-07-05T21:11:09.370078Z 0 (Note) InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-07-05T21:11:09.383259Z 0 (Note) InnoDB: Completed initialization of buffer pool
2020-07-05T21:11:09.386104Z 0 (Note) InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-07-05T21:11:09.436151Z 0 (Note) InnoDB: Highest supported file format is Barracuda.
2020-07-05T21:11:09.458334Z 0 (Note) InnoDB: Log scan progressed past the checkpoint lsn 7146120985
2020-07-05T21:11:09.537366Z 0 (Note) InnoDB: Doing recovery: scanned up to log sequence number 7151363584
2020-07-05T21:11:09.613422Z 0 (Note) InnoDB: Doing recovery: scanned up to log sequence number 7156606464
2020-07-05T21:11:09.678950Z 0 (Note) InnoDB: Doing recovery: scanned up to log sequence number 7161849344
2020-07-05T21:11:09.743568Z 0 (Note) InnoDB: Doing recovery: scanned up to log sequence number 7167092224
2020-07-05T21:11:09.814902Z 0 (Note) InnoDB: Doing recovery: scanned up to log sequence number 7172335104
2020-07-05T21:11:09.891447Z 0 (Note) InnoDB: Doing recovery: scanned up to log sequence number 7177577984
2020-07-05T21:11:09.961055Z 0 (Note) InnoDB: Doing recovery: scanned up to log sequence number 7182820864
2020-07-05T21:11:10.037062Z 0 (Note) InnoDB: Doing recovery: scanned up to log sequence number 7188063744
2020-07-05T21:11:10.115817Z 0 (Note) InnoDB: Doing recovery: scanned up to log sequence number 7193306624
2020-07-05T21:11:10.188869Z 0 (Note) InnoDB: Doing recovery: scanned up to log sequence number 7198549504
2020-07-05T21:11:10.258947Z 0 (Note) InnoDB: Doing recovery: scanned up to log sequence number 7203792384
2020-07-05T21:11:10.320226Z 0 (Note) InnoDB: Doing recovery: scanned up to log sequence number 7209035264
2020-07-05T21:11:10.394447Z 0 (Note) InnoDB: Doing recovery: scanned up to log sequence number 7214278144
2020-07-05T21:11:10.468725Z 0 (Note) InnoDB: Doing recovery: scanned up to log sequence number 7219521024
2020-07-05T21:11:10.501674Z 0 (Note) InnoDB: Doing recovery: scanned up to log sequence number 7221701778
2020-07-05T21:11:10.502002Z 0 (Note) InnoDB: Database was not shutdown normally!
2020-07-05T21:11:10.502062Z 0 (Note) InnoDB: Starting crash recovery.
2020-07-05T21:11:11.051988Z 0 (Note) InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 469959 row operations to undo
2020-07-05T21:11:11.052016Z 0 (Note) InnoDB: Trx id counter is 4498432
2020-07-05T21:11:11.052024Z 0 (Note) InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 6
    4 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
2020-07-05T21:11:12.544201Z 0 (Note) InnoDB: Apply batch completed
2020-07-05T21:11:13.012256Z 0 (Note) InnoDB: Starting in background the rollback of uncommitted transactions
2020-07-05T21:11:13.012289Z 0 (Note) InnoDB: Rolling back trx with id 4497948, 469959 rows to undo
    vajje2-mysql |
InnoDB: Progress in percents: 12020-07-05T21:11:13.013622Z 0 (Note) InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-07-05T21:11:13.013643Z 0 (Note) InnoDB: Creating shared tablespace for temporary tables
2020-07-05T21:11:13.018222Z 0 (Note) InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-07-05T21:11:13.060735Z 0 (ERROR) InnoDB: Trying to access page number 16384 in space 0, space name innodb_system, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get t
    his error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2020-07-05T21:11:13.060762Z 0 (ERROR) InnoDB: Server exits.

Trying to start MySQL using the native Windows version instead of Docker, I get a different error message

mysqld --console
2020-07-05T21:18:23.399589Z 0 (Warning) TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-07-05T21:18:23.399589Z 0 (Warning) 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2020-07-05T21:18:23.399589Z 0 (Warning) 'NO_AUTO_CREATE_USER' sql mode was not set.
2020-07-05T21:18:23.399589Z 0 (Warning) Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2020-07-05T21:18:23.400089Z 0 (Note) mysqld (mysqld 5.7.12) starting as process 16596 ...
2020-07-05T21:18:23.406089Z 0 (Note) InnoDB: Mutexes and rw_locks use Windows interlocked functions
2020-07-05T21:18:23.406589Z 0 (Note) InnoDB: Uses event mutexes
2020-07-05T21:18:23.406589Z 0 (Note) InnoDB: _mm_lfence() and _mm_sfence() are used for memory barrier
2020-07-05T21:18:23.406589Z 0 (Note) InnoDB: Compressed tables use zlib 1.2.3
2020-07-05T21:18:23.407589Z 0 (Note) InnoDB: Number of pools: 1
2020-07-05T21:18:23.407589Z 0 (Note) InnoDB: Not using CPU crc32 instructions
2020-07-05T21:18:23.460113Z 0 (Note) InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-07-05T21:18:23.466088Z 0 (Note) InnoDB: Completed initialization of buffer pool
2020-07-05T21:18:23.495090Z 0 (Note) InnoDB: Highest supported file format is Barracuda.
2020-07-05T21:18:23.498091Z 0 (Note) InnoDB: Log scan progressed past the checkpoint lsn 7146120985
2020-07-05T21:18:23.506129Z 0 (ERROR) InnoDB: ############### CORRUPT LOG RECORD FOUND ##################
2020-07-05T21:18:23.506625Z 0 (Note) InnoDB: Log record type 55, page 6632:0. Log parsing proceeded successfully up to 7147076749. Previous log record type 38, is multi 0 Recv offset 355267, prev 354841
2020-07-05T21:18:23.507094Z 0 (Note) InnoDB: Hex dump starting 100 bytes before and ending 100 bytes after the corrupted record:
 len 626; hex 150004000219000c2f0069706169726162010016000c07000ec9a2c3a6767265c992cb9072646e16d8bad98e20d8b120d8a7d98f20d8afd98fd98620d98622d8bad988d8b1e2808cd8a7d8b1d8afd986229400832d000d0bc5d449980906000000107dd2a699e8c046200006000180068006800700047fffffff254d831b00090018558100000080d8c2000000107dd200000044a21cb10000032d313a8005d44a00060054012e0003003100040035000400390007004000080048000d00005500077c000c80000297000232010250015f696466696c65776f72646d65616e696e676d6f727068656d6570726f6e756e63696174696f6e010027000b0003000c0e00246964183531643763616636663763323535373436353033393039339d66020016d8bad988d8a2d8afd8a7d984daa9db8cd988db8cd8b101009b00000700020094001200030015000700001c000c6a00706f736d65616e696e6702004e0012000300150004000019000c40005f696464657363010027000b0003000c0e00246964183530643263663334663763323535336331363030303030380dd8a7d8b3d98520d8aed8a7d8b529d8aad984d981d8b820d8aad8b1daa9db8c20daafd988d8a2d8afd8a7d984daa9db8cd988db8cd8b12001001e000c070016d8bad988d8a2d8afd8a7d984daa9db8cd988db8cd8b10000040022d8bad988d8a2d8afd8a7d984daa9db8cd988db8cd8b1223799e80000122e2f6d61707065722f776f72642e696264003799a400001f2e2f6d7973716c2f696e6e6f64625f7461626c655f73746174732e696264003799a300001f2e2f6d7973716c2f696e6e6f64625f696e6465785f73746174732e696264001f38; asc         / ipairab            vre    rdn                       "                 "   -     I       }     F                 %M      U           }    D      -1:   J   T .   1   5   9   @   H    U  |        2  P _idfilewordmeaningmorphemepronunciation  '        $id 51d7caf6f7c2557465039093 f                                                j posmeaning  N             @ _iddesc  '        $id 50d2cf34f7c2553c16000008              )                                                                           "                      "7     ./mapper/word.ibd 7     ./mysql/innodb_table_stats.ibd 7     ./mysql/innodb_index_stats.ibd  8;
2020-07-05T21:18:23.589588Z 0 (Warning) InnoDB: The log file may have been corrupt and it is possible that the log scan did not proceed far enough in recovery! Please run CHECK TABLE on your InnoDB tables to check that they are ok! If mysqld crashes after this recovery; Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2020-07-05T21:18:23.590089Z 0 (ERROR) InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT between the checkpoint 7146120985 and the end 7147103744.
2020-07-05T21:18:23.590089Z 0 (ERROR) InnoDB: Plugin initialization aborted with error Generic error
2020-07-05T21:18:23.791880Z 0 (ERROR) Plugin 'InnoDB' init function returned error.
2020-07-05T21:18:23.792342Z 0 (ERROR) Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-07-05T21:18:23.792840Z 0 (ERROR) Failed to initialize plugins.
2020-07-05T21:18:23.792840Z 0 (ERROR) Aborting

2020-07-05T21:18:23.793339Z 0 (Note) Binlog end
2020-07-05T21:18:23.793339Z 0 (Note) Shutting down plugin 'CSV'
2020-07-05T21:18:23.794344Z 0 (Note) mysqld: Shutdown complete

optimize my.cnf

Hi guys

I have a cpanel server with many websites. mainly in wordpress
the server is double xeon E5-2430 + ram 64 GB + hard drive 1 TB

my s … | Read the rest of https://www.webhostingtalk.com/showthread.php?t=1807770&goto=newpost

Specifying the slave database in my.cnf binlog_do_db causes MySQL to shutdown

I'm following fairly simple instructions on how to configure master / slave replication using this article from Digital Ocean.

On the slave side, I imported a database called "xyz". The next steps are:

sudo nano /etc/mysql/my.cnf

server-id = 2

relay-log = /var/log/mysql/mysql-relay-bin.log

log_bin = /var/log/mysql/mysql-bin.log

binlog_do_db = xyz

And then restart MySQL.

However, when I restart MySQL, I get this error:

Jan 17 06:31:29 hostname systemd[1]: mysql.service: Control process exited, code=exited status=1
Jan 17 06:31:29 hostname systemd[1]: mysql.service: Failed with result 'exit-code'.
Jan 17 06:31:29 hostname systemd[1]: Failed to start MySQL Community Server.
Jan 17 06:31:29 hostname systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Jan 17 06:31:29 hostname systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
Jan 17 06:31:29 hostname systemd[1]: Stopped MySQL Community Server.
Jan 17 06:31:29 hostname systemd[1]: mysql.service: Start request repeated too quickly.
Jan 17 06:31:29 hostname systemd[1]: mysql.service: Failed with result 'exit-code'.
Jan 17 06:31:29 hostname systemd[1]: Failed to start MySQL Community Server.

I isolated it from the line binlog_do_db. When I uncomment this line, MySQL can start again.

I honestly don't know where to start. On the master specifying the same database binlog_do_db was no problem.

MySql my.cnf recommended settings mariadb 10.2

I am a novice server manager and I have a problem when mysql 5.7 to mariadb 10.2. High performance 191%. Can any one help the optimze values ​​recommended in my.cnf for a database server with the following specifications:

> Architecture: x86_64
>
> Operating mode (s) of the CPU: 32 bits, 64 bits
>
> Byte order: Little Endian
>
> CPU (s): 2
>
> List of online CPUs: 0.1
>
> Thread (s) per heart: 1
>
> Core per socket: 1
>
> Plug (s): 2
>
> Node (s) NUMA: 1
>
> Vendor ID: GenuineIntel
>
> Processor family: 6
>
> Model: 79
>
Model Name: Intel (R) Xeon (R) CPU E5-2630 v4 @ 2.20GHz
>
> Step by step: 1
>
> MHz of the processor: 2199.998
>
> BogoMIPS: 4399.99
>
> Virtualization: VT-x
>
> Hypervisor provider: KVM
>
> Type of virtualization: complete
>
> L1d cache: 32K
>
> L1i cache: 32K
>
> N2 cache memory: 4096 KB
>
> L3 cache: 16384K
>
> NUMA node0 CPU (s): 0.1

and mysql tunner

> >> MySQLTuner 1.7.14 - Major Hayden 
>
> >> Bug reports, feature requests and downloads at http://mysqltuner.com/
>
> >> Run with - - help & # 39; for additional options and output filtering
>
> [--] Ignored version check for the MySQLTuner script
>
> [OK] Currently, support MySQL version 10.2.21-MariaDB
>
> [OK] Running on a 64-bit architecture
>
> -------- Recommendations for log files ------------------------------------ - --------
> -------------------- [--] Log file: /var/lib/mysql/mvpiot.novalocal.err(513K)
>
> [OK] The log file /var/lib/mysql/mvpiot.novalocal.err exists
>
> [OK] The log file /var/lib/mysql/mvpiot.novalocal.err is readable.
>
> [OK] The log file /var/lib/mysql/mvpiot.novalocal.err is not empty
>
> [OK] The log file /var/lib/mysql/mvpiot.novalocal.err is less than 32
> Mo
>
> [!!] /var/lib/mysql/mvpiot.novalocal.err has 1146 alert (s).
>
> [!!] /var/lib/mysql/mvpiot.novalocal.err has 2355 error (s).
>
> [--] 37 start (s) found in /var/lib/mysql/mvpiot.novalocal.err
>
>
> [--] 1) 2019-01-12 8:25:46 140431271381184 [Note] / usr / sbin / mysqld:
> ready for connections.
>
> [--] 2) 2019-01-12 7:29:03 140574858127552 [Note] / usr / sbin / mysqld:
> ready for connections.
>
> [--] 3) 2019-01-11 18:39:26 140560611846336 [Note] / usr / sbin / mysqld:
> ready for connections. [--] 4) 2019-01-11 17:00:17 140623437183168
> [Note] / usr / sbin / mysqld: ready for connections. [--] 5) 2019-01-11
> 16:30:08 140674500737216 [Note] / usr / sbin / mysqld: ready for
> connections. [--] 6) 2019-01-11 14:55:17 140065307875520 [Note]
> / usr / sbin / mysqld: ready for connections. [--] 7) 2019-01-11 13:34:41
> 140588138420416 [Note] / usr / sbin / mysqld: ready for connections. [--]
> 8) 2019-01-11 13:01:12 139932833945792 [Note] / usr / sbin / mysqld: ready
> for connections. [--] 9) 2019-01-11 12:27:23 140694443542720 [Note]
> / usr / sbin / mysqld: ready for connections. [--] 10) 2019-01-11 12:08:55
> 139655472789696 [Note] / usr / sbin / mysqld: ready for connections. [--] 9
> stop (s) detected in /var/lib/mysql/mvpiot.novalocal.err [--] 1)
> 2019-01-10 18:03:26 139706578528000 [Note] / usr / sbin / mysqld: Stop
> complete [--] 2) 2019-01-10 17:48:26 140143107737344 [Note]
> / usr / sbin / mysqld: complete stop [--] 3) 2019-01-10 17:45:56
> 140094187407104 [Note] / usr / sbin / mysqld: full stop [--] 4)
> 2019-01-10 17:42:51 139985407059712 [Note] / usr / sbin / mysqld: Stop
> complete [--] 5) 2019-01-10 17:26:12 140542010558208 [Note]
> / usr / sbin / mysqld: complete stop [--] 6) 2019-01-10 17:26:09
> 140119564142336 [Note] / usr / sbin / mysqld: full stop [--] 7)
> 2019-01-10 13:42:59 140154423613184 [Note] / usr / sbin / mysqld: Stop
> complete [--] 8) 2019-01-10 9:21:20 140596763801344 [Note]
> / usr / sbin / mysqld: complete stop [--] 9) 2019-01-10 9:21:18
> 139833937012480 [Note] / usr / sbin / mysqld: full stop
>
>
> -------- Storage Engine Statistics ------------------------------------ - ---------------------------
>
> [--] Status: + Aria + CSV + InnoDB + MEMORY + MRG_MISAM + MyISAM
> + PERFORMANCE_SCHEMA + SEQUENCE
>
> [--] Data in Aria tables: 192.0K (Tables: 6)
>
> [--] Data in MyISAM tables: 15.5M (Tables: 85)
>
> [--] Data in InnoDB tables: 140.7 M (Tables: 87)
>
> [OK] Total number of fragmented tables: 0
>
>
> -------- Analysis Performance Metrics --------------------------------- ----- ------------------------
>
> [--] innodb_stats_on_metadata: OFF
>
> [OK] No statistics are updated during the interrogation of INFORMATION_SCHEMA.
>
>
> -------- Safety recommendations -------------------------------------- - ---------------------------
>
> [OK] There are no anonymous accounts for database users
>
> [OK] All database users have passwords assigned
>
> [!!] There is no list of basic password files!
>
>
> -------- CVE Security Recommendations ------------------------------------- - ------------------------
>
> [--] Ignored because of the option --cvefile undefined
>
>
> -------- performance metrics -------------------------------------- - --------------------------------
>
> [--] Up for: 1h 26m 8s (432K q [83.607 qps], 20K conn, TX: 710M, RX:
> 32M)
>
> [--] Lit / written: 97% / 3%
>
> [--] Binary logging is disabled
>
> [--] Physical Memory: 3.9G
>
> [--] Max MySQL memory: 2.9G
>
> [--] Other process memory: 0B
>
> [--] Total buffers: Global 672.0 M + 4.5 M per thread (500 threads max)
>
> [--] P_S Max memory usage: 0B
>
> [--] Galera GCache Max Memory Usage: 0B
>
> [OK] Maximum utilization of the memory reached: 1016.7 M (25.74% of the installed RAM)
>
> [OK] Maximum possible use of memory: 2.9 G (74.41% of installed RAM)
>
> [OK] The possible overall use of memory with another process is compatible
> with available memory
>
> [OK] Slow queries: 0% (0 / 432K)
>
> [OK] Highest usage of available connections: 15% (76/500)
>
> [OK] Interrupted connections: 0.39% (82/20849)
>
> [!!] name resolution is active: an inverse name resolution is made for
> each new connection and can reduce performance
>
> [!!] The query cache can be disabled by default due to a mutex conflict.
>
> [OK] Query cache efficiency: 39.5% (237 KB cache / 602 KB to select)
>
> [OK] Ask the prunes per day: 0
>
> [OK] Tris requiring temporary tables: 0% (0 temporary sort / 129 trios)
>
> [!!] Joins made without index: 23
>
> [OK] Temporary tables created on the disk: 21% (296 on the disk / 1K in total)
>
> [OK] Thread Cache Success Rate: 99% (76 connections created / 20K)
>
> [OK] Table cache access rate: 94% (319 open / 339 open)
>
> [OK] Open file limit used: 1% (188 / 10K)
>
> [OK] Table locks acquired immediately: 100% (immediate 7K locks / 7K)
>
>
> -------- Performance Diagram -------------------------------------- - ---------------------------------
>
> [--] The performance scheme is disabled.
>
> [--] Memory used by P_S: 0B
>
> [--] The Sys schema is installed.
>
>
> -------- Metrics ThreadPool --------------------------------------- ---------------------------------
>
> [--] The ThreadPool statistic is enabled.
>
> [--] Size of the thread pool: 2 thread (s).
>
> [--] Using the default is sufficient for your version
> (10.2.21-MariaDB)
>
>
> -------- MyISAM Metrics --------------------------------------- -------------------------------------
>
> [!!] Key buffer used: 18.4% (24 MB used / 134MB cache)
>
> [OK] Total MyISAM buffer / index size: 128.0 M / 9.4 M
>
> [OK] Read buffer access rate: 99.4% (748 KB in cache / 4 KB in read)
>
> [!!] Write Buffer Success Rate: 1.3% (255 KB in cache / 3 KB in write)
>
>
> -------- InnoDB Metrics --------------------------------------- -------------------------------------
>
> [--] InnoDB is activated.
>
> [--] InnoDB thread competition: 0
>
> [OK] The InnoDB file per table is enabled
>
> [!!] InnoDB Buffer / Data Pool Size: 128.0M / 140.7M
>
> [!!] Ratio InnoDB Log File Size / InnoDB Buffer Pool Size (75%):
> 48.0 M * 2 / 128.0 M should be equal to 25%
>
> [OK] InnoDB Buffer Pool Instances: 1
>
> [--] Number of InnoDB buffer pool blocks: 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 reading buffer efficiency: 100.00% (532580940 hits / 532588160
> total)
>
> [!!] Efficiency of the InnoDB Writing Log: 51.86% (13396 hits / 25832 in total)
>
> [OK] The InnoDB newspaper waits: 0.00% (0 wait / 12436 writes)
>
>
> -------- AriaDB Metrics --------------------------------------- -------------------------------------
>
> [--] AriaDB is enabled.
>
> [OK] Aria correspondence page size / total Aria index: 128.0 MB / 96.0 KB
>
> [!!] Success rate of the Aria correspondence page: 79.6% (1K cached / 302 reads)
>
>
> -------- TokuDB Metrics --------------------------------------- -------------------------------------
>
> [--] TokuDB is disabled.
>
>
> -------- XtraDB Metrics --------------------------------------- -------------------------------------
>
> [--] XtraDB is disabled.
>
>
> -------- Metric Galera --------------------------------------- -------------------------------------
>
> [--] Galera is disabled.
>
>
> -------- Replication Metrics -------------------------------------- - --------------------------------
>
> [--] Replication Galera Synchronous: NO
>
> [--] No slave replication for this server.
>
> [--] Binlog format: MIXED
>
> [--] XA Support Enabled: ON
>
> [--] Primary semisynchronous replication: not enabled
>
> [--] Semi-synchronous replication slave: not enabled
>
> [--] This is a stand-alone server
>
>
> -------- Recommendations ---------------------------------------- ---- -----------------------------------
>
> General recommendations:
>
> Control the warning lines in the file /var/lib/mysql/mvpiot.novalocal.err
>
> Control of the error lines in the file /var/lib/mysql/mvpiot.novalocal.err
>
> MySQL was started in the last 24 hours - recommendations may be inaccurate
>
> Configure your accounts with ip or subnets only, then update your configuration with skip-name-resol = 1
>
> Adjust your join queries to always use indexes
>
> The performance scheme must be activated for a better diagnosis
>
> Before modifying 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)
>
> join_buffer_size (> 1.0M, or always use indexes with JOIN)
>
> performance_schema = ON activates PFS
>
> innodb_buffer_pool_size (> = 140.7 M) if possible.
>
> innodb_log_file_size should be (= 16 MB) if possible, so that the total size of the InnoDB log files is 25% of the buffer pool size.

If everyone optimizes, my.cnf can give me. Thank you so much.

configuration – My.cnf with cpu: 30 and 64 GB of RAM

my server with

                                processor: processor 30 / Intel (R) Xeon (R) processor E5-2690 v2 at 3.00 GHz
RAM: 61824916 kB

Server: Localhost via UNIX socket
mysql: 5.1
apache
Server type: MySQL
Server Version: 5.1.73 - Source
Apache / 2.2.15 (CentOS)

my database information:
engine | TABLES | rows | DATA | idx | total_size | idxfrac |
-> + -------- + -------- + -------- + ------- + ------- + ---- -------- + --------- +
-> | MyISAM | 41 | 22.08M | 3.79 G | 2.85G | 6.63G | 0,75 |
-> | InnoDB | 8 | 11,54M | 2,77 G | 1.84G | 4.61G | 0.66

my.cnf file:
# CUSTOMER #
port = 3306
socket =
/var/lib/mysql/mysql.sock
   [mysqld]
  #General #
user = mysql
default-storage-engine = MYISAM
socket = /var/lib/mysql/mysql.sock
pid-file = / var / lib / mysql /
# MyISAM #
key buffer size = 4G
myisam-recover = FORCE, BACKUP
# SECURITY #
max-allowed-packet = 32M
max-connect-errors = 1000000
skip-name-resol
innodb = FORCE
# DATA STORAGE #
datadir = / var / lib / mysql /
# CACHES AND LIMITS #
tmp-table-size = 512M
max-heap-table-size = 64M
query-cache-type = 1
query_cache_size = 512M
query_cache_limit = 20M
long_query_time = 5 # 0
join_buffer_size = 128M
sort_buffer_size = 16M
read_buffer_size = 128M
max-connections = 400
thread-cache-size = 100
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 4096
external jump-lock
myisam_sort_buffer_size = 256M
wait_timeout = 7200
interactive_timeout = 7200
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 2047M
innodb-flush-log-at-trx-commit = 0
innodb-file-per-table = 1
innodb-buffer-pool-size = 40G
innodb_log_buffer_size = 16M # 20G
innodb_file_per_table = 1
innodb_open_files = 3000

When the number of online users increases, the speed of the site decreases sharply. Thanks for helping my.cnf confige file

Optimal my.cnf | Talk Web Hosting

We have just moved to a new server with Cpanel & Cloudlinux, hosting about 80 WordPress websites.

The server configuration is:
Xeon E3-1230 v6
32 GB of RAM
2×1 TB SSD HW Raid 1

Our current my.cnf file looks like this:

Code:

[client-server]



! includedir /etc/my.cnf.d

[mysqld]
query_cache_size = 1G
join_buffer_size = 6M
key_buffer = 768M
innodb_buffer_pool_size = 4G
read_buffer_size = 4M
connect_timeout = 30
innodb_read_io_threads = 6
innodb_write_io_threads = 6
myisam_sort_buffer_size = 8M
innodb_file_per_table = 1
innodb_thread_concurrency = 6
table_cache = 75536
table_definition_cache = 8192
thread_cache = 128
query_cache_limit = 18M
open_files_limit = 12000
max_user_connections = 35
max_allowed_packet = 268435456
tmp_table_size = 3000M
thread_concurrency = 8
sort_buffer_size = 2M
wait_timeout = 7200
max_connections = 250
read_rnd_buffer_size = 512K
max_heap_table_size = 4000M
expire_logs_days = 7
competitor_insert = 2
local-infile = 0
default-storage-engine = MyISAM
[isamchk]
read_buffer = 16M
write_buffer = 16M
key_buffer = 64M
sort_buffer = 64M
[myisamchk]
read_buffer = 16M
write_buffer = 16M
key_buffer = 64M
sort_buffer = 64M

Use of the ram:

Code:

free -mh
Total used free buff / shared cache available
Mem: 31G 4.4G 1.4G 161M 25G 23G
Exchange: 0B 0B 0B

The charge is between 0.8 and 1.00.

Please tell me if it is a good configuration or if we should change it further.
We also use Engintron and Unixy Redis. Do you suggest another software that would speed up / optimize things?

Thank you.

MYSQLTUNER and MY.CNF and SERVER DETAILS – Please suggest what I can do.

I have no idea what needs to be in the my.cnf file. Can someone take a look at this and suggest what I should edit my.cnf? I'm hosting a Crea8social site. We thank you in advance for your expertise.

HERE ARE THE INFORMATION ABOUT THE SERVER

Code:

Processor information
Total number of processors: 8
Processor # 1
Seller
VéritableIntel
first name
3.50 GHz E3-1270 V2 CPU for Intel (R) Xeon (R)
Speed
1600.250 MHz
Hidden
8192 KB
Processor # 2
Seller
VéritableIntel
first name
3.50 GHz E3-1270 V2 CPU for Intel (R) Xeon (R)
Speed
1683.349 MHz
Hidden
8192 KB
Processor # 3
Seller
VéritableIntel
first name
3.50 GHz E3-1270 V2 CPU for Intel (R) Xeon (R)
Speed
1613.922 MHz
Hidden
8192 KB
Processor # 4
Seller
VéritableIntel
first name
3.50 GHz E3-1270 V2 CPU for Intel (R) Xeon (R)
Speed
1607.727 MHz
Hidden
8192 KB
Processor # 5
Seller
VéritableIntel
first name
3.50 GHz E3-1270 V2 CPU for Intel (R) Xeon (R)
Speed
1999.938 MHz
Hidden
8192 KB
Processor # 6
Seller
VéritableIntel
first name
3.50 GHz E3-1270 V2 CPU for Intel (R) Xeon (R)
Speed
1601.959 MHz
Hidden
8192 KB
Processor # 7
Seller
VéritableIntel
first name
3.50 GHz E3-1270 V2 CPU for Intel (R) Xeon (R)
Speed
1914.489 MHz
Hidden
8192 KB
Processor # 8
Seller
VéritableIntel
first name
3.50 GHz E3-1270 V2 CPU for Intel (R) Xeon (R)
Speed
1608.581 MHz
Hidden
8192 KB

Memory information
[    0.000000] Memory: 5227304k / 34340864k available (7664k kernel code, 814612k missing, 794872k reserved, 6055k data, init 1876)

System information
Linux server1.anymood.org 3.10.0-957.1.3.el7.x86_64 # 1 SMP Thu 29 Nov 14:49:43 UTC 2018 x86_64 x86_64 x86_64 GNU / Linux

Physical disks
[    1.852306] sd 1: 0: 0: 0: [sdb] 976773168 512-byte logical blocks: (500 GB / 465 GB)
[    1.852360] sd 1: 0: 0: 0: [sdb] Write Protect is off
[    1.852361] sd 1: 0: 0: 0: [sdb] Direction of the mode: 00 3a 00 00
[    1.852379] sd 1: 0: 0: 0: [sdb] Write Cache: Enabled, Read Cache: Enabled, Does Not Support DPO or FUA
[    1.852414] sd 0: 2: 0: 0: [sda] 7812939776 512-byte logical blocks: (4.00 TB / 3.63 TB)
[    1.852542] sd 0: 2: 0: 0: [sda] Write Protect is off
[    1.852544] sd 0: 2: 0: 0: [sda] Direction of the mode: 1f 00 00 08
[    1.852617] sd 0: 2: 0: 0: [sda] Write Cache: Enabled, Read Cache: Enabled, Does Not Support DPO or FUA
[    1.853274]  sdb: sdb1 sdb2 sdb3
[    1.853547] sd 1: 0: 0: 0: [sdb] Attached SCSI disk
[    1.853755] sd 0: 2: 0: 0: [sda] Attached SCSI disk
[    5.491483] sd 0: 2: 0: 0: attached scsi generic sg1 type 0
[    5.491529] sd 1: 0: 0: 0: attached scsi generic sg2 type 0


Current use of memory
Total used free buff / shared cache available
Mem: 32752400 3456172 27632256 20640 1663972 28889364
Swap: 2097148 0 2097148
Total: 34849548 3456172 29729404

Current use of the disc
Size of file system used Usage used% Mounted
/ dev / sdb2 456G 12G 421G 3% /
devtmpfs 16G 0 16G 0% / dev
tmpfs 16G 0 16G 0% / dev / shm
tmpfs 16G 9.0M 16G 1% / run
tmpfs 16G 0 16G 0% / sys / fs / cgroup
/ dev / sdb1 976M 145M 765M 16% / boot
/ dev / loop0 3.9G 8.4M 3.7G 1% / tmp
tmpfs 3.2G 0 3.2G 0% / run / user / 0

MYSQLTUNER EXIT

Code:

[root@server1 ~]# curl -L http://mysqltuner.pl/ | perl
% Total% Received% Xferd Average Speed ​​Time Time Time Current
Downloading total unloading of used left speed
0 0 0 0 0 0 0 0 -: -: - - :: -: - -: -: - 0
0 0 0 0 0 0 0 0 -: -: - 0:00:01 -: -: - 0
100 216k 100 216k 0 0 111k 0 0:00:01 0:00:01 -: -: - 111k
>> MySQLTuner 1.7.14 - Major Hayden 
 >> Bug reports, feature requests and downloads on http://mysqltuner.com/
>> Run with - - help & # 39; for additional options and output filtering

[--] Ignored version check for the MySQLTuner script
[OK] Currently, support MySQL version 10.2.19-MariaDB
[OK] Running on a 64-bit architecture

-------- Recommendations for log files ------------------------------------- ------ ---------------------------
[--] Log file: /var/lib/mysql/server1.anymood.org.err(24K)
[OK] The log file /var/lib/mysql/server1.anymood.org.err exists
[OK] The log file /var/lib/mysql/server1.anymood.org.err is readable.
[OK] The log file /var/lib/mysql/server1.anymood.org.err is not empty
[OK] The log file /var/lib/mysql/server1.anymood.org.err is less than 32 MB
[!!] /var/lib/mysql/server1.anymood.org.err has 10 warning (s).
[!!] /var/lib/mysql/server1.anymood.org.err has 6 errors.
[--] 5 start (s) found in /var/lib/mysql/server1.anymood.org.err
[--] 1) 2018-12-06 22:37:13 140333955983552 [Note] / usr / sbin / mysqld: ready for connections.
[--] 2) 2018-12-06 20:24:22 140083038988480 [Note] / usr / sbin / mysqld: ready for connections.
[--] 3) 2018-12-06 20:01:02 140436577986752 [Note] / usr / sbin / mysqld: ready for connections.
[--] 4) 2018-12-06 20:00:59 140379482245312 [Note] / usr / sbin / mysqld: ready for connections.
[--] 5) 2018-12-06 20:00:50 140368649152704 [Note] / usr / sbin / mysqld: ready for connections.
[--] 5 stop (s) detected in /var/lib/mysql/server1.anymood.org.err
[--] 1) 2018-12-07 0:02:52 140333495113472 [Note] / usr / sbin / mysqld: full stop
[--] 2) 2018-12-06 22:33:36 140082598475520 [Note] / usr / sbin / mysqld: full stop
[--] 3) 2018-12-06 20:24:22 140436442830592 [Note] / usr / sbin / mysqld: full stop
[--] 4) 2018-12-06 20:01:01 140379347396352 [Note] / usr / sbin / mysqld: full stop
[--] 5) 2018-12-06 20:00:59 140368208774912 [Note] / usr / sbin / mysqld: full stop

-------- Storage Engine Statistics ------------------------------------- - --------------------------
[--] Status: + Aria + CSV + InnoDB + MEMORY + MRG_MyISAM + MyISAM + PERFORMANCE_SCHEMA + SEQUENCE
[--] Data in InnoDB tables: 18.7 million euros (tables: 100)
[OK] Total number of fragmented tables: 0

-------- Analysis performance metrics ---------------------------------- ----- -----------------------
[--] innodb_stats_on_metadata: OFF
[OK] No statistics are updated during the query on INFORMATION_SCHEMA.

-------- Safety recommendations --------------------------------------- ----- --------------------------
[OK] There are no anonymous accounts for database users
[OK] All database users have passwords assigned
[!!] There is no list of basic password files!

-------- CVE Security Recommendations -------------------------------------- - -----------------------
[--] Ignored because of the option --cvefile undefined

-------- Performance indicators --------------------------------------- - -------------------------------
[--] Up to: 15m 7s (16K q [17.731 qps], 795 conn, TX: 30M, RX: 2M)
[--] Read / write: 88% / 12%
[--] Binary logging is disabled
[--] Physical Memory: 31.2g
[--] Max MySQL memory: 39.8G
[--] Other process memory: 0B
[--] Total buffers: 20.3 G globals + 132.4 Mio per thread (151 threads max)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max Memory Usage: 0B
[OK] Maximum utilization of the memory reached: 20.7 G (66.18% of the installed RAM)
[!!] Maximum possible use of memory: 39.8 G (127.44% of installed RAM)
[!!] The possible global use of memory with another process exceeded the memory
[OK] Slow queries: 0% (0 / 16K)
[OK] Highest usage of available connections: 1% (3/151)
[OK] Abandoned connections: 0.00% (0/795)
[!!] name resolution is active: an inverse name resolution is performed for each new connection and may reduce performance
[!!] The query cache can be disabled by default due to a mutex conflict.
[!!] Query cache efficiency: 0.0% (0 cached / 12,000 selections)
[OK] Ask the prunes per day: 0
[OK] Tris requiring temporary tables: 0% (0 temporary sorting / 1K sorting)
[!!] Joins made without index: 250
[OK] Temporary tables created on the disc: 23% (275 on the disk / 1K in total)
[OK] Thread Cache Success Rate: 99% (3 Created / 795 Connections)
[OK] Table cache access rate: 91% (66 open / 72 open)
[OK] Open file limit used: 0% (23 / 10K)
[OK] Table latches acquired immediately: 100% (17 immediate / 17 latches)

-------- Performance Diagram --------------------------------------- ----- --------------------------------
[--] The performance scheme is disabled.
[--] Memory used by P_S: 0B
[--] The Sys schema is installed.

-------- ThreadPool Metrics ---------------------------------------- ---- ----------------------------
[--] The ThreadPool statistic is enabled.
[--] Size of the thread pool: 8 threads.
[--] Using the default is sufficient for your version (10.2.19-MariaDB)

-------- MyISAM Metrics ---------------------------------------- ---- ------------------------------------
[!!] Key buffer used: 18.2% (24 MB used / 134MB cache)
[OK] Total MyISAM buffer / index size: 128.0 M / 64.0 K
[!!] Read Buffer Success Rate: 75.0% (24 Cached / 6 Reads)

-------- InnoDB metrics ---------------------------------------- ---- ------------------------------------
[--] InnoDB is activated.
[--] InnoDB thread competition: 0
[OK] The InnoDB file per table is enabled
[OK] InnoDB Buffer / Data Pool Size: 20.0G / 18.7M
[!!] Ratio InnoDB Log File Size / InnoDB Buffer Pool Size (0.46875%): 48.0 M * 2 / 20.0 G should be 25%
[!!] InnoDB Buffer Pool Instances: 8
[--] Number of InnoDB buffer pool chips: 160 for 8 buffer pool instance (s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB reading buffer efficiency: 99.98% (8432336 hits / 8434139 total)
[!!] Efficiency of InnoDB Writing Log: 20.31% (199 hits / 980 total)
[OK] InnoDB Journal waits: 0.00% (0 waiting / 1179 writing)

-------- AriaDB Metrics ---------------------------------------- ---- ------------------------------------
[--] AriaDB is enabled.
[OK] Aria page size / Aria index total: 128.0 M / 1B
[!!] Aria pagecache success rate: 89.4% (2K cached / 275 reads)

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

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

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

-------- Replication Metrics --------------------------------------- ----- -------------------------------
[--] Replication Galera Synchronous: NO
[--] No slave replication for this server.
[--] Binlog format: MIXED
[--] XA Support Enabled: ON
[--] Primary semisynchronous replication: not enabled
[--] Semi-synchronous replication slave: not enabled
[--] This is a stand-alone server

-------- Recommendations ----------------------------------------- ---- ----------------------------------
General recommendations:
Check warning lines in /var/lib/mysql/server1.anymood.org.err file
Check the error lines in the file /var/lib/mysql/server1.anymood.org.err
MySQL has been started in the last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for optimal performance.
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resol = 1
Adjust your join queries to always use indexes
The performance scheme must be enabled for better diagnostics
Before you change innodb_log_file_size and / or innodb_log_files_in_group, read this: https://bit.ly/2TcGgtU
Variables to adjust:
*** The maximum use of memory by MySQL is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (= 0)
query_cache_type (= 0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 128.0M, or always use indexes with JOIN)
performance_schema = ON enable PFS
innodb_log_file_size should be (= 2G) if possible, so that the total size of the InnoDB log files is 25% of the size of the buffer pool.
innodb_buffer_pool_instances (= 20)

EXISTING MY.CNF

Code:

[mysqld]



local-infile = 0
performance scheme = 0
innodb_buffer_pool_size = 20G
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
socket = / var / lib / mysql / mysql.sock
symbolic-links = 0
default-storage-engine = myisam
innodb_file_per_table = 1
max_allowed_packet = 1G
open_files_limit = 10000