Discussion between RolandoMySQLDBA and EApubs - 2012-08-27

last day (15 days later) »

5:43 PM

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...

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.

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...

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

RolandoMySQLDBA

5:43 PM

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

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';

here's the output of the first one

+--------------+-------------------------+--------+-------------+--------------+
| table_schema | table_name | engine | data_length | index_length |
+--------------+-------------------------+--------+-------------+--------------+
| ophopthaml | opuinhatko6_commentmeta | InnoDB | 1589248 | 2392064 |
| ophopthaml | opuinhatko6_comments | InnoDB | 2637824 | 819200 |
| ophopthaml | opuinhatko6_options | InnoDB | 1523712 | 16384 |
| ophopthaml | opuinhatko6_postmeta | InnoDB | 8929280 | 3162112 |

the second command :

+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

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

Do i have to worry about it?

RolandoMySQLDBA

6:00 PM

I am sorry I was gone

Next question

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

-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

no problem

take your time

RolandoMySQLDBA

6:18 PM

Please run ALTER TABLE ophopthaml.opuinhatko6_commentmeta;

then run ls -l opuinhatko6_commentmeta.ibd

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

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.

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

?

RolandoMySQLDBA

6:27 PM

You need to check for redundant indexes

Please run SHOW CREATE TABLE ophopthaml.opuinhatko6_commentmeta \G

*************************** 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

:)

RolandoMySQLDBA

The last two indexes start with the same column

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

RolandoMySQLDBA

6:29 PM

remove the meta_key index

ALTER TABLE ophopthaml.opuinhatko6_commentmeta DROP INDEX meta_key;

will it break things in wordpress? :)

RolandoMySQLDBA

No

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

?

RolandoMySQLDBA

6:32 PM

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 ???

+---------------------------------------------+--------------------+-----------------------+------------+------------+------------------+-------+-------------------------+------+---------------------------------------------------------------------------+
| Field_name | Min_value | Max_value | Min_length | Max_length | Empties_or_zeros | Nulls | Avg_value_or_avg_length | Std | Optimal_fieldtype |

RolandoMySQLDBA

OK that's not that bad

Anyway please run ls -l on ophopthaml.opuinhatko6_commentmeta again

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 ???

:) whats biginit?

RolandoMySQLDBA

6:39 PM

8 bytes

i

RolandoMySQLDBA

range from -9223372036854775808 to 9223372036854775807

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 ???

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

RolandoMySQLDBA

6:42 PM

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 ???

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

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

RolandoMySQLDBA

CREATE TABLE ophopthaml.rolando LIKE ophopthaml.opuinhatko6_commentmeta;

Query OK, 0 rows affected (0.09 sec)

RolandoMySQLDBA

6:45 PM

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

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

RolandoMySQLDBA

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

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

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

RolandoMySQLDBA

6:49 PM

???

hmmm still no difference right?

RolandoMySQLDBA

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

Then ls -l rolando.ibd

-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

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

RolandoMySQLDBA

6:54 PM

Side question : Are you in India ???

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

:)

RolandoMySQLDBA

ceylon

Old name for Sri Lanka

hehe yep :)

RolandoMySQLDBA

I am shocked with the index overhead

:( nothing's working right?

RolandoMySQLDBA

6:56 PM

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

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;

ok

done

now what shall I do? :)

RolandoMySQLDBA

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

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

before following your first post, it was 98MB

RolandoMySQLDBA

7:03 PM

This simply means all data and indexes live outside of ibdata1

ok

RolandoMySQLDBA

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

:)

RolandoMySQLDBA

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

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

or do i have to do something?

RolandoMySQLDBA

7:06 PM

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

Index overhead with UTF8 evidently cannot be compensated any further.

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

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...

OPTIMIZE TABLE?

ok thanks

RolandoMySQLDBA

7:09 PM

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

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

RolandoMySQLDBA

Yup

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

RolandoMySQLDBA

Perhaps once a month

in the crontab
0 0 * 1 * scriptname

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

RolandoMySQLDBA

7:12 PM

That's midnight on the first of the month

The script I showed you defrags only InnoDB

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

for rootpassword, can I put it in ""

no it won't work

RolandoMySQLDBA

does root have a password ???

yes or no ???

yes

it contains () which gives an error

RolandoMySQLDBA

7:20 PM

Try escaping the parentheses with a backslash \( and \)

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

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

7:38 PM

OK good night

:)

last day (15 days later) »

Transcript for

Aug '1227

Discussion between RolandoMySQLDBA an…

Imported from a comment discussion on dba.stackexchange.com/qu...

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile