We’re developing an enterprise product that uses MySQL 5.6 on Windows to store reports generated by multiple clients. Our database contains approximately 20 tables, each table containing ranging from a few hundred thousand to some million records. All tables have more than 10 columns with a combination of numeric and textual data. All tables use innodb engine with numeric field as primary key. The tables are indexed on another numeric field, different than the primary key.
There are about 10 connections used to merge new data into the database. The data is viewed via a web console. There is as such no limit on the number of instances on viewing the data. We also don’t have any reference/foreign key in tables so we don’t use joins.
We haven’t created stored procedure for fetching data. Does the store procedure really improve performance?
While searching solution on the internet, I found that if we changes the values of innodb_buffer_pool_size, read_rnd_buffer_size, sort_buffer_size etc fields in my.ini/my.cnf files, then we can improve performance as well as minimize memory requirements of mysql. But I am not confident about it because I don’t know what should be the proper values of it and what are the side effect of it. Currently I kept the default configuration. Please let me know which values can be changed in the configuration file to improve performance and minimize memory requirements without any side effect.
I would also like to know some other ways to optimize & fine tune MySQL engine that would boost the performance & use optimum resources.
Minimum software/hardware requirement of our product is :
OS : Windows 2000 SP4 Professional / Server and later.
RAM : 1 GB
CPU : 1 GHz.