Optimize MySQL

[trickzjen23]

I have a question, i am hosting my server in CentOS. However, due to huge database i have for my server. my server really lagging bad. If I am not mistaken global_reg_variables has 1.6million rows in it.

So far, what I did was to switch all MySAM tables to Innodb.(avoid locking queries)

I would like to ask what is the best mySQL server configuration for the best performance?

I mean for /etc/my.cnf?

My server has 12 GB Ram, and 8 cores.

Thanks!

 

[anacondaq]

Your question very strange. In your message i do not see any information how to help you. Please provide much much more details about your problem, like:

• are you have any problems with your database right now (any performance, etc)
• how big player base? How many active players usually per day?
• Tell some info about your server customization (it's for array / variable optimizations only, but i think you have kamishi-like addons which uses millons of rows for just stupid pallets or something like that)
• total database size
• tables list with tables size
• what is your mysql --version?
• show your my.cnf (mysql configuration)
• how many ram using your mysql server for now with your db?
• show information about your CPU (model \ Ghz \ cpuinfo)
• show information about your current disk \ model \ what speed of drive, how many iops etc

Only then depends on this information possible to walk to next step of understanding where is a problem and how optimize it. This issue very hard and complicated to be clear, but you can follow some basic recommendations for your server optimizations like:

1. use latest up to date mysql version, and see benchmarks of it. For example 5.7.* version faster then 5.5, 5.6 proof.
2. but at the same time mysql 5.7 has some compatibility issues, like: https://github.com/rathena/rathena/issues/1210
3. Usually when very large data available your disk drive (if your database not loaded to memory) will be bottleneck, so get only SSD / SSD NVMe disks for your database
4. Then, it's not correct, but better if you will use some tools like mysqltunner to understand some basic guide where need to move next, or for example https://tools.percona.com/wizard to configure your database depends on your problem. 
5. Then, please start to analyze your database, and understand what table sizes are big, and check the most common tables, and find reason what thing generate it, maybe it's time to rewrite some of your scripts? What do you think? 
6. Then, please enable for some time slow querries log to understand what querries uses hell a lot of your server resources, and optimize it (do not forget to disable it)
7. log database, maybe it's time to review logging things, maybe hell a lot of things at logs you are not using, or you can compress for example in seperate database?

 

It's just few cents what need to do... But for sure, there can be hell a lot more optimizations, because this problem very actual and very popular, and very complicated. Ah, and last, don't try to switch your mysql server to percona / mariadb, they will not give any benefits for you, tested, and confirmed on large databases by me several times, + you can see different benches in internet about it. 5.7 mysql version very fast