MySQL performance and slow inserts

[bbrendon]

Hello-

I've been having a problem with slow inserts related to the history and history_str tables.

Inserts to these tables sometimes take between 11 to 18 seconds. I haven't been able to figure out WHY as of yet.

I've gone through three rounds of performance tweeks to my.cnf and haven't had any noticeable improvements yet. Apparently the required change is hidden.

Has anyone else had this problem and found a resolution?
-bb

[Alexei]

11-18 seconds is something extreme. It may happen when you're running MySQL MyISAM on a low-spec hardware or MySQL settings while ZABBIX housekeeper is running. MyISAM locks the whole table on insert/update operations. I would suggest to:

1. Tweak MySQL MyISAM settings (key_buffer)
2. Improve disk IO (faster disks, RAID, etc)
3. Add more memory

[bbrendon]

All of my Zabbix tables are innodb. Should I convert them to myisam?

[Alexei]

No! In this case you shall tune InnoDB specific parameters in my.cnf.

[bbrendon]

11-18 seconds is something extreme. It may happen when you're running MySQL MyISAM on a low-spec hardware or MySQL settings while ZABBIX housekeeper is running. MyISAM locks the whole table on insert/update operations. I would suggest to:

1. Tweak MySQL MyISAM settings (key_buffer)
2. Improve disk IO (faster disks, RAID, etc)
3. Add more memory

Ok. Now I'm confused. (Below are your #s)

1. According to #1 I should be using MyISAM. Why else would I tune myisam settings?
2. Using Raid1. Disk IO is relatively consistent.
3. Possibly. Tuning MYSQL for the memory is important though and my first inclination is that this is not yet a memory issue. On average I'm doing 60 queries per second.

The system has quite a bit running on it. Its a dual core Opteron w/2 GB ram.

IO on the RAID array is about 110 write operations /sec and 10 read / sec
Mysql uses about 350 MB RAM.
Swap used runs about 350 - 500 MB
Mytop more often then not shows sleeping on all connections
There are 3401239 items in the history table

[Alexei]

I assumed that you run MyISAM that's why I mentioned tuning of MyISAM parameters.

Anyway, 60 queries per second is nothing on your hardware, it may handle thousands of qps! Check what MySQL is doing, run 'mysqladmin processlist'.

[bbrendon]

All sleeping. Mytop usually shows everything sleeping.

[Alexei]

Sorry, I'm running out of ideas...

[James Wells]

Greetings,

I've been having a problem with slow inserts related to the history and history_str tables.

Inserts to these tables sometimes take between 11 to 18 seconds. I haven't been able to figure out WHY as of yet.

Right off the bat, I suspect that you have indexed these tables, if so, remove the indexes. Another possibility would be that you have a very large collection of historical rows.

We still have these kinds of slow downs on one of my server sets with roughly 380GB in history alone.

[bbrendon]

modena:/etc/mysql# diff my.cnf.070204 my.cnf
0a1,3
< key_buffer            = 64M
---
> key_buffer_size               = 32M
65c71
< thread_cache_size     = 8
---
> thread_cache_size     = 16
80c86
< tmp_table_size = 128M
---
> tmp_table_size = 64M
82c88,92
< table_cache = 192
---
> max_heap_table_size = 32M
> 
> table_cache = 512
123,124c133,141
< sort_buffer_size = 4M
---
> innodb_log_buffer_size = 4M

These are the changes I made the other day and this morning I already have 20 slow queries racked up.

I'll be looking into James's idea next.

[Alexei]

Try setting this variable to something close 1/2-3/4 of available RAM:

set-variable = innodb_buffer_pool_size=512M

[bbrendon]

OK. I have removed the following indexes. My slow inserts are currently at 75.

The indexes on the history table that were removed are:
keyname: itemidclock
type: INDEX
cardinality: 3581835
field: itemid, clock

The indexes on the history_str table that were removed are:
keyname: itemidclock
type: index
cardinality: 275305
field: itemid, clock

[bbrendon]

Right off the bat, I suspect that you have indexed these tables, if so, remove the indexes. Another possibility would be that you have a very large collection of historical rows.

We still have these kinds of slow downs on one of my server sets with roughly 380GB in history alone.

After removing the indexes, CPU usage went through the roof. Load has gone way up. I'm adding back the indexes!

[Palmertree]

How much memory does your MySQL server have?

[bbrendon]

How much memory does your MySQL server have?

It says on the first page. 2gb