Extremely slow history_log inserts

[Smoke]

I'm having a problem with items of type log that takes forever to be stored on the database and end up filling the whole textwrite cache.

After some diagnostics i see that the problem is slow inserts on history_log table, as shown by these numbers... and i also cleaned up this table but the problem persists.

It turns impossible to store log items like this, and the average size of the item is 200 bytes, so its not that big... each entry has 1 line of text, and the value column is also ~ 200 B each.

synced 4024 items in 35.296247 sec
synced 9871 items in 120.146508 sec
synced 2023 items in 19.796334 sec
synced 4556 items in 31.893614 sec
synced 12242 items in 137.315439 sec

Currently, the table only has 90k records, and speed has not been increased

Anyone has experienced this issue or has any feedback?
80-120 records per second of insert seems extremely slow compared to other values

[Colttt]

how big is you database?
maybe you must use tablepartition..

http://zabbix.org/wiki/Docs/howto/mysql_partition

[Smoke]

the table only has 40k records, it is pretty small and its using only 700mb.
Why would i need to partition with this amount of data? makes no sense.

[ArtemK]

well, you could probably use some kind of tuning software to check your database. If you use mysql - check mysqltuner

[Smoke]

I did run mysqltuner script, it did not provide much information but the table and overall DB look good, a few tables were fragmented but thats all.

I'm wondering if these are normal numbers for the history_log table or indeed those inserts are very slow, maybe these are normal speed and there is nothing to do?

[ArtemK]

storage subsystem?

What is your values of iowait on database host, what storage do you have? Could you post mysqltuner output?

[Smoke]

Certainly..
Storage is Local SAS, and a dummy dd test does return good results so far.
1073741824 bytes (1.1 GB) copied, 2.15303 s, 499 MB/s
5368709120 bytes (5.4 GB) copied, 10.7512 s, 499 MB/s

iowait on DB host , avg of 1d is 8.2%, peak is 35%, there are some peaks but never for more than 1hr, clearly when housekeeper runs i bet. but nothing really strange there.

Here is the output of MySQL tunner

 >>  MySQLTuner 1.3.0 - Major Hayden <[email protected]>
[OK] Currently running supported MySQL version 5.6.19-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in InnoDB tables: 30G (Tables: 104)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 22

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 11h 15m 3s (31M q [251.171 qps], 88K conn, TX: 45B, RX: 5B)
[--] Reads / Writes: 25% / 75%
[--] Total buffers: 10.5G global + 14.1M per thread (500 max threads)
[!!] Maximum possible memory usage: 17.4G (111% of installed RAM)
[OK] Slow queries: 0% (3/31M)
[OK] Highest usage of available connections: 25% (125/500)
[OK] Key buffer size / total MyISAM indexes: 256.0M/101.0K
[OK] Key buffer hit rate: 100.0% (214M cached / 3 reads)
[OK] Query cache efficiency: 57.4% (5M cached / 9M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 7M sorts)
[OK] Temporary tables created on disk: 16% (179K on disk / 1M total)
[OK] Thread cache hit rate: 96% (2K created / 88K connections)
[OK] Table cache hit rate: 95% (400 open / 420 opened)
[OK] Open file limit used: 0% (74/8K)
[OK] Table locks acquired immediately: 99% (20M immediate / 20M locks)
[!!] InnoDB  buffer pool / data size: 10.0G/30.3G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    innodb_buffer_pool_size (>= 30G)

[ArtemK]

You gave too much memory to mysql, check if system doesn't use swap heavily

swapon -s
vmstat 1 20 (check si - swap in,so -swap out)

mysqltuner clearly saying it to you

Reduce your overall MySQL memory footprint for system stability

also, IMHO, key buffer size is too high, buffers per thread also are high (I have 1.2M per thread).

I'm not MySQL expert, but looks like your database performs poorly because of configuration.

Could you post mysql config and zabbix server config (how much db syncers do you have?)