REQUEST: Optimize Table SQL Query

[Chrysm]

It might be beneficial to add an optmize table sql query for zones that go back into sleep mode after someone was in it. I don't know how much of a difference it will make but, I know on my web server, the forums need to be optimized at least once a moth to reduce size and to stop errors. If I don't do it, errors will definatly show up sooner or later.

Comments? Flames?

[arkaria]

Hmmm interesting idea. Shouldn't really be too much of an issue I would think for eqemu as most of the tables are not being modified all the time. So should not be error prone.

Am I wrong?

Am I wrong?

You're right, an optimize may be worth it right after the db is sourced in, but if it is not modified much afterwards I don't think there is much to be gained by this.

[Chrysm]

I was thinking along the lines for those servers that have faction working as well as aa etc. The optimize would be for the user accounts and for the characters. When you kill something and get the faction hit or when you add that aa point or loot some plat it all wrties to the database

[arkaria]

Yes but that only really appllys to like 3 or 4 tables. And it's not doing lots of inserts and deletes that will "fragment" the DB so to speak. Most of those changes will be updates to single records I think.

[Chrysm]

Ok =) was a thought =)

[arkaria]

Was a good thought. And maybe I'm wrong and it would improve DB performace. I'm don't have alot of expirience tuning MySQL.

When you kill something and get the faction hit or when you add that aa point or loot some plat it all wrties to the database

From mysql.com

OPTIMIZE TABLE works the following way:

If the table has deleted or split rows, repair the table.
If the index pages are not sorted, sort them.
If the statistics are not up to date (and the repair couldn't be done by sorting the index), update them.

If you are adding/deleting rows or changing indexed fields then this might have some value, but I think the cases you quote are changing non-indexed fields in the DB, so optimize table wouldn't have much effect. Having said this, I am no mysql expert, so I may be wrong.

Just for fun I optimized my tables, launch mysql, use <your db>, and copy and paste this into a file and source it in:

OPTIMIZE TABLE aa_timers ;
OPTIMIZE TABLE account ;
OPTIMIZE TABLE altadv_vars ;
OPTIMIZE TABLE books ;
OPTIMIZE TABLE character_ ;
OPTIMIZE TABLE character_backup ;
OPTIMIZE TABLE city_defense ;
OPTIMIZE TABLE class_skill ;
OPTIMIZE TABLE doors ;
OPTIMIZE TABLE eventlog ;
OPTIMIZE TABLE faction_list ;
OPTIMIZE TABLE faction_values ;
OPTIMIZE TABLE forage ;
OPTIMIZE TABLE grid ;
OPTIMIZE TABLE guild_alliances ;
OPTIMIZE TABLE guild_controllers ;
OPTIMIZE TABLE guilds ;
OPTIMIZE TABLE hackers ;
OPTIMIZE TABLE inventory ;
OPTIMIZE TABLE items ;
OPTIMIZE TABLE lootdrop ;
OPTIMIZE TABLE lootdrop_entries ;
OPTIMIZE TABLE loottable ;
OPTIMIZE TABLE loottable_entries ;
OPTIMIZE TABLE merchantlist ;
OPTIMIZE TABLE name_filter ;
OPTIMIZE TABLE npc_faction ;
OPTIMIZE TABLE npc_faction_entries ;
OPTIMIZE TABLE npc_spells ;
OPTIMIZE TABLE npc_spells_entries ;
OPTIMIZE TABLE npc_types ;
OPTIMIZE TABLE object ;
OPTIMIZE TABLE object_contents ;
OPTIMIZE TABLE petitions ;
OPTIMIZE TABLE pets ;
OPTIMIZE TABLE player_corpses ;
OPTIMIZE TABLE player_corpses_backup ;
OPTIMIZE TABLE sharedbank ;
OPTIMIZE TABLE spawn2 ;
OPTIMIZE TABLE spawnentry ;
OPTIMIZE TABLE spawngroup ;
OPTIMIZE TABLE start_zones ;
OPTIMIZE TABLE starting_items ;
OPTIMIZE TABLE trader ;
OPTIMIZE TABLE tradeskillrecipe ;
OPTIMIZE TABLE variables ;
OPTIMIZE TABLE zone ;
OPTIMIZE TABLE zone_points ;
OPTIMIZE TABLE zone_server ;
OPTIMIZE TABLE zone_state_dump ;
OPTIMIZE TABLE zonepoints_raw ;
OPTIMIZE TABLE zoneserver_auth ;

You could incorporate this into your server startup if you wanted.

[Chrysm]

If you are using windows that MySQL program, MySQL Control Center has a right click context. Right click on tables and select tools and click optimize. You don't have to do it for each table just the unexpanded table list and it will do all of them and show you what the output was. If you use phpmyadmin the function is also there and when you refresh your table after optimization (if it did anything) your table should be reduced in size

[Monrezz]

Is it possible to get database.cpp to run a query that optimizes all mysql tables on startup, which is called in world\net.cpp?

Surely it wouldn't hurt performance, maybe take a few seconds longer to bootup, but once booted it would be fine...?

[Shawn319]

MySQL-Front 2.5 does a good job at table diagnostics. personally, every time my server is down i do a complete Check, analyze, and optimization.