I have;

  • a CRUD heavily loaded application in PHP 7.3 which uses CodeIgniter framework.
  • only 2 users access to application.
  • The DB is mariadb 10.2 and has 10 tables.In generally, stored INT and engine default is InnoDB but in a table, I store a "mediumtext" column.
  • application managed by cronjobs (10 different jobs for every minute)
  • a job average proceed is 100-200 CRUD from DB. (Totally ~ 1k-2k CRUD works in a minute with 10 tables)

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;

  • fix the DB errors,
  • increase average completed CRUD process
1

There are 1 best solutions below

2
On

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.