Discussion between RolandoMySQLDBA and EApubs

Stack Exchange

17:43

1

A: How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed

Ouch you have innodb_file_per_table off I wrote articles on this before. You will have to convert the InnoDB infrastructure. Oct 29, 2010 : My Original Post in StackOverflow Apr 01, 2012 : Is innodb_file_per_table advisable? Mar 25, 2012 : Why does InnoDB store all databases in one file? Feb 0...

EApubs

Do I have to drop even the performance_schema and the mysql databases?

RolandoMySQLDBA

You do not have to drop performance_schema and the mysql databases. In fact, please do not touch the mysql schema.

EApubs

Perfect thanks a lot... Im going to clean it now :)

Performed the cleanup and it did a big change :) But still, mysqltuner show 5 more fragmented tables. How to check this?

RolandoMySQLDBA

Updated my answer...

EApubs

The first table I tested have 1.89% fragmentation. But OPTIMIZE TABLE won't reduce it

RolandoMySQLDBA

17:43

What is the name of that table and what database is it in ???

EApubs

wp_commentmeta ... The 5 tables with fragmentation are : wp_commentmeta wp_comments wp_options wp_postmeta and wp_posts (Its a wordpress site)

hi, first, thanks for the help :)

RolandoMySQLDBA

Please run this

select table_schema,table_name,engine,data_length,index_length
from information_schema.tables WHERE table_name IN
('wp_commentmeta','wp_comments',
'wp_options','wp_postmeta','wp_posts');

Run this as well : SHOW VARIABLES LIKE 'datadir';

EApubs

here's the output of the first one

the second command :

there are more tables in this database and those tables are good

Do i have to worry about it?

RolandoMySQLDBA

18:00

I am sorry I was gone

Next question

EApubs

no problem :)

RolandoMySQLDBA

Run this in Linux please

cd /var/lib/mysql/ophopthaml
ls -l opuinhatko6_commentmeta.ibd opuinhatko6_comments.ibd opuinhatko6_options.ibd opuinhatko6_postmeta.ibd opuinhatko6_posts.ibd

EApubs

-rw-rw---- 1 mysql mysql 11534336 Aug 27 22:24 opuinhatko6_commentmeta.ibd
-rw-rw---- 1 mysql mysql 11534336 Aug 27 22:24 opuinhatko6_comments.ibd
-rw-rw---- 1 mysql mysql 9437184 Aug 27 23:34 opuinhatko6_options.ibd
-rw-rw---- 1 mysql mysql 19922944 Aug 27 23:33 opuinhatko6_postmeta.ibd
-rw-rw---- 1 mysql mysql 19922944 Aug 27 22:29 opuinhatko6_posts.ibd

top

sorry... top is not for you

RolandoMySQLDBA

BRB at work

EApubs

no problem

take your time

RolandoMySQLDBA

18:18

Please run ALTER TABLE ophopthaml.opuinhatko6_commentmeta;

then run ls -l opuinhatko6_commentmeta.ibd

EApubs

Query OK, 0 rows affected (0.00 sec)

-rw-rw---- 1 mysql mysql 11534336 Aug 27 22:24 opuinhatko6_commentmeta.ibd

RolandoMySQLDBA

Sorry ALTER TABLE ophopthaml.opuinhatko6_commentmeta ENGINE=InnoDB;

I know it is already InnoDB. Pleas run it and show the ls -l again

EApubs

Query OK, 11092 rows affected (0.97 sec)
Records: 11092 Duplicates: 0 Warnings: 0

-rw-rw---- 1 mysql mysql 11534336 Aug 27 23:53 opuinhatko6_commentmeta.ibd

still no difference :(

RolandoMySQLDBA

Here is my guess: You must have a lot of index overhead because if you look back at the SELECT query from before, it shows that the index_length for that table is bigger than the data_length.

EApubs

so is this normal for a wordpress site? is there a way to fix it

?

RolandoMySQLDBA

18:27

You need to check for redundant indexes

Please run SHOW CREATE TABLE ophopthaml.opuinhatko6_commentmeta \G

EApubs

*************************** 1. row ***************************
Table: opuinhatko6_commentmeta
Create Table: CREATE TABLE `opuinhatko6_commentmeta` (
`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`comment_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`meta_key` varchar(255) DEFAULT NULL,
`meta_value` longtext,
PRIMARY KEY (`meta_id`),
KEY `comment_id` (`comment_id`),
KEY `meta_key` (`meta_key`),
KEY `disqus_dupecheck` (`meta_key`,`meta_value`(11))
) ENGINE=InnoDB AUTO_INCREMENT=27947 DEFAULT CHARSET=utf8

RolandoMySQLDBA

BINGO

You have a redundant index

EApubs

:)

RolandoMySQLDBA

The last two indexes start with the same column

EApubs

what is it? (please note that im no mysql expert :) )

RolandoMySQLDBA

18:29

remove the meta_key index

ALTER TABLE ophopthaml.opuinhatko6_commentmeta DROP INDEX meta_key;

EApubs

will it break things in wordpress? :)

RolandoMySQLDBA

No

EApubs

Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

RolandoMySQLDBA

The MySQL Query Optimizer will simply use the disqus_dupecheck index when search by meta_key

I also noticed another problem

You are indexing on a huge column

EApubs

?

RolandoMySQLDBA

18:32

There are probably very long columns values in meta_key. That will bloat any index in any RDBMS not just MySQL

Run this query

SELECT meta_key FROM ophopthaml.opuinhatko6_commentmeta PROCEDURE ANALYSE();

What does it report ???

EApubs

RolandoMySQLDBA

OK that's not that bad

Anyway please run ls -l on ophopthaml.opuinhatko6_commentmeta again

EApubs

exit

sorry :)

-rw-rw---- 1 mysql mysql 11534336 Aug 28 00:03 opuinhatko6_commentmeta.ibd

RolandoMySQLDBA

Here is something else to consider : Do you have to use bigint ???

EApubs

:) whats biginit?

RolandoMySQLDBA

18:39

8 bytes

EApubs

i

RolandoMySQLDBA

range from -9223372036854775808 to 9223372036854775807

EApubs

as a data type in the table structure right?

RolandoMySQLDBA

Would you be able to live with a smaller int value whose range is -2147483648 to 2147483647 ???

EApubs

yep... biginit is there in all those tables and even in some other tables which don't have fragmentations

RolandoMySQLDBA

18:42

Let's do an experiment : Let's make a copy of the table and convert BIGINT into INT just to see if it shrinks the ibd file of the copied table. Wanna try ???

EApubs

no idea... wordpress created those values by default :(

:) is it safe?

RolandoMySQLDBA

Yes because we'll be making a copy rather than altering the real table

EApubs

ok please give me the commands starting from making a copy :)

RolandoMySQLDBA

CREATE TABLE ophopthaml.rolando LIKE ophopthaml.opuinhatko6_commentmeta;

EApubs

Query OK, 0 rows affected (0.09 sec)

RolandoMySQLDBA

18:45

ALTER TABLE ophopthaml.rolando MODIFY COLUMN meta_id INT(11);

EApubs

Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

RolandoMySQLDBA

INSERT INTO ophopthaml.rolando SELECT * FROM ophopthaml.opuinhatko6_commentmeta;

EApubs

i think it didn't copied the data to the new table only the structure

ah

:)

Query OK, 11092 rows affected (1.02 sec)
Records: 11092 Duplicates: 0 Warnings: 0

RolandoMySQLDBA

ls -l rolando.ibd

EApubs

-rw-rw---- 1 mysql mysql 11534336 Aug 28 00:17 rolando.ibd

RolandoMySQLDBA

18:49

???

EApubs

hmmm still no difference right?

RolandoMySQLDBA

Try this next : ALTER TABLE ophopthaml.rolando MODIFY COLUMN comment_id INT(11);

Then ls -l rolando.ibd

EApubs

-rw-rw---- 1 mysql mysql 11534336 Aug 28 00:21 rolando.ibd

same thing :(

RolandoMySQLDBA

ALTER TABLE ophopthaml.rolando DROP INDEX disqus_dupecheck;

ALTER TABLE ophopthaml.rolando ADD INDEX (meta_key);

ls -l rolando.ibd

EApubs

-rw-rw---- 1 mysql mysql 11534336 Aug 28 00:23 rolando.ibd

RolandoMySQLDBA

18:54

Side question : Are you in India ???

EApubs

in sri lanka... right below india.. same timezone

:)

RolandoMySQLDBA

ceylon

Old name for Sri Lanka

EApubs

hehe yep :)

RolandoMySQLDBA

I am shocked with the index overhead

EApubs

:( nothing's working right?

RolandoMySQLDBA

18:56

It must be the UTF8 since characters are 3 bytes each.

EApubs

i think there's something else which effect 5 of those tables

first, it told me there are 14 fragmented files

after recreating this by following your post, it came to 5

is this a serious issue?

RolandoMySQLDBA

It's not that serious with a small WordPress site. In fact, as content grows, the table may grow very little.

You can drop the rolando table

DROP TABLE rolando;

EApubs

ok

done

now what shall I do? :)

RolandoMySQLDBA

Did the ibdata1 file shrink ??? Run ls- l /var/lib/mysql/ibdata1 please

EApubs

-rw-rw---- 1 mysql mysql 18874368 Aug 28 00:31 /var/lib/mysql/ibdata1

before following your first post, it was 98MB

RolandoMySQLDBA

19:03

This simply means all data and indexes live outside of ibdata1

EApubs

ok

RolandoMySQLDBA

Here is what ibdata1 and the InnoDB Buffer Pool look like : scribd.com/doc/31337494/XtraDB-InnoDB-internals-in-drawing

EApubs

:)

RolandoMySQLDBA

Doing the InnoDB Cleanup significantly shrunk ibdata1. It should grow very slowly from here

EApubs

ok... so shall i forget about those 5 tables?

or do i have to do something?

RolandoMySQLDBA

19:06

Yes you can ignore. We've done all we could.

Index overhead with UTF8 evidently cannot be compensated any further.

EApubs

ok thanks a lot my friend :) one more question.... Now to optimize the tables, can I use the normal OPTIMIZE TABLE command? How about REPAIR TABLE?

RolandoMySQLDBA

REPAIR TABLE only works on MyISAM

EApubs

no need for innodb?

RolandoMySQLDBA

As for OPTIMIZE TABLE, I have a script for you to mass defrag all the InnoDB Tables. Let me get it...

EApubs

OPTIMIZE TABLE?

ok thanks

RolandoMySQLDBA

19:09

1

A: Deleting Entries from Table not reducing my DB file

If you would like to mass compress all InnoDB tables please run the following: MYSQL_USER=root MYSQL_PASS=password MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}" echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql mysql ${MYSQL_CONN} -A --skip-column-names -e"SELECT CONCAT('ALTER TAB...

The script is in my earlier post from July 27, 2012

EApubs

i have to put it in a bash script and run?

RolandoMySQLDBA

Yup

EApubs

perfect... how often do I need to run it?

RolandoMySQLDBA

Perhaps once a month

in the crontab
0 0 * 1 * scriptname

EApubs

i currently have two databases ... one have mysami and the other is innodb... is it safe to run the scrpt?

RolandoMySQLDBA

19:12

That's midnight on the first of the month

The script I showed you defrags only InnoDB

EApubs

and ignores the others?

RolandoMySQLDBA

This is done all MyISAM : dba.stackexchange.com/questions/21406/…

Here is a combined script

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';') "
SQL="${SQL} FROM information_schema.tables "
SQL="${SQL} WHERE table_schema NOT IN "
SQL="${SQL} ('information_schema','performance_schema','mysql')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > GlobalOptmizeTable.sql
mysql ${MYSQL_CONN} < GlobalOptmizeTable.sql

This will run OPTIMIZE TABLE on all your tables regardless of whether it is MyISAM or InnoDB

EApubs

for rootpassword, can I put it in ""

no it won't work

RolandoMySQLDBA

does root have a password ???

yes or no ???

EApubs

yes

it contains () which gives an error

RolandoMySQLDBA

19:20

Try escaping the parentheses with a backslash $ and $

EApubs

the script asks for a password

then says :: ERROR 1049 (42000): Unknown database 'P37-34Q4g95287L9=5q6d413K85dUH'

RolandoMySQLDBA

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p\"${MYSQL_PASS}\""
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';') "
SQL="${SQL} FROM information_schema.tables "
SQL="${SQL} WHERE table_schema NOT IN "
SQL="${SQL} ('information_schema','performance_schema','mysql')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > GlobalOptmizeTable.sql
mysql ${MYSQL_CONN} < GlobalOptmizeTable.sql

Try it again with the new line3

EApubs

it works!

thanks a lot my friend!

thanks a lot for your help and support :)

you saved me :) thanks again!

now im going down for the night.... good night my friend! :D thanks again! :)

RolandoMySQLDBA

19:38

OK good night

EApubs

:)

Transcript for

Discussion between RolandoMySQLDBA an…