Howto: Database optimizing

[beansman]

Ok, here goes a try:

First of all, change your tables to innodb (if you can, i cannot guarantee that this will help if not on innodb)

Code: Select all

ALTER TABLE `kb3_alliances` ENGINE = InnoDB;
ALTER TABLE `kb3_banned_mails` ENGINE = InnoDB;
ALTER TABLE `kb3_comments` ENGINE = InnoDB;
ALTER TABLE `kb3_config` ENGINE = InnoDB;
ALTER TABLE `kb3_constellations` ENGINE = InnoDB;
ALTER TABLE `kb3_contracts` ENGINE = InnoDB;
ALTER TABLE `kb3_contract_details` ENGINE = InnoDB;
ALTER TABLE `kb3_corps` ENGINE = InnoDB;
ALTER TABLE `kb3_dgmattributetypes` ENGINE = InnoDB;
ALTER TABLE `kb3_dgmeffects` ENGINE = InnoDB;
ALTER TABLE `kb3_dgmtypeattributes` ENGINE = InnoDB;
ALTER TABLE `kb3_item_locations` ENGINE = InnoDB;
ALTER TABLE `kb3_eveunits` ENGINE = InnoDB;
ALTER TABLE `kb3_invtypes` ENGINE = InnoDB;
ALTER TABLE `kb3_inv_all` ENGINE = InnoDB;
ALTER TABLE `kb3_inv_crp` ENGINE = InnoDB;
ALTER TABLE `kb3_inv_detail` ENGINE = InnoDB;
ALTER TABLE `kb3_inv_plt` ENGINE = InnoDB;
ALTER TABLE `kb3_ip_bans` ENGINE = InnoDB;
ALTER TABLE `kb3_items_destroyed` ENGINE = InnoDB;
ALTER TABLE `kb3_items_dropped` ENGINE = InnoDB;
ALTER TABLE `kb3_item_price` ENGINE = InnoDB;
ALTER TABLE `kb3_item_types` ENGINE = InnoDB;
ALTER TABLE `kb3_kills` ENGINE = InnoDB;
ALTER TABLE `kb3_log` ENGINE = InnoDB;
ALTER TABLE `kb3_navigation` ENGINE = InnoDB;
ALTER TABLE `kb3_pilots` ENGINE = InnoDB;
ALTER TABLE `kb3_races` ENGINE = InnoDB;
ALTER TABLE `kb3_regions` ENGINE = InnoDB;
ALTER TABLE `kb3_roles` ENGINE = InnoDB;
ALTER TABLE `kb3_ships` ENGINE = InnoDB;
ALTER TABLE `kb3_ships_values` ENGINE = InnoDB;
ALTER TABLE `kb3_ship_classes` ENGINE = InnoDB;
ALTER TABLE `kb3_standings` ENGINE = InnoDB;
ALTER TABLE `kb3_systems` ENGINE = InnoDB;
ALTER TABLE `kb3_system_jumps` ENGINE = InnoDB;
ALTER TABLE `kb3_titles` ENGINE = InnoDB;
ALTER TABLE `kb3_titles_roles` ENGINE = InnoDB;
ALTER TABLE `kb3_user` ENGINE = InnoDB;
ALTER TABLE `kb3_user_extra` ENGINE = InnoDB;
ALTER TABLE `kb3_user_roles` ENGINE = InnoDB;
ALTER TABLE `kb3_user_titles` ENGINE = InnoDB;

Afterwards, add indexes:

Code: Select all

ALTER TABLE `kb3_comments` ADD INDEX `kll_id` ( `kll_id` , `posttime` );
ALTER TABLE `kb3_config` ADD INDEX ( `cfg_site` ( 8 ) , `cfg_key` ( 32 ) , `cfg_value` ( 16 ) );
ALTER TABLE `kb3_contracts` ADD INDEX `ctr_id` ( `ctr_id` , `ctr_site` , `ctr_campaign` , `ctr_ended` );
ALTER TABLE `kb3_contracts` ADD INDEX ( `ctr_site` );
ALTER TABLE `kb3_inv_plt` ADD INDEX `inp_kll_id` ( `inp_kll_id` , `inp_plt_id` );
ALTER TABLE `kb3_roles` ADD INDEX `rol_site` ( `rol_site` , `rol_name` , `rol_descr` );
ALTER TABLE `kb3_pilots` ADD INDEX ( `plt_externalid` );
ALTER TABLE `kb3_systems` ADD INDEX ( `sys_eve_id` ) ;
ALTER TABLE `kb3_systems` ADD INDEX ( `sys_con_id` ) ;
ALTER TABLE `kb3_constellations` ADD INDEX ( `con_reg_id` ) ;
ALTER TABLE `kb3_comments` ADD INDEX ( `kll_id` ) ;
ALTER TABLE `kb3_comments` ADD INDEX ( `posttime` ) ;
ALTER TABLE `kb3_inv_plt` ADD INDEX ( `inp_kll_id` );
ALTER TABLE `kb3_kills` ADD INDEX ( `kll_fb_all_id` );
ALTER TABLE `kb3_kills` ADD INDEX ( `kll_fb_crp_id` );
ALTER TABLE `kb3_kills` ADD INDEX ( `kll_fb_plt_id` );
ALTER TABLE `kb3_item_locations` ADD INDEX `itl_id` ( `itl_location` , `itl_id` );
ALTER TABLE `kb3_item_locations` ADD INDEX `itl_id_2` ( `itl_id` , `itl_location` );
ALTER TABLE `kb3_items_dropped` ADD INDEX ( `itd_itl_id` , `itd_kll_id` , `itd_itm_id` );
ALTER TABLE `kb3_items_destroyed` ADD INDEX ( `itd_itl_id` , `itd_kll_id` , `itd_itm_id` );

Additions:

Code: Select all

#8/10 2008:
ALTER TABLE `kb3_corps` ADD INDEX ( `crp_name` );
ALTER TABLE `kb3_systems` ADD INDEX ( `sys_name` );
ALTER TABLE `kb3_alliances` ADD INDEX ( `all_name` );
#14/10 2008
ALTER TABLE `kb3_dgmtypeeffects` ENGINE = InnoDB;
ALTER TABLE `kb3_moons` ENGINE = InnoDB;

This might make your db fill a little more, but it's worth it if you are struggling with speed!

InnoDB setup reported helping, in mysql config:

Code: Select all

innodb_buffer_pool_size = 512M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT

Please return with feedback, did it help? Did i kill your host?

[beansman]

Some might want this too, changes varchars to chars:

Code: Select all

ALTER TABLE `kb3_alliances` CHANGE `all_name` `all_name` CHAR(64) NOT NULL;
ALTER TABLE `kb3_banned_mails` CHANGE `bml_ip` `bml_ip` CHAR(32) NOT NULL;
ALTER TABLE `kb3_config` CHANGE `cfg_site` `cfg_site` CHAR(16) NOT NULL;
ALTER TABLE `kb3_config` CHANGE `cfg_key` `cfg_key` CHAR(32) NOT NULL;
ALTER TABLE `kb3_constellations` CHANGE `con_name` `con_name` CHAR(128) NOT NULL;
ALTER TABLE `kb3_contracts` CHANGE `ctr_name` `ctr_name` CHAR(128) NOT NULL;
ALTER TABLE `kb3_contracts` CHANGE `ctr_site` `ctr_site` CHAR(64) NOT NULL;
ALTER TABLE `kb3_dgmattributetypes` CHANGE `attributeName` `attributeName` CHAR(100) NOT NULL;
ALTER TABLE `kb3_dgmattributetypes` CHANGE `icon` `icon` CHAR(5) NOT NULL;
ALTER TABLE `kb3_dgmattributetypes` CHANGE `displayName` `displayName` CHAR(100) NOT NULL;
ALTER TABLE `kb3_dgmeffects` CHANGE `guid` `guid` CHAR(60);
ALTER TABLE `kb3_dgmeffects` CHANGE `displayName` `displayName` CHAR(100) NOT NULL;
ALTER TABLE `kb3_dgmeffects` CHANGE `sfxName` `sfxName` CHAR(20) NOT NULL;
ALTER TABLE `kb3_eveunits` CHANGE `unitName` `unitName` CHAR(100);
ALTER TABLE `kb3_eveunits` CHANGE `displayName` `displayName` CHAR(20);
ALTER TABLE `kb3_invtypes` CHANGE `typeName` `typeName` CHAR(100) NOT NULL;
ALTER TABLE `kb3_invtypes` CHANGE `icon` `icon` CHAR(5);
ALTER TABLE `kb3_inv_detail` CHANGE `ind_sec_status` `ind_sec_status` CHAR(5);
ALTER TABLE `kb3_ip_bans` CHANGE `ipb_ip` `ipb_ip` CHAR(32) NOT NULL;
ALTER TABLE `kb3_ip_bans` CHANGE `ipb_comment` `ipb_comment` CHAR(128) NOT NULL;
ALTER TABLE `kb3_item_locations` CHANGE `itl_location` `itl_location` CHAR(24) NOT NULL;
ALTER TABLE `kb3_item_types` CHANGE `itt_name` `itt_name` CHAR(120) NOT NULL;
ALTER TABLE `kb3_log` CHANGE `log_site` `log_site` CHAR(20) NOT NULL;
ALTER TABLE `kb3_log` CHANGE `log_ip_address` `log_ip_address` CHAR(20) NOT NULL;
ALTER TABLE `kb3_navigation` CHANGE `target` `target` CHAR(10) NOT NULL;
ALTER TABLE `kb3_navigation` CHANGE `KBSITE` `KBSITE` CHAR(16) NOT NULL;
ALTER TABLE `kb3_pilots` CHANGE `plt_name` `plt_name` CHAR(64) NOT NULL;
ALTER TABLE `kb3_races` CHANGE `rce_race` `rce_race` CHAR(30) NOT NULL;
ALTER TABLE `kb3_regions` CHANGE `reg_name` `reg_name` CHAR(64) NOT NULL;
ALTER TABLE `kb3_roles` CHANGE `rol_site` `rol_site` CHAR(16) NOT NULL;
ALTER TABLE `kb3_roles` CHANGE `rol_name` `rol_name` CHAR(200) NOT NULL;
ALTER TABLE `kb3_roles` CHANGE `rol_descr` `rol_descr` CHAR(200) NOT NULL;
ALTER TABLE `kb3_ships` CHANGE `shp_name` `shp_name` CHAR(64) NOT NULL;
ALTER TABLE `kb3_ship_classes` CHANGE `scl_class` `scl_class` CHAR(32) NOT NULL;
ALTER TABLE `kb3_standings` CHANGE `sta_comment` `sta_comment` CHAR(200) NOT NULL;
ALTER TABLE `kb3_systems` CHANGE `sys_name` `sys_name` CHAR(128) NOT NULL;
ALTER TABLE `kb3_titles` CHANGE `ttl_name` `ttl_name` CHAR(100) NOT NULL;
ALTER TABLE `kb3_titles` CHANGE `ttl_descr` `ttl_descr` CHAR(200) NOT NULL;
ALTER TABLE `kb3_titles` CHANGE `ttl_site` `ttl_site` CHAR(16) NOT NULL;
ALTER TABLE `kb3_user` CHANGE `usr_site` `usr_site` CHAR(16) NOT NULL;
ALTER TABLE `kb3_user` CHANGE `usr_login` `usr_login` CHAR(40) NOT NULL;
ALTER TABLE `kb3_user` CHANGE `usr_pass` `usr_pass` CHAR(32) NOT NULL;
ALTER TABLE `kb3_user_extra` CHANGE `use_key` `use_key` CHAR(60) NOT NULL;
ALTER TABLE `kb3_user_extra` CHANGE `use_value` `use_value` CHAR(200) NOT NULL;

[Outofmydepth]

Id love to know what you did my db is struggling a lot

[beansman]

Heh.... yeah, i sped up the queries alot, but i also broke something

I will look into remaking the whole class.killlist.php query... fuckin big one

Will post it asap.

/Beanie

[Dark. Shadowray]

+1 do the good manual for noobs like me pls 9)

[fisk]

*wants*

[Guest]

any news on this yet?

if wanted - i wouldn't mind throwing isk/$ after the betterment of the EDK board..
Sure others would to tbh, since there goes ALOT of coding into such a project.

//Karbowiak

[beansman]

Someone try this:

Code: Select all

ALTER TABLE `kb3_inv_plt` ADD INDEX ( `inp_kll_id` ) 

And tell me if it helps...

[ralle030583]

Someone try this:

Code: Select all

ALTER TABLE `kb3_inv_plt` ADD INDEX ( `inp_kll_id` ) 

And tell me if it helps...

should help made it also ages ago on eve-kill-net with my over million KM's,
thaught that index is already in core.. oO

[beansman]

I didn't have it, but i might have screwed something earlier.

This one is also worth doing

Code: Select all

ALTER TABLE `kb3_pilots` ADD INDEX ( `plt_externalid` )