Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


Optimizing a big 6.5 GB mysql database
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

Optimizing a big 6.5 GB mysql database

mehargagsmehargags Member
edited January 2015 in Help

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

«1

Comments

  • You sure you don't want to blank out/obscure the DB names and just have the sizes?

  • You sell it? :)

  • I read zabby_rolo as zabby_yolo

  • Not sure what you are looking for specifically. DB optimization is a very specific and specialized situation. To answer your question, having a mixed set of innodb and myisam is no real issue. It will depend on the information being stored and how you are accessing it to really answer that question.

    Are you just cleaning up the database? If so, you have done all that can be. Or optimizing front end and DB? That is a bit more involved, lol I kid, a bit. If the latter, look into Database Normalization, http://en.wikipedia.org/wiki/Database_normalization

  • mehargagsmehargags Member
    edited January 2015

    @0xdragon the Table names are already changed...Don't worry
    @BuyAds ... LOL ... Sorry Not for SALE!

  • mehargagsmehargags Member
    edited January 2015

    Thanks @MCHPhil for pointing... will surely read.

    May I request @Microlinux, @Maounique & @bsdguy to shed some insight here too, don't mean to hijack any other threads :)

  • Uhm, why is a size of 4.something GB a question at all? (seriously and friendly asking, not cynical).
    That's small cake. And quite irrelevant for performance, too.

    And: What are your priorities? Performance? Reliability? How complex is the DB?

  • @bsdguy,
    No... it is not a question or a concern, but handling WP DBs all this time, I'm handling such a DB size for the first time, and so just collecting my knowledge for the "first time". you know how it feels~!

    Priorities?? as in? please elaborate.
    Performance, yes I want it perform without any hiccups, when replicating to second server, it should be fast!
    Complexity, I can't really comment, but If you can ask some specific questions, I can have the developer answer them.

    thanks...

  • @mehargags said:
    May I request Microlinux, Maounique & bsdguy to shed some insight here too, don't mean to hijack any other threads :)

    Well, the first insight is that most who worry about optimization do not need it and that many who do need it, do not worry or care ;)

    No need to ask your developers. Those are questions for the architect/(tech)project manager.
    Seriously, all that is an architectural design question. I've seen enough cases where simple bonding and/or a new, better switch did far more for application performance than any DB fiddling could have done.
    You should follow a clear line with your questions and start at the right end, namely, the front-end. So: How many requests/s? Now and in reasonably expected future?
    Depending on that and the hardware the site is on, you may or may not decide for a dedicated DB server (and the DB, and the strategy (e.g. caching write through)).

    I like the following picture as advice: A (non trivial) web site is a like piece of music. You don't need the most powerfull trumpet or the highest tones flute - you need a well balanced adequate orchestra ;)

    Thanked by 1mehargags
  • Go to https://github.com/mustafaramadhan/kloxo and find out mysql-convert.php. You can use this file for convert from current storage-engine to another.

    Thanked by 1mehargags
  • @mehargags said:
    May I request Microlinux, Maounique & bsdguy to shed some insight here too, don't mean to hijack any other threads :)

    Sorry, I am not much of a MySQL admin.

    Thanked by 1mehargags
  • said: 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.

    This depends entirely how you're using the data. Selecting or updating content? View this for a full run down.

    http://stackoverflow.com/questions/12614541/whats-the-difference-between-myisam-and-innodb

    Also, use this. It's a Godsend.
    http://mysqltuner.com/

    Hope this helps.

    Thanked by 1netomx
  • you may need to have specific problem first before trying to find a solution. find which part of the application is slow and which queries are slow. then tune from there. if there is no performance problem with the application, then why not just sit back and relax.

  • jarjar Patron Provider, Top Host, Veteran

    @mustafaramadhan said:
    Go to https://github.com/mustafaramadhan/kloxo and find out mysql-convert.php. You can use this file for convert from current storage-engine to another.

    It's really just one command by itself.

    http://dev.mysql.com/doc/refman/5.6/en/converting-tables-to-innodb.html

  • jarjar Patron Provider, Top Host, Veteran
    edited January 2015

    @Dillybob said:
    Also, use this. It's a Godsend. http://mysqltuner.com/

    Please do not use this blindly. If you don't understand its recommendations then you cannot take them at face value. There is no automated way to optimize MySQL efficiently, this script is a product of someone's refusal to accept this.

    It can, however, offer some insights. Those insights, however, can be had just as easily by knowing your application and how it uses your database.

    You will never learn more about your usage than you will from this command:

    watch 'mysqladmin processlist'

  • Here's another bit of advice:

    While it seems natural to many (most) to associate SQL with MySql, this is actually not really a natural preselection. Often other DBs like PostgreSQL or even SQLite (don't underestimate that thingy!) might actually be more adequate choices. In certain cases a classical (non-SQL) database might also be an attractive option (if, for instance, there are rather few tables but an urgent need for extreme speed). Or combinations.
    Again: Analyze your scenario well and discuss with the architect.

    Or, again: Don't care batsh-t (and concentrate on other bottlenecks (like lousy PHP code)). That's actually reasonable advice for about 80% - 90% of the web sites out there.

    Thanked by 1jar
  • For newbie (as Kloxo/Kloxo-MR user), more simple running 'sh /script/mysq-convert --engine=innodb' rather follow reference from http://dev.mysql.com/doc/refman/5.6/en/converting-tables-to-innodb.html

  • jarjar Patron Provider, Top Host, Veteran
    edited January 2015

    @mustafaramadhan said:
    more simple running 'sh /script/mysq-convert --engine=innodb'

    Not really any easier than running this:

    ALTER TABLE table_name ENGINE=InnoDB;

    Except that then you have to download something to do it.

    Thanked by 2M66B Spencer
  • Jar said: Except that then you have to download something to do it.

    LOL

  • That's not clear what you want. Mentioned commands will not make your database less. And i bet nothing will make it less except two things 1) Compression (very crazy way with InnoDB and MyISAM). 2) Manually detect and remove unused/old rows.
    If you are searching for sysadmin to manage this db - feel free to pm... I am able to manage this and 100x times bigger databases staying it highly available.

  • @Jar said:
    Except that then you have to download something to do it.

    I know above code and my script based on it. Different is my script with many options.

    For execute 'ALTER TABLE table_name ENGINE=InnoDB;', you must know database and table name. Also must know root password.

  • I did run a quite a lot larger DB (~80GB).

    Heavy improvements were caused by using Sphinx for searches (might not be possible if you can't edit code in the web/pc application) and by switching to a pure SSD array.

  • Sorry to disappoint but contrary to almost religious internet belief in most cases the contribution SSDs can offer are insignificant.
    Don't get me wrong. SSDs can be terribly fast. But serving web pages is akin to a relay race in way. And connecting the pieces is very important though often overlooked.

    And again RAM. RAM again. And RAM once more. Why? Because a DB job consists of 3 parts. SQL parsing and translation (Keep in mind that there are not really SQL databases. SQL is but a front-end to a DB). Next - and typically the intense part - working through indices and finally some simple disk reads (usually cheap). As the records are usually very considerably larger than the indices and as the latter are the most worked on pretty every sensible DB system keeps indices in RAM - if there's enough, that is.

    Another classical culprit is dealing with many connections (between e.g. web app and DB). You would be surprised how many applications stupidly use slow TCP/IP sockets to connect to a DB server running one the same machine (very typical scenario with PHP/MySql users). Running each session in its own process (friendly greetings to many, many AMP servers ...) isn't helping the desire for speed either; and again SSDs won't change much there.

    Finally and usually most importantly PHP code is the worst offender (in terms of speed). And again SSDs don't help much.

    In a typical AMP scenario SSDs enhance static content delivery and that's pretty much about it. And, no, SSDs won't do that much for DB record reading/writing. For one because the OS usually does a pretty good buffering job (See: RAM again!). Secondly the DB itself usually doesn't read/write in tiny pieces and thirdly, also because even a stupid simple, not RAID striped or HW buffered disk contributes relatively little cost to the whole cycle.

    And again: For 80% - 90% of websites out there all of this can basically be (and probably is) largely ignored. Except maybe "Get enough RAM! And then some more!".

    Thanked by 3wcypierre Joery aglodek
  • mehargagsmehargags Member
    edited January 2015

    Thanks alot for all the Words of Wisdom, I'm learning alot here. I feel the questions of DB management are far more subjective that typical app/web hosting scenarios, involving hairline analysis and tweaking at different front/back ends.

    That said, as @BsdGuy & @Jar mentioned, what are the best ways analyze and read real time Disk I/Os and Database TPS from the APP ? Also how to see the way PHP App is talking/behaving with the Databse. Please name some tools, keeping my beginner level in mind. I will surely look at mysqladmin processlist, but some good reporting/analysis tools in beginner readable form shall help.

    I'll try and run MySqlTuner script on the (2yrs old )current server and post the results here, is there anything else you all would like to see/investigate on the current server? I'll be happy to exercise the same.

    So very humbled by all your answers...! keep pouring more... love to be part of LET

  • What others have said, every database and application is unique, there's no generic answer.

    mysqlreport is also another tool to use that can help diagnose bottlenecks.

    Thanked by 1aglodek
  • @mehargags said:
    ... what are the best ways analyze and read real time Disk I/Os and Database TPS from the APP ? ...

    Short and dry: Your app.

    Forget about benchmarks. They may be useful for very simple scenarios like "Which of A, B, and C controllers puts most bytes out?". But they are next to worthless in complex scenarios - and the vast majority of real world scenarios is complex.

    If you want a real and very noticeable improvement write your app in a real language and compile it.
    And learn, learn, learn. About databases, your OS and its inner workings, FCGI, your web server, etc. etc.
    The reason is simple: Before you can play with tuning "screws" you must first well understand the devices and machines.

    And the quick and simple advice, you already got it. Go get more RAM.

    Thanked by 1jar
  • The mysql server is tracking all queries and you can use PhpMyAdmin or mysqlreport to list the queries with the most CPU or processing time.

    But you should start with your web application first:

    • Upgrade you CakePHP version
    • Disable Debug Mode
    • Enable caching for your models
    Thanked by 1mehargags
  • Try to use NDB engine faster than myIsam and innodb.

  • As a quick start, how Much RAM... would you simply advice for this Database.. The app front end claims to have 40-60,000 Visits with upto 100K pageviews a DAY

    Ofcourse, I will be doing a lot of reading and learning to tune up/down the hardware, and spend/save money as applicable. Still would appreciate if there is a ball park idea what hardware to start with, a high-end VPS @Ramnode or @Vultr or should I go dedicated ? Would like to keep it below $100 if possible, but can expand if it is really required.

  • 4n0nx4n0nx Member
    edited January 2015

    Yes go with Vultr, they have best RAID.

    edit: that was pure sarcasm

Sign In or Register to comment.