top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How can I work with Big innodb tables?

+1 vote
483 views

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 advises to follow? Fragmentation and sharing discarded 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 the only possible solution is increase the resources (RAM).

Any suggestions?

posted May 15, 2014 by Sheetal Chauhan

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote

rule of thumbs is innodb_buffer_pool = database-size or at least as much RAM that frequently accessed data stays always in the pool

answer May 15, 2014 by Amit Parthsarthi
Similar Questions
+1 vote
Optimizing InnoDB tables in MySQL?

I was trying to optimize the InnoDB tables. I have executed the next query to detect what are the fragmented tables.

SELECT TABLE_SCHEMA,TABLE_NAME
FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND Data_free > 0

After that, I have seen that there are 49 fragmented tables. With one table, I have executed "optimize table table_name;" and "analyze table table_name;". The result is the same, the table continuous fragmented.

Any suggestions? I have followed the mysqltuner recommendations...

+1 vote
InnoDB problem on MySQL - table does not exist

I've restored an MySQL backup from our MySQL server into another server. The backup includes InnoDB tables. After the import, MySQL recognized the innodb tables fine but when I try to do a check table it returns that the table doesn't exists.

Permission and owner of the table files (.frm files) are ok, since it recognizes MyISAM tables (they have the same permission). Innodb engine is enabled..

Which can cause the tables to appears as "non existent", as far as they do really exist?

+2 votes
MySQL Archive Engine

I have a customer who is wanting to use the Archive Engine. I have no experience with this engine, other than what I am been reading. Why would I want to use Archive over InnoDB. They are only going to be placing audit information in the table.

0 votes
How can I insert data into a table from two other tables in mysql?

How can I insert data into one table from two other tables where i have three tables namely users, role and userrole.
Now I want to insert the data into userrole table from users table and role table with a single statement.

0 votes
SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?

I've searched but with no luck... what do exactly these variables mean:

1343928 OS file reads, ********** OS file writes, 19976022 OS fsyncs

I am wondering if my innodb_buffer_pool setting is not to low. Does 'file reads' show number of times innodb files have been read into memory from server's start? What about file writes/fsyncs?

...