How do MyISAM and InnoDB Utilize HD Space?

Question

im getting these Errors from W3C

Line 41, Column 143: Stray end tag body.

…t1_02.gif','images/Part1_03.gif','images/Part1_04.gif','images/Part1_05.png')"> ✉ Line 41, Column 143: An body start tag seen but an element of the same type was already open.

…t1_02.gif','images/Part1_03.gif','images/Part1_04.gif','images/Part1_05.png')"> ✉ Line 41, Column 143: Cannot recover after last error. Any further errors will be ignored.

…t1_02.gif','images/Part1_03.gif','images/Part1_04.gif','images/Part1_05.png')">

How can i fix that, And my code is:

http://pastebin.com/g11fz1qP

Solution 1

MySQL use of DiskSpace is quite predictable.

The information_schema can quickly give away how much space is used by both storage engines. However, it is far better to configure InnoDB with innodb_file_per_table. That way, you can micromanage the diskspace of individual InnoDB tables. If you do not have innodb_file_per_table, the ibdata1 will grow and NEVER, EVER SHRINK.

I wrote nice articles about Cleaning Up InnoDB once and for all.

• MySQL InnoDB - innodb_file_per_table cons?
• https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261

As for MyISAM, you need to periodically run one of the following:

• OPTIMIZE TABLE myisam-tablename;
• ALTER TABLE myisam-tablename ENGINE=MyISAM; ANALYZE TABLE myisam-tablename;

These will compress MyISAM so that there are not unused data and index pages in the MyISAM table components (.MYD and .MYI)

You can manually monitor the disk space usage with this query:

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 3 pw) A ORDER BY TSize;

This will tell how much space is occupied by data and index for engine storage engine. Notice at the end of the query the clause: (SELECT 3 pw). Change the number to generate the report in different units

(SELECT 0 pw) for Bytes
(SELECT 1 pw) for KiloBytes
(SELECT 2 pw) for MegaBytes
(SELECT 3 pw) for GigaBytes
(SELECT 4 pw) for TeraBytes
(SELECT 5 pw) for PetaBytes (Write me if you every get numbers this high)

UPDATE 2012-05-26 22:29 EDT

When it comes to InnoDB, if you use innodb_file_per_table, you may find that there is a difference between the file size of the .ibd file and the sum of data_length + index_length.

For an InnoDB table mydb.mytable, here is the comparison you should make:

• Get the size of file by running ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print %5}'
• Get the size of the table from the information schemna's point of view: SELECT data_length+index_length FROM information_schema.tables WHERE table_schema='mydb' AND table_name='mytable';
• If the filesize > (data_length+index_length) * 1.1, then you should drag the table like this: ALTER TABLE mydb.mytable ENGINE=InnoDB;

This will make a temp table, copy only real data pages and index pages into the temp table, delete the original, and rename the temp table back to mydb.mytable. Instant table compression with one command. Please plan all table compressions during off hours.

Solution 2

MyISAM allocates to disk reflecting the actual space utilized. If you are not using foreign keys, transactions, or any other feature unique to InnoDB, you could convert to MyISAM easily. InnoDB is quicker for writing and MyISAM is quicker for reading. Ultimately, I would recommend researching the differences between the engines in detail before arbitrarily changing storage engines.

Here is a procedure I have used for freeing up InnoDB tablespace in the past:

Deleting huge chunks of data from mysql innodb

Comments

[John Rocha]

@RolandoMySQLDBA. I've found that the data size, index size and free space columns don't always match the size of the table's .idb file. I've found the sum of these columns is almost always smaller than the the actual .idb file -- up to 33% smaller. Is there another column that I'm missing? Looking at your query above I see that you're only using data size and index size.

[RolandoMySQLDBA]

If data_length+index_length is significantly less than the actual .ibd file, this makes the .ibd file the perfect candidate for defragmentation. I add that to my answer.

[RolandoMySQLDBA]

I just updated my answer !!!

[Thrillofit]

I have done it and it solved the first error with Stray, Now i have the two other left; (Line 41, Column 143: An body start tag seen but an element of the same type was already open. And Line 41, Column 143: Cannot recover after last error. Any further errors will be ignored.) This is the code what have the problem. <body onLoad="MM_preloadImages('images/Part1_01.gif','images/Part1‌​_02.gif','images/Par‌​t1_03.gif','images/P‌​art1_04.gif','images‌​/Part1_05.png')">

[Code Lღver]

you have used the <body> tag two time. on one page you can use only one time <body> tag. remove the first <body> tag and the end tag of the </body> that you have used before the <body onLoad ....>.

[Thrillofit]

I Have removed now both, <body> and </body>, but still getting the same problem. pastebin.com/JhUPJZ5k

[Code Lღver]

I think you are newbie in HTML. well no problem. put your script in head tag and close the body tag before the closing of html tag means: </body></html>. this will be at the bottom.

[Thrillofit]

I have just started to learn about HTML, so yeah, im kinda newbie hehe :P Anyways, i guess i have done it right (pastebin.com/ethUxZdn ) <--- Update#2 but it still comes the same problem,

[Code Lღver]

now this is my last effort and this will give you surely proper solution.

[Code Lღver]

you have not define the function MM_preloadImages . you should also learn the function(OOPS) to learn the function. in javascript pass the arguments as given in onload function.

[Thrillofit]

I still dont understand what you mean :/ But what can i do, i mean what should i change in the code to make it work? Anyways i have also done a another new docuemnt with that script and bodyonLoad and it works but i dont have any input text, So i dont know, Can it cause it?

[Thrillofit]

Nevermind! Got it worked somehow, i dont even know what i did but it works!

[NaturalBornCamper]

I ran both OPTIMIZE and ANALYZE on a 849mb table and it jumped to 894mb after OPTIMIZE.. seems like this didn't work at all and made it worse