[ iggsy @ 22.11.2009. 18:47 ] @
Nisam bas siguran da je ovo pravi forum ali valjda ce neko pomoci ...
Napadaju mi server pa ga razvaljuju, i to pitaj boga odakle sve ne .. sumnjam da su u pitanju ddos napadi.
Pored tooga su mi skenirali ssh jedno nedelju dana sa brute force 24 sata dnevno, u isto vreme kao i ftp pa sam ftp ugasio a ssh-u promenio port i podigao firewall.

Pored toga sam pronasao dobru skriptu da blokira botove, mada mi je pojela jedno 5000 posetilaca dnevno ali sta da se radi.

Ima li neko ideju kako i zasto mi mysql kuca preko 100%? Analizirati acsess logove se ne isplati jer se mere gigabajtima.

Inace na serveru su dva sajta sa oko 30.000 posetilaca dnevno, u pitanju je LMAP root server sa webmin panelom.

Hvala
[ Tyler Durden @ 22.11.2009. 20:33 ] @
Pa moras da vidis koji upiti guse MySQL, da li postoji neko usko grlo, neki problematican query...
Vidi sa mysqladmin proc stat komandom za pocetak.
[ iggsy @ 23.11.2009. 13:30 ] @
Da li ovo nesto znaci?

+------+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+----+---------+------+-------+------------------+
| 1953 | root | localhost | | Sleep | 33 | | |
| 2135 | root | localhost | | Query | 0 | | show processlist |
+------+------+-----------+----+---------+------+-------+------------------+
Uptime: 445 Threads: 2 Questions: 10844 Slow queries: 0 Opens: 442 Flush tables: 1 Open tables: 64 Queries per second avg: 24.369

Ovi pikovi procesora kada se dese sajt mi izbaci error to many connections


dok je u to vreme baza izgleda ovako



help?!
[ iggsy @ 23.11.2009. 15:11 ] @
E ovo je momenat kada zakuca:

Code:

+-------+--------------+-----------+---------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| Id    | User         | Host      | db            | Command | Time | State                        | Info                                                                                                 |
+-------+--------------+-----------+---------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| 24115 | user1   | localhost | baza1  | Query   | 37   | closing tables               | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24130 | user1   | localhost | baza1  | Query   | 41   | closing tables               | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24143 | user1   | localhost | baza1  | Query   | 37   | closing tables               | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24168 | user1   | localhost | baza1  | Query   | 33   | closing tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24180 | user1   | localhost | baza1  | Query   | 16   | closing tables               | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24182 | user1   | localhost | baza1  | Query   | 37   | closing tables               | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24183 | user1   | localhost | baza1  | Query   | 32   | closing tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24195 | user1   | localhost | baza1  | Query   | 16   | Copying to tmp table on disk | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24204 | user1   | localhost | baza1  | Query   | 16   | converting HEAP to MyISAM    | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24205 | user1   | localhost | baza1  | Query   | 37   | removing tmp table           | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24206 | user1   | localhost | baza1  | Query   | 35   | Sorting result               | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24207 | user1   | localhost | baza1  | Query   | 35   | closing tables               | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24208 | user1   | localhost | baza1  | Sleep   | 16   |                              |                                                                                                      |
| 24214 | user1   | localhost | baza1  | Query   | 16   | Sorting result               | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24218 | user1   | localhost | baza1  | Query   | 17   | closing tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24222 | user1   | localhost | baza1  | Query   | 40   | closing tables               | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24223 | user1   | localhost | baza1  | Query   | 39   | closing tables               | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24231 | user1   | localhost | baza1  | Sleep   | 37   |                              |                                                                                                      |
| 24234 | user1   | localhost | baza1  | Query   | 16   | Copying to tmp table on disk | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24238 | user2 | localhost | baza2 | Sleep   | 3    |                              |                                                                                                      |
| 24239 | user2 | localhost | baza2 | Query   | 16   | closing tables               | SELECT id, title, date, keywords, category, alt_name, flag FROM _post WHERE MATCH (title, short_stor |
| 24240 | user1   | localhost | baza1  | Query   | 3    | converting HEAP to MyISAM    | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24241 | user1   | localhost | baza1  | Query   | 13   | Opening tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24243 | user2 | localhost | baza2 | Query   | 27   | closing tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24245 | user2 | localhost | baza2 | Query   | 35   | closing tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24244 | user2 | localhost | baza2 | Query   | 35   | closing tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24247 | user2 | localhost | baza2 | Query   | 35   | closing tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24248 | user2 | localhost | baza2 | Query   | 35   | closing tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24250 | user2 | localhost | baza2 | Query   | 34   | closing tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24251 | user1   | localhost | baza1  | Sleep   | 3    |                              |                                                                                                      |
| 24254 | user2 | localhost | baza2 | Query   | 33   | closing tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24255 | user1   | localhost | baza1  | Query   | 33   | closing tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24258 | user2 | localhost | baza2 | Sleep   | 3    |                              |                                                                                                      |
| 24260 | user1   | localhost | baza1  | Query   | 16   | closing tables               | SELECT id, title, date, keywords, category, alt_name, flag FROM _post WHERE MATCH (title, short_stor |
| 24261 | user1   | localhost | baza1  | Query   | 3    | Opening tables               | SELECT * FROM `_dlelinks_log` WHERE `ip`='89.142.117.135'                                            |
| 24262 | user1   | localhost | baza1  | Query   | 0    | Opening tables               | SELECT _comments.id, post_id, _comments.user_id, date, autor as gast_name, _comments.email as gast_e |
| 24263 | user1   | localhost | baza1  | Query   | 14   | closing tables               | SELECT id, title, date, keywords, category, alt_name, flag FROM _post WHERE MATCH (title, short_stor |
| 24266 | user1   | localhost | baza1  | Sleep   | 27   |                              |                                                                                                      |
| 24267 | user2 | localhost | baza2 | Query   | 27   | closing tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24270 | user1   | localhost | baza1  | Query   | 15   | converting HEAP to MyISAM    | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24271 | user1   | localhost | baza1  | Query   | 14   | closing tables               | SELECT * FROM `_dlelinks_log` WHERE `ip`='91.113.31.104'                                             |
| 24273 | user1   | localhost | baza1  | Query   | 14   | closing tables               | SELECT id, title, date, keywords, category, alt_name, flag FROM _post WHERE MATCH (title, short_stor |
| 24275 | user1   | localhost | baza1  | Query   | 24   | removing tmp table           | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24277 | user1   | localhost | baza1  | Query   | 21   | converting HEAP to MyISAM    | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24278 | user1   | localhost | baza1  | Query   | 19   | converting HEAP to MyISAM    | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24279 | user1   | localhost | baza1  | Query   | 3    | converting HEAP to MyISAM    | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24280 | user1   | localhost | baza1  | Query   | 15   | Opening tables               | SELECT * FROM `_dlelinks_log` WHERE `ip`='81.252.36.73'                                              |
| 24283 | user2 | localhost | baza2 | Query   | 3    | closing tables               | SELECT COUNT(*) as count FROM _post WHERE approve AND allow_main AND date < '2009-11-23 16:06:58'    |
| 24285 | user1   | localhost | baza1  | Query   | 10   | Opening tables               | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24286 | user1   | localhost | baza1  | Query   | 10   | Opening tables               | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24287 | user1   | localhost | baza1  | Query   | 15   | converting HEAP to MyISAM    | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24290 | user1   | localhost | baza1  | Query   | 14   | closing tables               | SELECT id, title, date, keywords, category, alt_name, flag FROM _post WHERE MATCH (title, short_stor |
| 24293 | user1   | localhost | baza1  | Sleep   | 16   |                              |                                                                                                      |
| 24295 | user2 | localhost | baza2 | Query   | 16   | closing tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24296 | user1   | localhost | baza1  | Sleep   | 16   |                              |                                                                                                      |
| 24297 | user2 | localhost | baza2 | Query   | 16   | closing tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24298 | user1   | localhost | baza1  | Query   | 3    | closing tables               | SELECT id, title, date, keywords, category, alt_name, flag FROM _post WHERE MATCH (title, short_stor |
| 24299 | user1   | localhost | baza1  | Sleep   | 16   |                              |                                                                                                      |
| 24300 | user1   | localhost | baza1  | Sleep   | 16   |                              |                                                                                                      |
| 24301 | user1   | localhost | baza1  | Query   | 3    | closing tables               | SELECT SQL_NO_CACHE id, autor, _post.date AS newsdate, _post.date AS date, short_story AS story, _po |
| 24302 | user1   | localhost | baza1  | Query   | 15   | converting HEAP to MyISAM    | SELECT id, title, date, alt_name FROM _post WHERE approve='1' ORDER BY RAND() LIMIT 0,15             |
| 24303 | user1   | localhost | baza1  | Sleep   | 15   |                              |                                                                                                      |
| 24304 | user2 | localhost | baza2 | Query   | 15   | closing tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24305 | user1   | localhost | baza1  | Sleep   | 15   |                              |                                                                                                      |
| 24306 | user1   | localhost | baza1  | Sleep   | 15   |                              |                                                                                                      |
| 24307 | user2 | localhost | baza2 | Query   | 15   | removing tmp table           | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24308 | user2 | localhost | baza2 | Query   | 15   | removing tmp table           | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24309 | user2 | localhost | baza2 | Query   | 14   | Creating tmp table           | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24310 | user1   | localhost | baza1  | Query   | 13   | removing tmp table           | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24311 | user1   | localhost | baza1  | Query   | 13   | Opening tables               | SELECT id, autor, date, short_story, full_story, xfields, title, category, descr, keywords, alt_name |
| 24312 | user1   | localhost | baza1  | Query   | 12   | Opening tables               | SELECT SQL_NO_CACHE COUNT(*) AS count FROM _post WHERE _post.approve AND _post.date < '2009-11-23 16 |
| 24313 | user2 | localhost | baza2 | Query   | 12   | Opening tables               | SELECT SQL_NO_CACHE COUNT(*) AS count FROM _post WHERE _post.approve AND _post.date < '2009-11-23 16 |
| 24314 | user2 | localhost | baza2 | Sleep   | 12   |                              |                                                                                                      |
| 24315 | user1   | localhost | baza1  | Query   | 12   | Opening tables               | SELECT id, autor, date, short_story, full_story, xfields, title, category, descr, keywords, alt_name |
| 24316 | user2 | localhost | baza2 | Query   | 12   | Opening tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24317 | user1   | localhost | baza1  | Query   | 12   | Opening tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24318 | user2 | localhost | baza2 | Query   | 12   | Opening tables               | SELECT id, autor, date, short_story, full_story, xfields, title, category, descr, keywords, alt_name |
| 24319 | user2 | localhost | baza2 | Sleep   | 11   |                              |                                                                                                      |
| 24320 | user2 | localhost | baza2 | Query   | 11   | Opening tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24321 | user2 | localhost | baza2 | Query   | 11   | Opening tables               | SELECT * FROM _users WHERE user_id='29504'                                                           |
| 24323 | user2 | localhost | baza2 | Query   | 10   | Opening tables               | SELECT id, autor, date, short_story, full_story, xfields, title, category, descr, keywords, alt_name |
| 24324 | user2 | localhost | baza2 | Query   | 10   | Opening tables               | SELECT id, autor, date, short_story, SUBSTRING(full_story, 1, 15) as full_story, xfields, title, cat |
| 24325 | user1   | localhost | baza1  | Query   | 10   | Opening tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24326 | user1   | localhost | baza1  | Query   | 8    | Opening tables               | SELECT * FROM `_dlelinks_log` WHERE `ip`='218.211.42.59'                                             |
| 24327 | user1   | localhost | baza1  | Query   | 8    | Opening tables               | SELECT id, autor, date, short_story, full_story, xfields, title, category, descr, keywords, alt_name |
| 24328 | user1   | localhost | baza1  | Query   | 6    | Opening tables               | SELECT SQL_NO_CACHE COUNT(*) AS count FROM _post WHERE _post.approve AND _post.date < '2009-11-23 16 |
| 24329 | user1   | localhost | baza1  | Query   | 6    | Opening tables               | SELECT SQL_NO_CACHE COUNT(*) AS count FROM _post WHERE _post.approve AND _post.date < '2009-11-23 16 |
| 24330 | user1   | localhost | baza1  | Query   | 6    | Opening tables               | SELECT SQL_NO_CACHE COUNT(*) AS count FROM _post WHERE _post.approve AND _post.date < '2009-11-23 16 |
| 24331 | user1   | localhost | baza1  | Query   | 6    | Opening tables               | SELECT SQL_NO_CACHE COUNT(*) AS count FROM _post WHERE _post.approve AND _post.date < '2009-11-23 16 |
| 24332 | user1   | localhost | baza1  | Query   | 6    | Opening tables               | SELECT id, autor, date, short_story, full_story, xfields, title, category, descr, keywords, alt_name |
| 24333 | user2 | localhost | baza2 | Sleep   | 6    |                              |                                                                                                      |
| 24334 | user2 | localhost | baza2 | Query   | 6    | Opening tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24335 | user1   | localhost | baza1  | Query   | 5    | Opening tables               | SELECT id, autor, date, short_story, full_story, xfields, title, category, descr, keywords, alt_name |
| 24336 | user1   | localhost | baza1  | Query   | 5    | Opening tables               | SELECT SQL_NO_CACHE COUNT(*) AS count FROM _post WHERE _post.approve AND _post.date < '2009-11-23 16 |
| 24337 | user1   | localhost | baza1  | Query   | 5    | Opening tables               | SELECT SQL_NO_CACHE COUNT(*) AS count FROM _post WHERE _post.approve AND _post.date < '2009-11-23 16 |
| 24338 | user1   | localhost | baza1  | Query   | 4    | Opening tables               | SELECT SQL_NO_CACHE COUNT(*) AS count FROM _post WHERE _post.approve AND _post.date < '2009-11-23 16 |
| 24339 | user1   | localhost | baza1  | Query   | 4    | Opening tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24340 | user1   | localhost | baza1  | Query   | 4    | Opening tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24341 | user2 | localhost | baza2 | Query   | 3    | Opening tables               | SELECT tekst FROM boris_search ORDER BY RAND() LIMIT 60                                              |
| 24342 | user2 | localhost | baza2 | Query   | 1    | Opening tables               | SELECT id, autor, date, short_story, full_story, xfields, title, category, descr, keywords, alt_name |
| 24343 | root         | localhost |               | Query   | 0    |                              | show processlist                                                                                     |
+-------+--------------+-----------+---------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
Uptime: 5448  Threads: 101  Questions: 124315  Slow queries: 636  Opens: 1090  Flush tables: 1  Open tables: 64  Queries per second avg: 22.818




Koj mu je djavo?
hvala!
[ Darklord @ 27.11.2009. 02:05 ] @
Prostudiraj statistiku web servera aktiviraj neku adresu. Direktiva u konfiguracji je server-status takodje utvrdi koliko imas konekcija na www server, moguce da su te botovi uhvatili na zub vidim tamo imas dosta tabela u vezi komentara koje su u datom trenutku otvorene. Da ne upisuju spam ?

Danijel,