CODEWITHSUNDEEP
mysqlperformancecodeigniterinnodbmysql-pconnect
myalcin81
myalcin81

Reputation: 196

Best way to optimize database performance in mysql (mariadb) with buffer size and persistence connection configurations

I have;

Tested;

  1. Persistent Connection in MySQL I faced an issue maximum connection exceed, so I noticed the Code Igniter do not close connection if you do not set pconnect to config to true in database.php. So, simplified that, it uses allow persistent connection if you set it true. So, I want to fix that issue and I find a solution that I need to set it false and it will close all connections automatically. I changed my configuration to disallow Persistent connections.

  2. After I update persistent connection disabled. My app started to run properly and after 1 hour later, it crashed again because of a couple of errors that showed below and I fixed those errors with setting max_allow_package to maximum value in my.cnf for mariadb.

    Warning --> Error while sending QUERY packet. PID=2434 Query error: MySQL server has gone away

  3. I noticed the DB needs to be tuning. The database size is 1GB+. I have a lot of CRUD jobs scheduled for every minute. So, I changed to buffer size to 1GB and innodb engine pool size to %25 of it. I get used to MySQL Tuner and I figure out those variables with that.

Finally, I am still getting query package errors.

Packets out of order. Expected 0 received 1. Packet size=23

My server has 8GB ram (%25 used), 4 core x 2ghz (%10 used) I couldn't decide which configuration is the best option for now. I couldn't increase RAM, also %25 used of ram because a key buffer size is 1GB and it could get full use of ram instant jobs.

Can I;

Upvotes: 1

Views: 2019

Answers (1)

Rick James
Rick James

Reputation: 142296

8GB ram --> innodb_buffer_pool_size = 5G.

200 qpm --> no problem. (200qps might be a challenge).

10 tables; 2 users --> not an issue.

persistent connections --> frill; not required.

key_buffer_size = 1G? --> Why? You should not be using MyISAM. Change to 30M.

max_allow_package --> What's that? Perhaps a typo for max_allow_packet? Don't set that to more than 1% of RAM.

Packets out of order --> sounds like a network glitch, not a database error.

MEDIUMINT --> one byte smaller than INT, so it is a small benefit when applicable.

Upvotes: 0

Related Questions