Optimizing a big 6.5 GB mysql database

Hi Everyone, Calling out the DBAs and Experienced SysAdmins here for some suggestions. In Continuation to my thread here Suggest: VPS for 6.5GB Database/Php app - High availability , I'm preparing further actions. The client has given me a sample DB set so I can see if there is a scope of improvement and squeezing its size.

This Set is **4.1GB **imported as seen in PHPMyadmin (PMA).

I'm naive on DB Administration, I did the basic things I knew and read about:

1. mysqlcheck --all-databases


2. mysqlcheck --all-databases -o


3. mysqlcheck --all-databases --auto-repair -u root -p


4. mysqlcheck --all-databases --analyze -u root -p

But the database size remains intact at 4.1GB though I was expecting it to drop a little bit. Does this mean the tables are OK already?

Take a look at the DB here:

Importantly... I noticed this Database uses mixed MyISAM & InnoDB tables, which was quite a frequent practice (atleast 3-4 years back). Is it OK to use them?. If you see the top four tables that are the bulkiest, one is inno, next one myISAM, and alternating.

member_data-> InnoDB-> 1 GiB

zabby_rolo-> MyISAM-> 900 MiB

treeloger_orders-> InnoDB-> 549.2 MiB

bs_newsletters_view-> MyISAM-> 467.7 MiB

...

My question is, should such tables be all innodb? will it give me good throughput?

P.S. the production server will run Mariadb 5.5 or Percona 5.5 The FrontEnd is a CakePHP 1.x App

Can you suggest any tools I can use to further analyze tables and chop out any unnecessary bulk out of them ? It'd help me as I plan to replicate it at 30 mins delay with a secondary fail over server across a different DC.

Any other valuable suggestions are duly welcome...

Thanks for reading this length... Looking forward for your answers...!

http://ift.tt/1dvHQ0Z