Neue Antwort schreiben 
 
Themabewertung:
  • 0 Bewertung(en) - 0 im Durchschnitt
  • 1
  • 2
  • 3
  • 4
  • 5
MYSQL 5.7 "crashes" on SELECT DISTINCT query
Michael_ Offline
Bier aus Hawaii

Beiträge: 1.510
Registriert seit: Feb 2011
Beitrag #1
MYSQL 5.7 "crashes" on SELECT DISTINCT query
Rufe ich meine home.php im eingeloggten Zustand auf dann läd die Seite ewig.
Nur ein Neustart von MYSQL behebt das Problem.

Folgender MYSQL Prozess wird geöffnet wenn ich die home.php aufrufe:
Dieser scheint mysql zu crashen oder overloaden oder was auch immer:

Zitat: yourwbb_root localhost yourwbb_wbb2 Query 4 Sending data SELECT DISTINCT p.userid, t.*, b.boardid, b.title, b.hotthread_reply, b.hotthread_view, b.postsperpa

Wenn ich in der home.php folgenden Code ausklammer geht es wieder einwandfrei:

Code:
    if ($wbbuserdata['userid']) {
         $ywhome_ownuserid = "DISTINCT p.userid,";
         $ywhome_ownjoin = "LEFT JOIN bb".$n."_posts p ON (t.threadid = p.threadid AND p.userid = '$wbbuserdata[userid]')";
    } else {
         $ywhome_ownuserid = '';
         $ywhome_ownjoin = '';    
    }

Ich denke daher das DISTINCT das Problem auslöst?
Nur wieso?

ich hab in der mysql konfigurationsdatei

sql_mode=MYSQL40 eingetragen hat das damit was zu tun?

war mein fix hierfür:
https://stackoverflow.com/questions/3466...e-in-mysql

Hoffe ihr könnt mir helfen.

am sql_mode liegt es nicht hab ich grad entfernt hat nichts gebracht.
nur strict_mode rausnehmen hat auch nichts gebracht.

Weiß keiner rat?
(Dieser Beitrag wurde zuletzt bearbeitet: 11.10.2018 20:28 von winfreak.)
10.10.2018 20:59
Alle Beiträge dieses Benutzers finden Diese Nachricht in einer Antwort zitieren
Michael_ Offline
Bier aus Hawaii

Beiträge: 1.510
Registriert seit: Feb 2011
Beitrag #2
RE: MYSQL 5.7 "crashes" on SELECT DISTINCT query
Dieser fehler macht mich verrückt.

wenn ich ein frisches wbb2 installiere und da den homeseiten hack funktioniert es einwandfrei.
auch mit der home.php aus dem forum das probleme macht.
Serverload im ACP steigt bei dem problem übrigens auf 200% an.

Das Problem ist das dieser Query beim hauptforum für immer data sendet und dann andere somit in die warteschleife hängt.

Eig muss es doch an den mysql einstellungen liegen oder? nur wo?
vllt. auch weil in dem fall viele daten abzufragen sind???
11.10.2018 00:19
Alle Beiträge dieses Benutzers finden Diese Nachricht in einer Antwort zitieren
Michael_ Offline
Bier aus Hawaii

Beiträge: 1.510
Registriert seit: Feb 2011
Beitrag #3
RE: MYSQL 5.7 "crashes" on SELECT DISTINCT query
Cpanel support

Zitat:Hi there,

I can see what is happening, but it will likely require a qualified systems administrator to get involved as it appears to be a problem with the running query.

The query you already mentioned comes up first, then it seems to hold the table in a lock under a state of "Sending data". While it's doing this, other queries start to build up and they're stuck waiting because the table is locked.

Ja das ist genau das was ich auch schon weiß, ne lösung hat er nicht parat.
zumal das script auf dem alten server 1:1 lief und nur hier probleme macht.

warum???
11.10.2018 02:38
Alle Beiträge dieses Benutzers finden Diese Nachricht in einer Antwort zitieren
freaked Offline
× ∫яεαкεδ εησυġн × ζιgнтѕтαя ×

Beiträge: 17.046
Registriert seit: Jul 2008
Beitrag #4
RE: MYSQL 5.7 "crashes" on SELECT DISTINCT query
Zitat: it will likely require a qualified systems administrator to get involved
[Bild: giphy.gif]

11.10.2018 04:11
Webseite des Benutzers besuchen Alle Beiträge dieses Benutzers finden Diese Nachricht in einer Antwort zitieren
Michael_ Offline
Bier aus Hawaii

Beiträge: 1.510
Registriert seit: Feb 2011
Beitrag #5
RE: MYSQL 5.7 "crashes" on SELECT DISTINCT query
Kann das an MYSQL 5.7 liegen? auf dem alten Server lief MYSQL 5.6
11.10.2018 04:34
Alle Beiträge dieses Benutzers finden Diese Nachricht in einer Antwort zitieren
Alpha Offline
Oskar

Beiträge: 16.344
Registriert seit: Jan 2009
Beitrag #6
RE: MYSQL 5.7 "crashes" on SELECT DISTINCT query
Ja, bestimmt! ... NOT

Mark IV Style Motherfucker!
(Dieser Beitrag wurde zuletzt bearbeitet: 11.10.2018 07:27 von Alpha.)
11.10.2018 07:27
Alle Beiträge dieses Benutzers finden Diese Nachricht in einer Antwort zitieren
Blue Offline
Seit dem 17.10.2006 dabei!

Beiträge: 21.529
Registriert seit: Jul 2008
Beitrag #7
RE: MYSQL 5.7 "crashes" on SELECT DISTINCT query
(11.10.2018 04:11)freaked schrieb:  
Zitat: it will likely require a qualified systems administrator to get involved
[Bild: giphy.gif]
:atomrofl:
11.10.2018 07:44
Alle Beiträge dieses Benutzers finden Diese Nachricht in einer Antwort zitieren
Michael_ Offline
Bier aus Hawaii

Beiträge: 1.510
Registriert seit: Feb 2011
Beitrag #8
RE: MYSQL 5.7 "crashes" on SELECT DISTINCT query
Zitat:While reviewing the query, I see that this is a 'SELECT DISTINCT'. Since the DISTINCT variable is being passed, this adds a step to sort the queries row information and will normally increase run times of queries. There are a couple ways to work around this, such as using a GROUP by or indexing instead. Since you indicated that you do not want to modify the code, you may instead want to work with a system administrator to adjust your optimization settings in the my.cnf.

In some cases, increasing certain variables(especially memory usage and temp table sizes) can allow for faster completion of large/slow queries like the one being generated. Most likely, the original host had more verbose settings that allowed this query to complete in a faster timeframe. Since your server has a large amount of available RAM(64G), the Mysql settings could be greatly increased for memory usage. Due to the potential impact to server stability, it is strongly recommended that you work with a qualified system administrator when customizing the Mysql settings.

Jemand plan von mysql und was genau ich in der my.cnf anpassen könnte?
11.10.2018 18:22
Alle Beiträge dieses Benutzers finden Diese Nachricht in einer Antwort zitieren
Michael_ Offline
Bier aus Hawaii

Beiträge: 1.510
Registriert seit: Feb 2011
Beitrag #9
RE: MYSQL 5.7 "crashes" on SELECT DISTINCT query
Lokal mit Uwamp funktioniert es mit MYSQL 5.7 ohne Probleme.

This drive me nuts

Hat hier keiner Ahnung von Servern???

Hier noch paar infos vllt. hilft das ja weiter ^^

Zitat: >> 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
[OK] Currently running supported MySQL version 5.7.23
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ---------------------------------------------- --------------------
[--] Log file: /var/log/mysqld.log(474K)
[OK] Log file /var/log/mysqld.log exists
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 195 warning(s).
[!!] /var/log/mysqld.log contains 46 error(s).
[--] 47 start(s) detected in /var/log/mysqld.log
[--] 1) 2018-10-12T00:36:15.444184Z 0 [Note] /usr/sbin/mysqld: ready for connect ions.
[--] 2) 2018-10-11T18:53:27.325995Z 0 [Note] /usr/sbin/mysqld: ready for connect ions.
[--] 3) 2018-10-11T18:51:13.454323Z 0 [Note] /usr/sbin/mysqld: ready for connect ions.
[--] 4) 2018-10-11T18:45:25.800882Z 0 [Note] /usr/sbin/mysqld: ready for connect ions.
[--] 5) 2018-10-11T18:42:03.132576Z 0 [Note] /usr/sbin/mysqld: ready for connect ions.
[--] 6) 2018-10-11T18:39:20.937986Z 0 [Note] /usr/sbin/mysqld: ready for connect ions.
[--] 7) 2018-10-11T18:33:51.398004Z 0 [Note] /usr/sbin/mysqld: ready for connect ions.
[--] 8) 2018-10-11T18:30:52.282564Z 0 [Note] /usr/sbin/mysqld: ready for connect ions.
[--] 9) 2018-10-11T18:13:42.600315Z 0 [Note] /usr/sbin/mysqld: ready for connect ions.
[--] 10) 2018-10-11T18:08:44.174380Z 0 [Note] /usr/sbin/mysqld: ready for connec tions.
[--] 61 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2018-10-12T00:36:14.359708Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2018-10-11T18:53:25.624189Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2018-10-11T18:51:10.372080Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2018-10-11T18:45:24.667020Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2018-10-11T18:42:01.971305Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2018-10-11T18:39:19.348540Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2018-10-11T18:33:50.221718Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2018-10-11T18:30:08.902981Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2018-10-11T18:29:13.579274Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2018-10-11T18:29:02.754675Z 0 [Note] /usr/sbin/mysqld: Shutdown complet e

-------- Storage Engine Statistics --------------------------------------------- --------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +My ISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 549.1M (Tables: 145)
[--] Data in InnoDB tables: 16.0K (Tables: 1)
[--] Data in MEMORY tables: 295.9K (Tables: 2)
[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
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

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

-------- Performance Metrics --------------------------------------------------- --------------------
[--] Up for: 4h 13m 54s (46K q [3.024 qps], 5K conn, TX: 119M, RX: 5M)
[--] Reads / Writes: 74% / 26%
[--] Binary logging is disabled
[--] Physical Memory : 62.7G
[--] Max MySQL memory : 11.2G
[--] Other process memory: 758.6M
[--] Total buffers: 169.0M global + 1.1M per thread (10000 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 183.6M (0.29% of installed RAM)
[OK] Maximum possible memory usage: 11.2G (17.80% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/46K)
[OK] Highest usage of available connections: 0% (13/10000)
[OK] Aborted connections: 0.05% (3/5711)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 21K selects)
[OK] Query cache prunes per day: 0
[!!] Sorts requiring temporary tables: 31% (51 temp sorts / 161 sorts)
[!!] Joins performed without indexes: 2452
[OK] Temporary tables created on disk: 5% (17 on disk / 286 total)
[OK] Thread cache hit rate: 99% (13 created / 5K connections)
[OK] Table cache hit rate: 77% (625 open / 808 opened)
[OK] Open file limit used: 1% (641/50K)
[OK] Table locks acquired immediately: 99% (37K immediate / 37K locks)

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

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

-------- MyISAM Metrics -------------------------------------------------------- --------------------
[!!] Key buffer used: 33.3% (2M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/213.6M
[OK] Read Key buffer hit rate: 98.9% (110K cached / 1K reads)
[!!] Write Key buffer hit rate: 57.1% (7 cached / 4 writes)

-------- InnoDB Metrics -------------------------------------------------------- --------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/16.0K
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128. 0M should be equal 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
[!!] InnoDB Read buffer efficiency: 85.56% (1487 hits/ 1738 total)
[!!] InnoDB Write Log efficiency: 0% (2 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2 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/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
MySQL was started within the last 24 hours - recommendations may be inaccura te
Configure your accounts with ip or subnets only, then update your configurat ion with skip-name-resolve=1
Adjust your join queries to always utilize indexes
Performance schema should be activated for better diagnostics
Before changing innodb_log_file_size and/or innodb_log_files_in_group read t his: http://bit.ly/2wgkDvS
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
sort_buffer_size (> 256K)
read_rnd_buffer_size (> 256K)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
performance_schema = ON enable PFS
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
[root@server ~]# ^C
[root@server ~]# ^C
[root@server ~]# -------- Performance Metrics --------------------------------------------------- --------------------
-bash: --------: command not found
[root@server ~]# [--] Up for: 4h 13m 54s (46K q [3.024 qps], 5K conn, TX: 119M, RX: 5M)
-bash: syntax error near unexpected token `('
[root@server ~]# [--] Reads / Writes: 74% / 26%
-bash: [--]: command not found
[root@server ~]# [--] Binary logging is disabled
-bash: [--]: command not found
[root@server ~]# [--] Physical Memory : 62.7G
-bash: [--]: command not found
[root@server ~]# [--] Max MySQL memory : 11.2G
-bash: [--]: command not found
[root@server ~]# [--] Other process memory: 758.6M
-bash: [--]: command not found
[root@server ~]# [--] Total buffers: 169.0M global + 1.1M per thread (10000 max threads)
-bash: syntax error near unexpected token `('
[root@server ~]# [--] P_S Max memory usage: 0B
-bash: [--]: command not found
[root@server ~]# [--] Galera GCache Max memory usage: 0B
-bash: [--]: command not found
[root@server ~]# [OK] Maximum reached memory usage: 183.6M (0.29% of installed RAM)
-bash: syntax error near unexpected token `('
[root@server ~]# [OK] Maximum possible memory usage: 11.2G (17.80% of installed RAM)
-bash: syntax error near unexpected token `('
[root@server ~]# [OK] Overall possible memory usage with other process is compatible with memory available
-bash: [OK]: command not found
[root@server ~]# [OK] Slow queries: 0% (0/46K)
-bash: syntax error near unexpected token `('
[root@server ~]# [OK] Highest usage of available connections: 0% (13/10000)
-bash: syntax error near unexpected token `('
[root@server ~]# [OK] Aborted connections: 0.05% (3/5711)
-bash: syntax error near unexpected token `('
[root@server ~]# [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
-bash: !]: event not found
[root@server ~]# [!!] Query cache may be disabled by default due to mutex contention.
-bash: !]: event not found
[root@server ~]# [!!] Query cache efficiency: 0.0% (0 cached / 21K selects)
-bash: !]: event not found
[root@server ~]# [OK] Query cache prunes per day: 0
-bash: [OK]: command not found
[root@server ~]# [!!] Sorts requiring temporary tables: 31% (51 temp sorts / 161 sorts)
-bash: !]: event not found
[root@server ~]# [!!] Joins performed without indexes: 2452
-bash: !]: event not found
[root@server ~]# [OK] Temporary tables created on disk: 5% (17 on disk / 286 total)
-bash: syntax error near unexpected token `('
[root@server ~]# [OK] Thread cache hit rate: 99% (13 created / 5K connections)
-bash: syntax error near unexpected token `('
[root@server ~]# [OK] Table cache hit rate: 77% (625 open / 808 opened)
-bash: syntax error near unexpected token `('
[root@server ~]# [OK] Open file limit used: 1% (641/50K)
-bash: syntax error near unexpected token `('
[root@server ~]# [OK] Table locks acquired immediately: 99% (37K immediate / 37K locks)
(Dieser Beitrag wurde zuletzt bearbeitet: 12.10.2018 05:59 von Michael_.)
12.10.2018 05:04
Alle Beiträge dieses Benutzers finden Diese Nachricht in einer Antwort zitieren
s4ndwichMakeR Offline
Realitätsfeinmotoriker‮

Beiträge: 5.195
Registriert seit: Jul 2008
Beitrag #10
RE: MYSQL 5.7 "crashes" on SELECT DISTINCT query
Hast du max_allowed_packet und vor allem max_execution_time (ab 5.7.4 bis exkl. 5.7.8) bzw. max_statement_time (ab 5.7.8) zur Rumtime schon überprüft? Letzteres wird die Laufzeit nicht verbessern, aber zumindest ein Ergebnis liefern, wenn es an der schieren Ausführzeit hängen sollte. Ich befürchte aber, die Default-Werte sind schon sehr mild und führten dich schließlich doch wieder in den Performance-Fix-Bereich.

• • • – • – – • – –
12.10.2018 10:04
Alle Beiträge dieses Benutzers finden Diese Nachricht in einer Antwort zitieren
Neue Antwort schreiben 


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste