Optimize MySQL - MySQLTuner

[Solutho]

Tables that have a lot of 'SELECT' queries being run on them will often end up being fragmented.

Hmmm... why does this happens? I mean the 'fragmented' part.

(Great thread, btw! Cheers)

 

[Rox]

I cannot seem to find the my.cnf file on kloxo! Any help will be appreciated.

I am not that good with ssh so i want to edit that file through kloxo. I am comfortable that way.

 

[pi0tr3k]

I'm worried about OPTIMIZE TABLE query, as I'm sure my mysql server will die.
I have tables with 4MILL records, ~12GB ..

How can I optimize such a big table, without any problems.?

 

[Whoo]

I'm worried about OPTIMIZE TABLE query, as I'm sure my mysql server will die.
I have tables with 4MILL records, ~12GB ..

How can I optimize such a big table, without any problems.?

Run it with the nice command.

 

[Sp32]

I cannot seem to find the my.cnf file on kloxo! Any help will be appreciated.

I am not that good with ssh so i want to edit that file through kloxo. I am comfortable that way.

It should be in the /etc/ folder?

What operating system are you running?

 

[Rox]

Centos

 

[Sp32]

Yes, it should be in the /etc/ folder called my.cnf

Can you not find it?

 

[Rox]

I cannot find the /etc/ folder lol

 

[Sp32]

the /etc/ folder should be a folder in your / directory, so you need to go to the highest directory then locate the etc folder, if in SSH type "cd /etc/" without quotes

I suggest you try SSH, it is very good once you are used to doing stuff, and nano is an extremely easy editor to use, as simple as notepad.

 

[Rox]

Arh! Will use SSH. Thanks for this post Sp

 

[Lifetalk]

I'm worried about OPTIMIZE TABLE query, as I'm sure my mysql server will die.
I have tables with 4MILL records, ~12GB ..

How can I optimize such a big table, without any problems.?

Increase the size of your innodb buffer pool. You've got quite a bit of free RAM and you could possibly up that size by another 1GB or so. Optimize your tables after that.

Use the nice command as Whoo suggested (or renice the pid after), and take your site down while the optimize is running. Depending on your server and drive, it may take some time.

I suggested taking the site offline because you don't want queries hitting your database all the while you're optimizing the tables which would just build up a queue because they'll wait for locked tables to open before they can actually complete; you'll end up building a backlog of hundreds of queries that will kill the MySQL server.

 

[PacificHost]

I'm worried about OPTIMIZE TABLE query, as I'm sure my mysql server will die.
I have tables with 4MILL records, ~12GB ..

How can I optimize such a big table, without any problems.?

Lower max_connections to 128
Lower key_buffer to 50M
Insert query_cache_type=1
Insert max_seeks_for_key=10
Lower open_files_limit to 2048
Raise table_cache to 2048

It seems you have about 20.6G of InnoDB tables, which outweights the resources available on your server. I would suggest truncating some tables that don't need to be so large. Set the ENGINE for temporary tables such as session tables to the MEMORY engine.

 

[cvrle77]

I am sure that after 9 months OP got wanted reply.

 

[First-Dragon]

Little help please. Thanks

 

[PacificHost]

You should be able to raise tmp_table_size and max_heap_table_size to 64M and be okay. To use a little less memory, you can also set max_connections to 128.