Howto: Database optimizing

Thought of something everyone else is having trouble with and just wanna help them? Post it here.
ralle030583
Padavan
Posts: 219
Joined: Sat Jun 14, 2008 20:24
Location: Germany
Contact:

Re: Howto: Database optimizing

Post by ralle030583 » Wed Oct 08, 2008 00:20

LaMaH wrote:The database currently contains: 522.714 killmails :lol:

IM COMMING FOR YOU EVE-KILL!! :D
stop fetching them eve-kill :-P (sry coudnt resist)

LaMaH
Apprentice
Posts: 44
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by LaMaH » Wed Oct 08, 2008 00:32

ralle030583 wrote:
LaMaH wrote:The database currently contains: 522.714 killmails :lol:

IM COMMING FOR YOU EVE-KILL!! :D
stop fetching them eve-kill :-P (sry coudnt resist)
im not :(
does eve-kill's feed even work btw.?..

i never seemed to be able to make anything fetch from it
Co-Owner and developer of EVSCO

ralle030583
Padavan
Posts: 219
Joined: Sat Jun 14, 2008 20:24
Location: Germany
Contact:

Re: Howto: Database optimizing

Post by ralle030583 » Wed Oct 08, 2008 09:55

should work if you do it for an corp / alliance
http://eve-kill.net/?a=feed&corp=Vanishing%20Point.

and that happens if you dont give anything to the feed:
http://eve-kill.net/?a=feed
Lear how to use this feed or move on...

hux
Greenhorn
Posts: 19
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by hux » Thu Oct 09, 2008 10:43

Is there an updated, final script to do all these changes? I haven't updated since 324, will do a clean 33x install these days and plan to use the db optimizing as well. As far as I understand, 33x is still not the version with the new installer, so I guess those db changes haven't made it to the svn yet.

beansman
Regular
Posts: 221
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by beansman » Thu Oct 09, 2008 10:46

If you wait a couple of days, it will all be in a brand new shiny installer :D
/HyperBeanie

Author of the Value Fetcher: Get It Here
Co-owner and developer of EVSCO

hux
Greenhorn
Posts: 19
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by hux » Thu Oct 09, 2008 11:12

Will work on my patience. :) Just asking myself now if those changes to the DB that will come with the new installer will cause problems with existing DBs. I mean... if I reinstall, got innoDB etc afterwards, and than try to import the MySQL dump... wonder if that works. Or if the changes to the existing data will be made on the fly.

beansman
Regular
Posts: 221
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by beansman » Thu Oct 09, 2008 11:15

As long as it's pure inserts, no problemo!

We are not changing table layout... (yet ;))
/HyperBeanie



Author of the Value Fetcher: Get It Here

Co-owner and developer of EVSCO

User avatar
FriedRoadKill
Regular
Posts: 226
Joined: Fri Jun 20, 2008 22:23

Re: Howto: Database optimizing

Post by FriedRoadKill » Thu Oct 09, 2008 19:53

So I was cruising through my DB after trying this stuff out on my test server and I noticed some inv_pilot fields pointing to kill ID 0 (a void id). This also applies to a few other tables. I checked it out somewhat, and my working theory is that mails that malform still write some information to the DB - causing data to be written that will never be seen.

So, seeing as it's the DB optimisation thread, why not also include some cleansing methods?
Here's some sql statements that checks if anything is referencing kill id 0, and deletes it.

Code: Select all

delete from kb3_kills where kll_id = 0;
delete from kb3_inv_detail where ind_kll_id = 0;
delete from kb3_inv_crp where inc_kll_id = 0;
delete from kb3_inv_plt where inp_kll_id = 0;
delete from kb3_items_destroyed where itd_kll_id = 0;
delete from kb3_items_dropped where itd_kll_id = 0;
Please to check this on your testbeds first before deploying to your lovely large kill DBs :). Now I've run this on my DBs, and I see no ill effects yet, just need somebody else to confirm before the masses apply it. This also isn't a comprehensive list - there may be are others like it.
I did data dumps. And some code. ~Now retired ~

User avatar
Seahorse
Apprentice
Posts: 48
Joined: Sat Jun 14, 2008 20:24
Location: In a bar, drinking JD...
Contact:

Re: Howto: Database optimizing

Post by Seahorse » Thu Oct 09, 2008 22:01

ralle030583 wrote:should work if you do it for an corp / alliance
http://eve-kill.net/?a=feed&corp=Vanishing%20Point.

and that happens if you dont give anything to the feed:
http://eve-kill.net/?a=feed
Lear how to use this feed or move on...
Error getting XML data from http://eve-kill.net/?a=feed&year=2008&week=41&gz=1
"Regrettably your planet is one of those scheduled for demolition"
Rgds
Mike

nutter
Greenhorn
Posts: 22
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by nutter » Thu Oct 09, 2008 23:41

FriedRoadKill wrote:So I was cruising through my DB after trying this stuff out on my test server and I noticed some inv_pilot fields pointing to kill ID 0 (a void id). This also applies to a few other tables. I checked it out somewhat, and my working theory is that mails that malform still write some information to the DB - causing data to be written that will never be seen.

So, seeing as it's the DB optimisation thread, why not also include some cleansing methods?
Here's some sql statements that checks if anything is referencing kill id 0, and deletes it.

Code: Select all

delete from kb3_kills where kll_id = 0;
delete from kb3_inv_detail where ind_kll_id = 0;
delete from kb3_inv_crp where inc_kll_id = 0;
delete from kb3_inv_plt where inp_kll_id = 0;
delete from kb3_items_destroyed where itd_kll_id = 0;
delete from kb3_items_dropped where itd_kll_id = 0;
Please to check this on your testbeds first before deploying to your lovely large kill DBs :). Now I've run this on my DBs, and I see no ill effects yet, just need somebody else to confirm before the masses apply it. This also isn't a comprehensive list - there may be are others like it.
hi ya iv just done it on my db and np probs yet m8 thanx dude

Post Reply

Return to “Tutorials”

Who is online

Users browsing this forum: No registered users and 12 guests