Προβλημα με την καταραμενη sql

Τεχνικές και μόνο Συζητήσεις για WEB hosting servers, Mail servers, DNS servers. Όχι αναζήτηση υπηρεσιών εδώ!

Συντονιστές: WebDev Moderators, Super-Moderators

Απάντηση
Άβαταρ μέλους
nackgr
Δημοσιεύσεις: 1444
Εγγραφή: 22 Ιαν 2006 20:02
Τοποθεσία: Αθηνα
Επικοινωνία:

Προβλημα με την καταραμενη sql

Δημοσίευση από nackgr » 08 Ιουν 2013 01:01

γεια χαρα!

εχω θεματα με την καταραμενη sql σε ασχετες στιγμες παγώνει και ανεβαζει load στον σερβερ 25/30 και τα site απλα κανουν Load ...

13-14 σελιδες
15 databases (joomla /wp / 1 magento κ αλλα μικρα )
εχω i7
12gb ram



my.cfn

Κώδικας: Επιλογή όλων

[mysqld]
# Settings user and group are ignored when systemd is used (fedora >= 15).
# If you need to run mysqld under different user or group, 
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
user=mysql

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Semisynchronous Replication
# http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
# uncomment next line on MASTER
;plugin-load=rpl_semi_sync_master=semisync_master.so
# uncomment next line on SLAVE
;plugin-load=rpl_semi_sync_slave=semisync_slave.so

# Others options for Semisynchronous Replication
;rpl_semi_sync_master_enabled=1
;rpl_semi_sync_master_timeout=10
;rpl_semi_sync_slave_enabled=1

# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
;performance_schema

key_buffer = 384M
max_allowed_packet = 1M
table_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 16
query_cache_type = 1
query_cache_size = 128M
log_slow_queries=/var/log/mysqld.slowquery.log
max_connections=500
wait_timeout=120
tmp_table_size = 64M
max_heap_table_size = 64M
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 20M 

log-bin
log
log-error
slow-query-log
[mysqld_safe]
#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

#slow_query_log = 2
#log_slow_queries=/var/log/mysql/slow-query.log
#long_query_time=2
#log_queries_not_using_indexes=YES
#slow_query_log_file = /var/log/mysqld/log-slow-queries.log
αυταααααααααααα...
e-Byte.gr
Dafuq.gr

Άβαταρ μέλους
nackgr
Δημοσιεύσεις: 1444
Εγγραφή: 22 Ιαν 2006 20:02
Τοποθεσία: Αθηνα
Επικοινωνία:

Προβλημα με την καταραμενη sql

Δημοσίευση από nackgr » 08 Ιουν 2013 09:30

οταν ειναι κολημμενη εκανα show full process list στην mysql

Κώδικας: Επιλογή όλων

| Id    | User        | Host            | db          | Command | Time | State                        | Info                                                                                                                                                                   |
+-------+-------------+-----------------+-------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 14153 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672934' order by rand&#40;&#41; limit 4                                 |
| 14159 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    1 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672937' order by rand&#40;&#41; limit 1                                 |
| 14163 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |   70 | Copying to tmp table on disk | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and id!='42881' and id!='42880' and date_live<='1370672868' order by RAND&#40;&#41; LIMIT 1 |
| 14166 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    2 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672936' order by rand&#40;&#41; limit 1                                 |
| 14171 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    3 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672935' order by rand&#40;&#41; limit 4                                 |
| 14172 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672934' order by rand&#40;&#41; limit 1                                 |
| 14174 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    3 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672935' order by rand&#40;&#41; limit 1                                 |
| 14184 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    6 | Copying to tmp table on disk | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672932' order by rand&#40;&#41; limit 1                                 |
| 14186 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    5 | Copying to tmp table on disk | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672933' order by rand&#40;&#41; limit 1                                 |
| 14190 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    2 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672936' order by rand&#40;&#41; limit 4                                 |
| 14207 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | select * from ws_files where id = '1946'                                                                                                                               |
| 14210 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |   69 | Copying to tmp table on disk | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and id!='42881' and id!='42880' and date_live<='1370672869' order by RAND&#40;&#41; LIMIT 1 |
| 14211 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |   70 | Copying to tmp table on disk | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and id!='24946' and id!='24945' and date_live<='1370672867' order by RAND&#40;&#41; LIMIT 1 |
| 14212 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    5 | Copying to tmp table on disk | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672933' order by rand&#40;&#41; limit 1                                 |
| 14215 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    3 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672935' order by rand&#40;&#41; limit 4                                 |
| 14222 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    5 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND id > 32208 and approved='1' and date_live<='1370672933' order by id ASC LIMIT 1                  |
| 14223 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    5 | Copying to tmp table on disk | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and id!='4364' and id!='4363' and date_live<='1370672933' order by RAND&#40;&#41; LIMIT 1   |
| 14240 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672934' order by rand&#40;&#41; limit 1                                 |
| 14246 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    3 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672935' order by rand&#40;&#41; limit 4                                 |
| 14247 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672934' order by date_live DESC LIMIT 27395,5                   |
| 14248 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    5 | Copying to tmp table on disk | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and id!='4364' and id!='4363' and date_live<='1370672933' order by RAND&#40;&#41; LIMIT 1   |
| 14250 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND id > 47285 and approved='1' and date_live<='1370672934' order by id ASC LIMIT 1                  |
| 14251 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |   75 | removing tmp table           | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672863' order by rand&#40;&#41; limit 4                                 |
| 14252 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | select * from ws_files where id = '35858'                                                                                                                              |
| 14253 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    6 | Copying to tmp table on disk | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672932' order by rand&#40;&#41; limit 4                                 |
| 14254 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    3 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672935' order by date_live DESC LIMIT 14925,5                   |
| 14255 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND id > 9846 and approved='1' and date_live<='1370672933' order by id ASC LIMIT 1                   |
| 14256 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    5 | Copying to tmp table on disk | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and id!='27085' and id!='27084' and date_live<='1370672933' order by RAND&#40;&#41; LIMIT 1 |
| 14261 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and id!='9353' and id!='9352' and date_live<='1370672934' order by RAND&#40;&#41; LIMIT 1   |
| 14262 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    2 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672935' order by date_live DESC LIMIT 0,5                       |
| 14263 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and id!='18439' and id!='18438' and date_live<='1370672933' order by RAND&#40;&#41; LIMIT 1 |
| 14264 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |  102 | removing tmp table           | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672836' order by rand&#40;&#41; limit 1                                 |
| 14265 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    3 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672935' order by date_live DESC LIMIT 0,5                       |
| 14266 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    2 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672935' order by date_live DESC LIMIT 22775,5                   |
| 14268 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    3 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672935' order by date_live DESC LIMIT 0,5                       |
| 14269 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND id > 49357 and approved='1' and date_live<='1370672934' order by id ASC LIMIT 1                  |
| 14270 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    3 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672935' order by date_live DESC LIMIT 0,5                       |
| 14272 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and id!='43903' and id!='43902' and date_live<='1370672934' order by RAND&#40;&#41; LIMIT 1 |
| 14276 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |   56 | Copying to tmp table on disk | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672879' order by rand&#40;&#41; limit 1                                 |
| 14277 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |   56 | Copying to tmp table on disk | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672879' order by rand&#40;&#41; limit 1                                 |
| 14278 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |   56 | Copying to tmp table on disk | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672879' order by rand&#40;&#41; limit 1                                 |
| 14279 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |   56 | Copying to tmp table on disk | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672879' order by rand&#40;&#41; limit 1                                 |
| 14285 | ispconfig   | localhost&#58;58828 | dbispconfig | Sleep   |   51 |                              | NULL                                                                                                                                                                   |
| 14286 | root        | localhost       | NULL        | Query   |    0 | NULL                         | show full processlist                                                                                                                                                  |
| 14287 | ispconfig   | localhost&#58;58829 | dbispconfig | Sleep   |   43 |                              | NULL                                                                                                                                                                   |
| 14291 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND id > 47285 and approved='1' and date_live<='1370672934' order by id ASC LIMIT 1                  |
| 14292 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    5 | Copying to tmp table on disk | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and id!='21354' and id!='21353' and date_live<='1370672933' order by RAND&#40;&#41; LIMIT 1 |
| 14293 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and id!='40001' and id!='40000' and date_live<='1370672934' order by RAND&#40;&#41; LIMIT 1 |
| 14294 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | update ws_files set views = views+1 where id = '31166'                                                                                                                 |
| 14295 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | update ws_files set views = views+1 where id = '49914'                                                                                                                 |
| 14296 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | select * FROM ws_files WHERE  &#40;type = 'video' OR type = 'image'&#41;  AND approved='1' and date_live<='1370672934' order by rand&#40;&#41; limit 1                                 |
| 14297 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | SELECT * FROM ws_files WHERE id = '49345'                                                                                                                              |
| 14298 | c1_420Dafuq | localhost       | c1_420Dafuq | Query   |    4 | Waiting for table level lock | SELECT * FROM ws_files WHERE id = '47189'                                                                                                                              |
+-------+-------------+-----------------+-------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
53 rows in set &#40;0.00 sec&#41;
αυταααααααααααα...
e-Byte.gr
Dafuq.gr

Άβαταρ μέλους
dva_dev
Script Master
Δημοσιεύσεις: 3790
Εγγραφή: 16 Σεπ 2005 01:32
Επικοινωνία:

Προβλημα με την καταραμενη sql

Δημοσίευση από dva_dev » 08 Ιουν 2013 12:42

To "order by rand()" για ποιό λόγο το χρειάζεσαι;

Άβαταρ μέλους
Cha0s
SysAdmin
Δημοσιεύσεις: 10248
Εγγραφή: 28 Ιούλ 2001 03:00

Προβλημα με την καταραμενη sql

Δημοσίευση από Cha0s » 09 Ιουν 2013 22:02

Οι βάσεις είναι normalized;

Υπάρχουν παντού σωστά Indexes;

Ιδανικά δεν θα έπρεπε να χρησιμοποιεί tmp tables η mysql για να εκτελέσει το query.
Μάλιστα αν το dataset που προσπαθεί να κάνει order by είναι πάνω από 64MB τότε θα προσπαθήσει να ρίξει το tmp table στον δίσκο, που σημαίνει ότι θα έχεις υψηλό disk IO και αν είναι πολλά μαζεμένα τέτοια queries, μπορεί να κολλήσει ολόκληρο το μηχάνημα.

Αυτό που σε καίει τώρα είναι να διορθωθούν τα queries που χρησιμοποιούν tmp tables.

tmp tables η mysql δημιουργεί συνήθως όταν κάνεις ORDER BY, GROUP BY κλπ, εφόσον δεν έχουν σωστά indexes οι πίνακες που κάνεις τα queries.

Μπορείς να περιορίσεις λίγο το θέμα του disk IO για όσα tmp tables γράφονται στον δίσκο με χρήση ramdrive για αυτά.
Είχα δώσει οδηγίες εδώ για το ramdrive
https://www.freestuff.gr/forums/viewtop ... 529#577529

Επίσης γίνονται lock τα tables οπότε πολλά queries σύμφωνα με το process list που πόσταρες, απλά περιμένουν να γίνουν unlock οι πίνακες πριν εκτελεστούν.

Γενικά ιδανικά τα queries πρέπει να εκτελούνται σε μερικά ms και ότι είναι βαρύ αναγκαστικά να γίνεται cache (πχ με memcache).


Πέρα από αυτά, σε επίπεδο my.cnf θα αύξανα την query_cache σε 512Μ να έχει αρκετό χώρο για cache (εκτός αν δεν έχεις πολλά prunes)

Επίσης θα αύξανα τo tmp_table_size & max_heap_table_size σε 512Μ

Επίσης θα μείωνα τα max_connections σε 100.
Καλύτερα είναι να βγάζει μήνυμα λάθους ότι είναι full από connections παρά να προσπαθεί να εκτελέσει παραπάνω queries από αυτά που μπορεί και να κολλήσει όλο το μηχάνημα.

Το Logging που κάνεις το χρειάζεσαι; Όσο λιγότερα write IOPs στον δίσκο, τόσο καλύτερα όταν θα έχεις κίνηση.

Απάντηση

Επιστροφή στο “Apache, IIS, DNS Servers”

Μέλη σε σύνδεση

Μέλη σε αυτήν τη Δ. Συζήτηση: Δεν υπάρχουν εγγεγραμμένα μέλη και 0 επισκέπτες