Bravo List - Announce.php high load in mysqld on 1500 peers

Bravo List (http://www.bvlist.com/index.php)

- Free Torrent Source (http://www.bvlist.com/forumdisplay.php?f=25)

- - Announce.php high load in mysqld on 1500 peers (http://www.bvlist.com/showthread.php?t=4107)

Announce.php high load in mysqld on 1500 peers

Announce.php high load in mysqld on 1500 peers
anyone can help me to use announce from tbdev

Quote:

Originally Posted by cobrabm (Post 18814)

Announce.php high load in mysqld on 1500 peers
anyone can help me to use announce from tbdev

You will have the same problem with tbdev announce.php on a large tracker.

You have to add aditional indexes to your database tables peers, sntached , usergroups and users.
The index of your tables will grow up, but you will earn speed!

Add to user table:

PHP Code:


		
			
ALTER  TABLE  `users`  ADD  INDEX  `passkey` (  `passkey`  );

Add to snatched table:

PHP Code:


		
			
ALTER  TABLE  `snatched`  ADD  INDEX  `torrent_user` (  `torrentid` ,  `userid`  );

Add to peers table:

PHP Code:


		
			
ALTER  TABLE  `peers`  DROP  INDEX  `torrent_peer_id` ,
 ADD  INDEX  `torrent_peer_id` (  `torrent` ,  `peer_id` ,  `passkey`  ) ;

Add to table usergoups:

PHP Code:


		
			
ALTER  TABLE  `usergroups`  ADD  INDEX  `hassfreeleech` (  `hasfreeleech`  );

This are some examples, you can add more, but be carefull. Large indexes can have too negativ impacts to your serverload.

Dont forget to increase your key buffer size for mysql engine :D

Please tell how helpfull this was!

this commands helps me now mysqld stay on 20-60% not in 100-150 %

how much to insrase key buffer size?

Go to phpmyadmin and look how big all your indexes together are.
Let us say your indexes are 100MB big, then set it to +50% = 150MB and look from time to time if you need to increase key buffer again.

You can add more indexes if you need. You have to look over each mysql_query($string) that is often loaded.
And if the query search on a not indexed column then add index to it.

Example1:

PHP Code:


		
			
SELECT * FROM users WHERE ip = $ip

if the column is not indexed then add index to it.

Example2:

PHP Code:


		
			
SELECT * FROM users WHERE class >= $class AND donor = yes

in these case you have to add an index over the columns "class" AND "donor"

Or if the where statment search over 3 or 4 columns you have to add an index over 3 or 4 columns.
You have to try which are the best indexes for you.

how to remove anti-cheat mod this high load

Before you try to remove the mod, try this change to DB:

PHP Code:


		
			
ALTER TABLE `anti_cheat` DROP INDEX `usertorrent` ,
ADD INDEX `usertorrent` ( `user_id` , `torrent_id` , `uploaded` , `downloaded` ) ;

Is verry helpfully for me thx much

If you know something ticks tell me

Thanks a lot

tell us please how much your server load decrease in this case?

25836 mysql 20 0 234m 49m 6220 S 14 2.5 7:33.31 mysqld
and not jump after 50 %

Bump: now jumping cpu after 100% please help to full indexing the database

Bump: any one can say me somme indexing triks ?