performance – Why is the execution time on Datagrip longer on MySQL 8 than MySQL 5.7 on a new Linux Server?

I am migrating from a 4 year old setup to a brand new high performance server and am experiencing slower performance than on the old machine.

The old setup is a Ubuntu 16.04 Server on bare metal Intel i7 64G RAM and MySQL 5.7 and is under medium load with many services running.

The new setup is an AMD Ryzen™ 9 5950X with 128G RAM, no load and only MySQL 8 running on Ubuntu 20.04. Both are bare metal machines and I am the only one who can access it.

Now the database I am testing against holds a table with 100M rows. The new one holds an exact copy, imported after a mysqldump. I am running the exact same simple query without any join, just from one table.

The old server returns the result in about 120ms the new one returns in about 200ms.

10 rows retrieved starting from 1 in 192 ms (execution: 184 ms,
fetching: 8 ms)

I am using DataGrip for the query (connected via SSH). If I connect via SSH terminal directly and use MySQL on the console the result is returned in 0.00s

How is that even possible? I did run a few tests via mysqltuner, but found nothing that might be of help.

Here is part of the the my.cnf from the new server:

(mysqld)
default_authentication_plugin= mysql_native_password
innodb_buffer_pool_size = 100G
innodb_buffer_pool_instances = 64
innodb_buffer_pool_chunk_size = 134217728
innodb_log_file_size = 12G    
collation_server        = utf8_unicode_ci
character_set_server    = utf8

This is mysqltuner output:

mysqltuner
 >>  MySQLTuner 1.7.13 - Major Hayden <major@mhtx.net>
 >>  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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: (!!) Currently running unsupported MySQL version 8.0.25-0ubuntu0.20.04.1
(OK) Operating on 64-bit architecture
 
-------- Log file Recommendations ------------------------------------------------------------------
(--) Log file: /mnt/mysql/data/leo.err(0B)
(!!) Log file /mnt/mysql/data/leo.err doesn't exist
(!!) Log file /mnt/mysql/data/leo.err isn't readable.
 
-------- Storage Engine Statistics -----------------------------------------------------------------
(--) Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
(--) Data in InnoDB tables: 16.8G (Tables: 39)
(OK) Total fragmented tables: 0
 
-------- Analysis Performance Metrics --------------------------------------------------------------
(--) innodb_stats_on_metadata: OFF
(OK) No stat updates during querying INFORMATION_SCHEMA.
 
-------- Security Recommendations ------------------------------------------------------------------
(--) Skipped due to unsupported feature for MySQL 8
 
-------- CVE Security Recommendations --------------------------------------------------------------
(OK) NO SECURITY CVE FOUND FOR YOUR VERSION
 
-------- Performance Metrics -----------------------------------------------------------------------
(--) Up for: 1m 47s (419 q (3.916 qps), 45 conn, TX: 402K, RX: 35K)
(--) Reads / Writes: 100% / 0%
(--) Binary logging is enabled (GTID MODE: OFF)
(--) Physical Memory     : 125.8G
(--) Max MySQL memory    : 104.2G
(--) Other process memory: 136.7M
(--) Total buffers: 104.0G global + 1.1M per thread (151 max threads)
(--) P_S Max memory usage: 72B
(--) Galera GCache Max memory usage: 0B
(OK) Maximum reached memory usage: 104.0G (82.72% of installed RAM)
(OK) Maximum possible memory usage: 104.2G (82.85% of installed RAM)
(OK) Overall possible memory usage with other process is compatible with memory available
(OK) Slow queries: 0% (0/419)
(OK) Highest usage of available connections: 1% (2/151)
(!!) Aborted connections: 4.44%  (2/45)
(!!) name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
(--) Query cache have been removed in MySQL 8
(OK) Sorts requiring temporary tables: 0% (0 temp sorts / 19 sorts)
(!!) Joins performed without indexes: 18
(OK) Temporary tables created on disk: 0% (0 on disk / 23 total)
(OK) Thread cache hit rate: 95% (2 created / 45 connections)
(OK) Table cache hit rate: 80% (327 open / 408 opened)
(OK) Open file limit used: 0% (2/10K)
(OK) Table locks acquired immediately: 100% (9 immediate / 9 locks)
(OK) Binlog cache memory access: 0% (0 Memory / 0 Total)
 
-------- Performance schema ------------------------------------------------------------------------
(--) Memory used by P_S: 72B
(--) Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
(--) ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
(!!) Key buffer used: 18.2% (3M used / 16M cache)
(!!) Cannot calculate MyISAM index size - re-run script as root user
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
(--) InnoDB is enabled.
(--) InnoDB Thread Concurrency: 0
(OK) InnoDB File per table is activated
(OK) InnoDB buffer pool / data size: 104.0G/16.8G
(OK) Ratio InnoDB log file size / InnoDB Buffer pool size: 12.0G * 2/104.0G should be equal 25%
(OK) InnoDB buffer pool instances: 64
(--) Number of InnoDB Buffer Pool Chunk : 832 for 64 Buffer Pool Instance(s)
(OK) Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
(!!) InnoDB Read buffer efficiency: 13.96% (5858 hits/ 41974 total)
(OK) InnoDB Write log efficiency: 98.44% (630 hits/ 640 total)
(OK) InnoDB log waits: 0.00% (0 waits / 10 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:
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with JOINs)

What is a recommended aproach to narrow down the problem?

mysql – Safari can’t open “‎localhost/phpmyadmin/setup/” Safari can’t connect to “localhost”

However just typing “localhost” returns “It Works!”

Need to setup phpmyadmin which is located in /Library/WebServer/Documents/phpMyAdmin with the config file in /Library/WebServer/Documents/phpMyAdmin/config as config.php

-rw-r--r--   1 root      staff  4473 15 May 19:09 config.php

macOS Big Sur macOS 11.3.1 (20E241) on MacBook Pro.
MySQL 8.0.23-community.

Storing emojis in MySQL – Database Administrators Stack Exchange

I’m new to MySQL database. I want to store emojis in my database. I already have data in my database, and I don’t want to lose it.

I don’t know the proper way of storing the utf8mb4 character-set using the MySQL console. Is there any way of doing that using mysql-administrator?

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-----------------------+
| Variable_name           | Value                 |
+-------------------------+-----------------------+
| innodb_version          | 5.7.33                |
| protocol_version        | 10                    |
| slave_type_conversions  |                       |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2 |
| version                 | 5.7.33-log            |

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR 
Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8               |
| character_set_connection | utf8               |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8               |
| character_set_server     | utf8               |
| character_set_system     | utf8               |
| collation_connection     | utf8_general_ci    |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8_general_ci    |
+--------------------------+--------------------+
10 rows in set (0.01 sec)

I changed my.cnf file to

(client)
default-character-set = utf8mb4

(mysql)
default-character-set = utf8mb4

(mysqld)
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

It shows no error, but neither does it store the emojis.

Como criar um sistema de tempo online do usuario com php mysql

Como eu crio um sistema que calcula o tempo em q o usuario fica no site.
ex: entrou no site começa a contar… ai ficou ate uma hora e essa hora q ele ficou vai pro banco de dados e so vai somando quando entrar dnv…

php – recieving error while inserting int value as varchar in mysql

getting a syntax error when trying to insert some data.

Telling that i am storing a int value for flaw_val and am storing it in a varchar type in my database. I think this is where error is.
Help me figure the thing up.Also tell how to fix it up.

Main code:

$flawquery = "insert into flawed_data (serial_id,main_title,url_title,company,hosted_at,links_sup,flaw_dis,flaw_val,flaw_sol,file_name,current_date,current_time,name,username,email) values ('$serial_id' , '$title' , '$url_title' , '$company' , '$hosted_at' , '$links_supported' , '$flaw' , '$flaw_val' , '$flaw_sol' , '$fileNameNew' , '$current_date' , '$current_time' , '$name' , '$username' , '$email') ";

Thanks in advance.

mysql – ERROR 1047 (08S01): WSREP has not yet prepared node for application use

I have 3 node MySQL Galera cluster in Production, Today when we hit large DDoS on our network cause some issue and assuming some kind of network separation caused following issue on node-1

ERROR 1047 (08S01): WSREP has not yet prepared node for application use

node-2 and node-3 is working fine. in this condition what option i have to fix node-1 only without creating any chaos?

I did google and people only giving example of 2 node cluster but in my case its 3 node and its functional at present so i don’t want to bootstrap again, what is the safe way to bring this node back into cluster?

How does MySQL manage a delete then select of the same row within a transaction

Considering MySQL ver 5.7.
Take the following example:

Begin Transaction
Delete from payments where id = 1
Select from payments where id = 1
End Transaction

In such a scenario should I expect the select statement to return no rows, since I’ve just deleted that row? Or will it still return a row because the transaction has not yet been committed?

And if the select does not return a row, then can I assume that any cascade deletes on child tables are also safely deleted before I do my select?

(To answer the inevitable question of “why would you do this?” the answer is simply that I’m hoping to re-use the same script in multiple different scenarios, so I want to know if it’s safe to do so or if I have to write separate scripts. In one scenario the delete will execute, in another it will not.)

innodb – How to solve this inconsistency timing of MySQL queries?

SHOW CREATE TABLE foldertable

Result

CREATE TABLE `foldertable` (
 `serverToken` bigint(1) NOT NULL,
 `folderName` varchar(255) NOT NULL,
 `folderid` varchar(255) NOT NULL,
 `RootFolderPath` longtext NOT NULL,
 `createdTime` datetime NOT NULL,
 `LastEdited` datetime NOT NULL,
 `RootFolderPreviewPath` longtext NOT NULL,
 `userFolderPathName` text NOT NULL,
 `starred` tinyint(1) NOT NULL,
 `trashed` tinyint(1) NOT NULL,
 PRIMARY KEY (`folderid`) USING BTREE,
 UNIQUE KEY `folderid` (`folderid`),
 KEY `serverToken` (`serverToken`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

I am always executing second query while first transaction is running .
The sleep(10) is there intentionaly

First session

First Query :-

mysqli_begin_transaction($conn);

$sql = "SELECT * FROM foldertable where folderid  = '1234567' FOR  UPDATE" ;
$sql = "SELECT * FROM foldertable where serverToken  = 1234567 FOR  UPDATE" ;
mysqli_query($conn , $sql) ;
echo (mysqli_error($conn)) ;
echo(mysqli_affected_rows($conn)) ;
sleep (10);
mysqli_commit($conn) ;

2nd Query

INSERT INTO `foldertable`(
    `serverToken`,
    `folderName`,
    `folderid`,
    `RootFolderPath`,
    `createdTime`,
    `LastEdited`,
    `RootFolderPreviewPath`,
    `userFolderPathName`,
    `starred`,
    `trashed`
)
VALUES(
    12345,
    '',
    'ABCDE',
    '',
    '',
    '',
    '',
    '',
    '',
    ''
);


Result

1 row inserted. (Query took 9.4569 seconds.)

Second session

First Query :-

mysqli_begin_transaction($conn);

$sql = "SELECT * FROM foldertable where folderid  = '1234567' FOR  UPDATE" ;
$sql = "SELECT * FROM foldertable where serverToken  = 1234567 FOR  UPDATE" ;
mysqli_query($conn , $sql) ;
echo (mysqli_error($conn)) ;
echo(mysqli_affected_rows($conn)) ;
sleep (10);
mysqli_commit($conn) ;

2nd Query

INSERT INTO `foldertable`(
    `serverToken`,
    `folderName`,
    `folderid`,
    `RootFolderPath`,
    `createdTime`,
    `LastEdited`,
    `RootFolderPreviewPath`,
    `userFolderPathName`,
    `starred`,
    `trashed`
)
VALUES(
    12345,
    '',
    'ABCDEf',
    '',
    '',
    '',
    '',
    '',
    '',
    ''
);

Result

1 row inserted. (Query took 9.4569 seconds.)

Third session

First Query :-

mysqli_begin_transaction($conn);

$sql = "SELECT * FROM foldertable where folderid  = '1234567' FOR  UPDATE" ;
$sql = "SELECT * FROM foldertable where serverToken  = 1234567 FOR  UPDATE" ;
mysqli_query($conn , $sql) ;
echo (mysqli_error($conn)) ;
echo(mysqli_affected_rows($conn)) ;
sleep (10);
mysqli_commit($conn) ;

2nd Query

INSERT INTO `foldertable`(
    `serverToken`,
    `folderName`,
    `folderid`,
    `RootFolderPath`,
    `createdTime`,
    `LastEdited`,
    `RootFolderPreviewPath`,
    `userFolderPathName`,
    `starred`,
    `trashed`
)
VALUES(
    1234567,
    '',
    'ABCDEfg',
    '',
    '',
    '',
    '',
    '',
    '',
    ''
);

Result

1 row inserted. (Query took 9.4569 seconds.)

Forth session

First Query :-

mysqli_begin_transaction($conn);

$sql = "SELECT * FROM foldertable where folderid  = '1234567' FOR  UPDATE" ;
$sql = "SELECT * FROM foldertable where serverToken  = 1234567 FOR  UPDATE" ;
mysqli_query($conn , $sql) ;
echo (mysqli_error($conn)) ;
echo(mysqli_affected_rows($conn)) ;
sleep (10);
mysqli_commit($conn) ;

2nd Query

INSERT INTO `foldertable`( `serverToken`, `folderName`, `folderid`, `RootFolderPath`, `createdTime`, `LastEdited`, `RootFolderPreviewPath`, `userFolderPathName`, `starred`, `trashed` ) VALUES( 123, '', 'ABCDEfgh', '', '', '', '', '', '', '' )

Result

1 row inserted. (Query took 0.0073 seconds.)

Fifth session

First Query :-

mysqli_begin_transaction($conn);

$sql = "SELECT * FROM foldertable where folderid  = '1234567' FOR  UPDATE" ;
$sql = "SELECT * FROM foldertable where serverToken  = 1234567 FOR  UPDATE" ;
mysqli_query($conn , $sql) ;
echo (mysqli_error($conn)) ;
echo(mysqli_affected_rows($conn)) ;
sleep (10);
mysqli_commit($conn) ;

2nd Query

INSERT INTO `foldertable`( `serverToken`, `folderName`, `folderid`, `RootFolderPath`, `createdTime`, `LastEdited`, `RootFolderPreviewPath`, `userFolderPathName`, `starred`, `trashed` ) VALUES( 1235465, '', 'ABCEfgh', '', '', '', '', '', '', '' )

Result

1 row inserted. (Query took 9.5040 seconds.)

Fifth session

First Query :-

mysqli_begin_transaction($conn);

$sql = "SELECT * FROM foldertable where folderid  = '1234567' FOR  UPDATE" ;
$sql = "SELECT * FROM foldertable where serverToken  = 1234567 FOR  UPDATE" ;
mysqli_query($conn , $sql) ;
echo (mysqli_error($conn)) ;
echo(mysqli_affected_rows($conn)) ;
sleep (10);
mysqli_commit($conn) ;

2nd Query

INSERT INTO `foldertable`(
    `serverToken`,
    `folderName`,
    `folderid`,
    `RootFolderPath`,
    `createdTime`,
    `LastEdited`,
    `RootFolderPreviewPath`,
    `userFolderPathName`,
    `starred`,
    `trashed`
)
VALUES(
    1235465,
    '',
    'ABCEfghi',
    '',
    '',
    '',
    '',
    '',
    '',
    ''
);

Result

1 row inserted. (Query took 0.0087 seconds.)

I am quite new to mysql . I am using xampp for Testing and phpmyadmin for query and postman for transaction api .

So How can I solve this strange behaviour .