MySQL INNODB Tuning

MySQL & MariaDB Other MySQL® Questions
1

Hello,

I have one dedicated MySQL database server with 4GB RAM only serving one database and table engine is INNODB.

According to stats we are getting only INSERT,UPDATE,DELETE queries on this server with no SELECT queries. Also this database server receiving around 150 queries per second (as i applied trigger which records data in another database on same server). Mostly server hungs with “Too many connections” error and max_connections limit is 200.

innodb_buffer_pool_size is set to default 256MB. Can someone please let me know which parameters i need to look in to speedup data modification queries.

I guess innodb_buffer_pool is cache which used for SELECTs only. Let me know increasing innodb_buffer_pool_size speedup INSERT queries on not and which are other parameters i need to look in. Please help me out to tune this server.

2

The buffer pool is used for all queries, not just SELECT.

3

Apart from innodb_buffer_pool_size what other parameters i need to look in to speed up INSERT/UPDATE queries.

Also in mysql data directory innodb log files (log_file0 & log_file1) resides. What sort of info those log files have and how please explain it little.

What i undertand those innodb logfiles are undo logs. During write queries data first written to those logfiles and then it committed to disk. Finally it’s deleted from logfile.

Please correct me if i am wrong.

Thank you.