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 » Mon Oct 06, 2008 09:17

<- ID10T :-/

you shouldn't run an alter table on a table with millions of entries like kb3_inv_details on an operating system.... :oops:
(its now running since 14h and no end in sight...)

edit: oh i see the light at the end of the tunnel:
State of mysql process:
XXXX| XXXX| XXXX| eve-kill | Query | 35229 | rename result table | ALTER TABLE `kb3_inv_detail` CHANGE `ind_sec_status` `ind_sec_status` CHAR(5)
35229 = ~8 hours threat runtime ... :?

note to myself next time:
lock the db next time cause:
| 112883 | XXX| localhost | eve-kill | Query | 34833 | Waiting for tables | insert into kb3_inv_detail values ( 1334149, 26751, '-10.0', 14, 9285, 30, 8117, 0 |
| 113238 | XXX| localhost | eve-kill | Query | 34493 | Waiting for tables | insert into kb3_inv_detail values ( 1334150, 77818, '0.8', 468, 17641, 7, 641, 0, |
| 113794 | XXX| localhost | eve-kill | Query | 33648 | Waiting for tables | insert into kb3_inv_detail values ( 1334151, 162153, '2.3', 1539, 1911, 17, 643, 0 |
| 113887 | XXX| localhost | eve-kill | Query | 33396 | Waiting for tables | insert into kb3_inv_detail values ( 1334152, 143706, '-10.0', 14, 9285, 427, 24692 |
| 113995 | XXX| localhost | eve-kill | Query | 33165 | Waiting for tables | insert into kb3_inv_detail values ( 1334153, 188523, '5.0', 502, 21935, 101, 27395 |
| 114127 | XXX| localhost | eve-kill | Query | 33054 | Waiting for tables | insert into kb3_inv_detail values ( 1334154, 45012, '-0.1', 1539, 28465, 223, 2164 |
| 114257 | XXX| localhost | eve-kill | Query | 32834 | Waiting for tables | delete from kb3_inv_detail where ind_kll_id = 1334153 |
| 114265 | XXX| localhost | eve-kill | Query | 32806 | Waiting for tables | insert into kb3_inv_detail values ( 1334155, 188523, '5.0', 502, 21935, 101, 27395 |
| 114296 | XXX| localhost | eve-kill | Query | 32672 | Waiting for tables | insert into kb3_inv_detail values ( 1334156, 41899, '2.96386140086429', 1539, 2846 |
| 114400 | XXX| localhost | eve-kill | Query | 32386 | Waiting for tables | insert into kb3_inv_detail values ( 1334157, 198068, '-2.0', 14, 21837, 26, 2881, |
| 114462 | XXX| localhost | eve-kill | Query | 32271 | Waiting for tables | insert into kb3_inv_detail values ( 1334158, 198068, '-2.0', 14, 21837, 26, 2881, |
| 114518 | XXX| localhost | eve-kill | Query | 32100 | Waiting for tables | insert into kb3_inv_detail values ( 1334159, 128452, '-10.0', 14, 20493, 427, 2456 |
| 114635 | XXX| localhost | eve-kill | Query | 31866 | Waiting for tables | delete from kb3_inv_detail where ind_kll_id = 1334159 |
| 114855 | XXX| localhost | eve-kill | Query | 31269 | Waiting for tables | insert into kb3_inv_detail values ( 1334160, 17712, '-8.4', 14, 20493, 17, 7085, 0 |
| 114909 | XXX| localhost | eve-kill | Query | 31169 | Waiting for tables | insert into kb3_inv_detail values ( 1334161, 17712, '-8.1', 14, 20493, 17, 7085, 0 |
| 115328 | XXX| localhost | eve-kill | Query | 30131 | Waiting for tables | insert into kb3_inv_detail values ( 1334162, 128452, '-10.0', 14, 20493, 427, 2456 |
| 115573 | XXX| localhost | eve-kill | Query | 29680 | Waiting for tables | insert into kb3_inv_detail values ( 1334163, 198068, '-2.0', 14, 21837, 26, 587, 0 |
| 115633 | XXX| localhost | eve-kill | Query | 29502 | Waiting for tables | insert into kb3_inv_detail
...

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

Re: Howto: Database optimizing

Post by LaMaH » Mon Oct 06, 2008 10:28

ralle030583 wrote:<- ID10T :-/
and other stuff
haha, "only" took 3h on the killmail.org database, was also enough.. :\
speed increase is pretty nice tho
Co-Owner and developer of EVSCO

Merrick Tolkien
Noob
Posts: 1
Joined: Sat Jun 28, 2008 11:06

Re: Howto: Database optimizing

Post by Merrick Tolkien » Mon Oct 06, 2008 16:48

Is there someone nice enough to make a sticky with a step by step guide on how to run this thing on the database?

Im a noob and way out of my depth when it comes to the database of the killboard but its running really slow and needs some love. Its not been "cleaned" or "optimised" or what ever in 18 months.

I know my way a little round the mysql interface but not well.

Thanks for any help you can give.

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

Re: Howto: Database optimizing

Post by beansman » Mon Oct 06, 2008 18:29

Open phpmyadmin ;)

Select your database, click the "SQL" tab.

Insert the stuff in the field, click ok.
/HyperBeanie

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

vascos
Apprentice
Posts: 45
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by vascos » Mon Oct 06, 2008 19:38

Thanks guys, those optimisations work a treat.

I thank you and my host thanks you :)
vasco di

Author of :- KillCharts Mod and BannerPic Mod

Tribalize
Apprentice
Posts: 69
Joined: Fri Aug 15, 2008 20:53

Re: Howto: Database optimizing

Post by Tribalize » Mon Oct 06, 2008 20:57

I Have An Upgrade Script For The Database Optimizations Listed On Page 1

Have Tested It On 2 Boards..Seems Fine

Would Like To Have A Second Set Of Eyes Check It

Do Not Run Until Tested By A Mod Or Admin
Attachments
sqlupgrade.rar
(32.3 KiB) Downloaded 303 times

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

Re: Howto: Database optimizing

Post by beansman » Tue Oct 07, 2008 10:06

Seems nice, but i think some of the tables could take too long, so if you make it with "states".

Like, do 1 query, reload page, do 1 query, reload page... think you understand me :)
And maybe set the timeout in the php files.
/HyperBeanie



Author of the Value Fetcher: Get It Here

Co-owner and developer of EVSCO

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

Re: Howto: Database optimizing

Post by beansman » Tue Oct 07, 2008 10:11

LaMaH wrote:
ralle030583 wrote:<- ID10T :-/
and other stuff
haha, "only" took 3h on the killmail.org database, was also enough.. :\
speed increase is pretty nice tho
Only took around 1 minute on http://svn.nsbit.dk/killboard ;)
/HyperBeanie



Author of the Value Fetcher: Get It Here

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 » Tue Oct 07, 2008 10:29

beansman wrote:
LaMaH wrote:
ralle030583 wrote:<- ID10T :-/
and other stuff
haha, "only" took 3h on the killmail.org database, was also enough.. :\
speed increase is pretty nice tho
Only took around 1 minute on http://svn.nsbit.dk/killboard ;)
could be cause of that small diff:
http://svn.nsbit.dk/killboard :
This killboard currently contains: 64.021 killmails,
http://www.eve-kill.net :
This killboard currently contains: 1.331.813 killmails,
;-)

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

Re: Howto: Database optimizing

Post by LaMaH » Tue Oct 07, 2008 17:39

The database currently contains: 522.714 killmails :lol:

IM COMMING FOR YOU EVE-KILL!! :D
Co-Owner and developer of EVSCO

Post Reply

Return to “Tutorials”

Who is online

Users browsing this forum: No registered users and 1 guest