Jump to content
ChrisWJ

How's YOUR database?! Healthcheck and configuration analysis

Recommended Posts

Hey guys, Another update from your friendly neighborhood LT-DBA.

 

I put together some health/config checks for the database tuning and optimization. Run these in MySQL (SQLYog/Command Line/GUI of choice)

 

None of these queries will impact performance on the database so you can run them at any time that you'd like.

 

The settings analyzed below are mostly things that should be set in your MySQL configuration file (my.ini)

 

for TX_ISOLATION.. the my.ini setting is transaction-isolation. (transaction-isolation=READ-COMMITTED)

 

Most of these settings can be changed without a restart of MySQL for example: SET GLOBAL QUERY_CACHE_SIZE=0 will update it (mind you, next time you restart anything you SET will be reverted back to it's original stat once MySQL is restarted, so Update your config file AS WELL) :geek:

 

 

Remember! Before making changes especially with indexes and things that modify the overall structure of your database take a backup!

 

Backups save jobs (trust me.. this is experience talking!) :ugeek:

 

As usual, enact the LTG disclaimer.

 

Note: A lot of the checks are going to not need to be updated on 2013. Most of them were put in to place for the first release IIRC.

 

 

-- This gives the overall queries per second. This is not a good indication of average if server uptime is less than 1 day.
SELECT sum(s1.variable_value) / s2.variable_value as QPS FROM INFORMATION_SCHEMA.GLOBAL_STATUS s1, information_schema.global_status s2 
where s1.variable_name like 'queries%' and s2.variable_name = 'uptime';

-- Uptime in minutes
SELECT s1.variable_value/60 as uptime_minutes FROM INFORMATION_SCHEMA.GLOBAL_STATUS s1 
where s1.variable_name ='uptime';

-- If you look for LATEST DETECTED DEADLOCK, this will give an indication that an old index or proc MAY need to be updated, common fixes involve FOUNDDEVICE table and COMMANDS table. If this section doesn't exist... GOOD!
show engine innodb status;

-- Query cache TYPE should be off, query cache size should be zero. TX_ISOLATION should be READ-COMMITTED, file per table should be ON, buffer pool size should be increased if there is available memory on the machine.
-- Max connections should be much higher than max_used if they are close or equal, increase max_connections, Version should be at LEAST 5.5.X, prefer 5.5.31. 
-- If version_compile_machine shows non 64 and its a 64 bit OS, mysql should be upgraded. INNODB_FLUSH_LOG_AT_TRX_COMMIT should be 0 (2 is also acceptable. 1 is not.)
select * from information_schema.global_variables where variable_name in('max_connections','INNODB_FILE_PER_TABLE','query_cache_size','query_cache_type','innodb_buffer_pool_size','tx_isolation','VERSION','version_compile_machine');
select * from information_schema.global_status where variable_name in ('max_used_connections');


-- This is the size of The labtech Database in MB (if this is smaller than innodb_buffer_pool_size that is VERY good.. means who DB can run in Memory)
SELECT sum( data_length + index_length ) / 1024 / 1024 "Data Base Size MB" 
FROM information_schema.TABLES where table_schema='labtech'; 

-- This should return zero results. If it doesn't we can clean up indexes which will clear up space and make DML statements faster. 
select table_name,column_name, count(*) from information_schema.statistics where index_schema='labtech' and seq_in_index=1 group by table_name, column_name having count(*)>1;

Edited by Guest

Share this post


Link to post
Share on other sites

Alright guys....

 

Regarding the last query.. this basically is going to tell if you have duplicated indexes on any of your tables. This causes 2 indexes to get rebuilt, when only one is required, every time in insert/update/delete happens. WHICH HAPPENS A LOT IN LABTECH. Also, there is another index that I am putting in on founddevice table that helps a TON with lock contention, thus minimizing (almost completely alleviating deadlocks completely).

 

Again, we're doing schema modification here. TAKE A BACK UP BEFOREHAND!!!!

 

First run the alter statements in indexmods.sql if you have the the table name in the results from query above showing count of 2 or greater. No need to run them all if the table name isn't showing up (it won't execute.. it will throw does not exist error) This depends on what version you are running as I have been slowly cleaning these up along the way.

 

Also, now that you have modified the schema.. if you do something and mess up the schema of your DB and you go to run CheckTables.exe to correct it, it will revert back to the old way of the not needed indexes! Drats! I have included a Dbasetable file as well that is up to date. NOTE: WHEN YOU RUN CHECK TABLES WITH NEW FILE - Some modifications are GOING TO take place. Depending on what version of LT you are on will depend on the changes. If you see something of concern - revert back if you run in to issues (remember that backup from above???) BACK UP YOUR OLD DBASETABLE.SQL FILE AS WELL BEFORE REPLACING IF REVERTING IS NEEDED.

 

I am not currently having any problems on 2012.1 but, I think I was on the last patch version of that before 2013, and I am not doing NEARLY as much with LT in my test env as anyone here most likely.

 

This is the most reason Updated DBaseTable file with all of the appropriate changes and the index mods are in the below code snippet! Enjoy :ugeek:

 

-- Remove dupe / unneeded indexes
ALTER TABLE `productkeys` DROP KEY `ClientID`;
ALTER TABLE `l_mobileandroidperms` DROP KEY `permnames`;
ALTER TABLE `VMCounter` DROP KEY `HVHId`;
ALTER TABLE `VMCounter` DROP KEY `HVMId`;
ALTER TABLE `cim_hv_Properties` DROP KEY `HVHID`;
ALTER TABLE `hvcpu` DROP KEY `HVCPUID`;
ALTER TABLE `hvdevices` DROP KEY `HVDeviceID`;
ALTER TABLE `hvhost` DROP KEY `fk_HVHost_HVMachine1`;
ALTER TABLE `hvpci` DROP KEY `HVPCIID`;
ALTER TABLE `h_agenthistory` DROP KEY `AgentID`;
ALTER TABLE `commands` DROP KEY `ComputerID`;
ALTER TABLE `runningscripts` DROP KEY `ScriptID`;

-- Change index on founddevice on pre-existing
ALTER TABLE founddevice add index `location_ip` (`LocationId`,`IpAddress`);
ALTER TABLE founddevice drop index `locationid`;

DBaseTable.zip

Share this post


Link to post
Share on other sites

Thanks a lot for putting that all together for us. I had quite a few duplicate indexes. I still have one left on degragmentation (DriveID), but was able to remove the rest thanks to your awesome information!

Share this post


Link to post
Share on other sites

Got another modification for you Geeks!

 

This will help with ticket screen load time IMMENSELY!

 

alter table ticketdata drop index `ticketid`;

alter table ticketdata drop index `datatype`;

alter table ticketdata add index `tid_datatype` (`ticketid`,`datatype`);

Share this post


Link to post
Share on other sites
Got another modification for you Geeks!

 

This will help with ticket screen load time IMMENSELY!

 

alter table ticketdata drop index `ticketid`;

alter table ticketdata drop index `datatype`;

alter table ticketdata add index `tid_datatype` (`ticketid`,`datatype`);

 

 

Chris,

 

Can this be done with 2013 w/Ignite? Does this affect anything else...reports, PSA Plugins, etc. Thanks.

 

-jeff

Share this post


Link to post
Share on other sites

Jeff - I have done it on a couple 2013 systems. There should be no impact on anything - except anything involving ticket data loading faster :)

Share this post


Link to post
Share on other sites

I finally got around to performing these updates and can say you weren't kidding on the ticket loading times! Thanks so much for posting all of this. I had several extra indexes that are now gone.

 

I do still have one listed for "extrafield". Column name: Sort; count: 2

If you have any ideas on that one, that'd be awesome, but even just what's already been done is great.

 

Thanks Chris!

Share this post


Link to post
Share on other sites
I finally got around to performing these updates and can say you weren't kidding on the ticket loading times! Thanks so much for posting all of this. I had several extra indexes that are now gone.

 

I do still have one listed for "extrafield". Column name: Sort; count: 2

If you have any ideas on that one, that'd be awesome, but even just what's already been done is great.

 

Thanks Chris!

To get the name of the indexes issue: SHOW INDEXES FROM EXTRAFIELD;

 

Here are the keys that should be on extrafield table:

 

 
PRIMARY KEY  (`ID`),
 UNIQUE KEY `Names` (`Form`,`Name`),
 KEY `Sort_idx` (`Sort`),
 KEY `Section_idx` (`Section`)

 

 

I am guessing that you have the following index named `Sort` as that one is now antiquated.

 

If that is the name you can issue the following:

 

ALTER TABLE EXTRAFIELD DROP INDEX `Sort`;

Share this post


Link to post
Share on other sites

That was it! Thanks Chris.

 

So this may be a question for a different topic, but why do these indexes get left around once they are deprecated? Obviously the new indexes are being generated by the LT upgrade scripts. I would expect those same scripts to help us "long timers" out by clearing out old, unneeded indexes like these at the same time.

 

I'm guessing there's a reason they are left and not automatically removed. But this just highlights another reason this site/forum is so needed. Without it, those of us just running right along with LT would have no idea how to fix/correct/update things like this to keep LT running optimally.

Share this post


Link to post
Share on other sites
That was it! Thanks Chris.

 

So this may be a question for a different topic, but why do these indexes get left around once they are deprecated? Obviously the new indexes are being generated by the LT upgrade scripts. I would expect those same scripts to help us "long timers" out by clearing out old, unneeded indexes like these at the same time.

 

I'm guessing there's a reason they are left and not automatically removed. But this just highlights another reason this site/forum is so needed. Without it, those of us just running right along with LT would have no idea how to fix/correct/update things like this to keep LT running optimally.

 

As of current, the reason indexes aren't being removed automatically is that if there is the slightest typo and one of the indexes get removed that are application critical, it can CRIPPLE the application in a few swift keystrokes (or mis-keystrokes). I am currently trying to make the tools available to the users and support to help identify antiquated things to clean up and then phase 2 of that is to Automate. Gotta find the problems first :ugeek:

Share this post


Link to post
Share on other sites
Note: A lot of the checks are going to not need to be updated on 2013. Most of them were put in to place for the first release IIRC.

 

Meaning when we upgrade to 2013, they should be handled for us, or if we installed 2013 NEW. . .

 

I've been on LT for about 3-4 years now and within the last year upgraded mySQL to 64 bit and moved to 2013 quite some time ago. Many of my settings are wrong with what you have below (TX Isolation, query cache type, file per table. . .) Also have 13 dupe indexes and version compile machine shows x86 - nothing about 64 bit. Weird on that one.

Share this post


Link to post
Share on other sites

I cleaned up all my dupe indexes, but still have two hanging around.

 

hotfix -> hotfixid

hvcounter -> hvhid

 

SHOW INDEXES FROM HOTFIX reveals HotFixID is in two columns (Primary key and HotFixIndex)

 

SHOW INDEXES FROM HVCOUNTER reveals HVHID is in two columns (Primary key and HVHID)

 

Looking at the DBaseTable.zip - do I have the correct lines to clean these up?

 

ALTER TABLE `hotfix` DROP KEY `HotFixIndex`;

ALTER TABLE `hvcounter` DROP KEY `HVHId`;

Share this post


Link to post
Share on other sites

For some reason, some installs are only showing x86 and they are 64bbit. Not sure on that one.

 

As for the alter table drop key statements, yes, those would be correct and those aren't indexes I've even come across yet.Those are for sure old! Go ahead and remove.

Share this post


Link to post
Share on other sites
For some reason, some installs are only showing x86 and they are 64bbit. Not sure on that one.

 

As for the alter table drop key statements, yes, those would be correct and those aren't indexes I've even come across yet.Those are for sure old! Go ahead and remove.

 

I read later on about a possible bug in some mySQL versions that talk about showing 64bit vs x86. So looks like a non-issue.

 

Thanks for the verification on the SQL statements. We've been running LT since 2010, so I think we're considered old fogies and get to see weird stuff in our database sometimes! :D

Share this post


Link to post
Share on other sites

Anyone know if there's a way to do a SQL statement right in a monitor? Then we could monitor for the dupe index issue. I know I could script it and run it against the server daily, but always seems cludgy to me.

Share this post


Link to post
Share on other sites

In regards to the final query in your original post:

 

> select table_name,column_name, count(*) from information_schema.statistics where index_schema='labtech' and seq_in_index=1 group by table_name, column_name having count(*)>1;

 

I suppose 2013.1 update doesn't remove those unneeded indexes? Any knowledge of whether or not these are planned to be included later?

Share this post


Link to post
Share on other sites

We show the following tables having duplicate indexes using the check on the first post.

 

cim_hv_properties HVHID 2

commands ComputerID 2

extrafield Sort 2

founddevice LocationId 2

hvcpu HVCPUID 2

hvdevices HVDeviceID 2

hvhost HVHID 2

hvpci HVPCIID 2

h_agenthistory AgentID 2

localizationresources Category 2

ltantivirusthreats CompID 2

l_mobileandroidperms PermissionName 2

productkeys ClientID 2

runningscripts ScriptID 2

vmcounter HVHId 3

Share this post


Link to post
Share on other sites

does this still apply to LT 10.5?

 

I have the following:

 

table_name column name count(*)

localization resources category 2

plugin_veeam_lbp_job id 2

plugin_veeam_lbp_license server_id 2

 

do i just alter the table using the column names, or do I need to investigate with show indexes before altering?

plugin_veeam_lbp_vms id 2

Share this post


Link to post
Share on other sites

I was able to cleanup the columns as described above, except for the localizationresources table.

 

Here's what the query for duplicates returned:

 

table_name                column_name     count(*)
localizationresources      Category          2

 

So, I ran SHOW INDEXES FROM localizationresources; which yielded the following:

 

Table                  Non_unique  Key_Name      Seq_in_index  Column_name  Collation   Cardinality
localizationresources       0      PRIMARY             1       Category         A       17
localizationresources       0      PRIMARY             2       Key              A       625
localizationresources       0      PRIMARY             3       CultureCode      A       625
localizationresources       1      Category_Key        1       Category         A       6
localizationresources       1      Category_Key        2       Key              A       625

 

For good measure, here is the table:

 

Category      Key       CultureCode     Value     GUID
AgentTray     About     en-US           About     9f4a167e-c5b3-11e3-891d-080027cc050e

 

So, as someone who knows nothing about diddly squat, what is it that I need to drop? :?

Share this post


Link to post
Share on other sites

I think that's a question for LabTech Support. For serious. "Which table do I drop?" makes me wanna close this window and walk the heck away, and I suspect it would give any second-tier support tech an automatic hernia as well. :-)

Share this post


Link to post
Share on other sites

Query to fix localizationresources duplicate indexes.

 

ALTER TABLE `labtech`.`localizationresources`
DROP INDEX `Category_Key`

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×