List:General Discussion« Previous MessageNext Message »
From:Manuel Arostegui Date:May 19 2014 6:18am
Subject:Re: Big innodb tables, how can I work with them?
View as plain text  
2014-05-15 14:26 GMT+02:00 Antonio Fernández Pérez <
antoniofernandez@stripped>:

> ​
>
> ​
> ​Hi,
>
> I have in my server database some tables that are too much big and produce
> some slow query, even with correct indexes created.
>
> For my application, it's necessary to have all the data because we make an
> authentication process with RADIUS users (AAA protocol) to determine if one
> user can or not navigate in Internet (Depending on the time of all his
> sessions).
>
> So, with 8GB of data in one table, what are your advices to follow?
> Fragmentation and sharding discarted because we are working with disk
> arrays, so not apply. Another option is to delete rows, but in this case, I
> can't. For the other hand, maybe de only possible solution is increase the
> resources (RAM).


Adding more RAM will only save you for a few weeks/months until the data
isn't able to fit in memory any longer. You will face the same problem soon
(if your data is and will be still growing).
There will be a point where you just can't buy more and better hardware
(actually you kinda can, but you will spend load of money and might end up
with nice servers just doing nothing because they support more memory in
their motherboard so you need to upgrade it too).

You should give your application a thought and start considering
noSQL/table sharding/partitioning/archiving.
Maybe it is too late, but before needing another hardware upgrade, yo
should've thought about a solution that would allow you keep growing
without needing to spend all in hardware (unless you have unlimited money).

Good luck!
Manuel.

Thread
Big innodb tables, how can I work with them?Antonio Fernández Pérez15 May 2014
  • Re: Big innodb tables, how can I work with them?Reindl Harald15 May 2014
  • Re: Big innodb tables, how can I work with them?Manuel Arostegui19 May 2014
    • Re: Big innodb tables, how can I work with them?Johan De Meersman19 May 2014
      • Re: Big innodb tables, how can I work with them?Manuel Arostegui19 May 2014
  • Re: Big innodb tables, how can I work with them?Andrew Moore19 May 2014
    • Re: Big innodb tables, how can I work with them?Antonio Fernández Pérez20 May 2014