Ad Widget

**nelsonab** · 01-04-2008, 01:57

It sounds like your problem is quite similar to others known performance issues with MySQL. A few things to consider, first have you optimized the configuration of MySQL? Take a look around the forums for what others have done to optimize MySQL. I had a server where MySQL was taking up around 50-80% of one of the processors, with a little tweaking it's now down to around 10-20% with about 15-20 hosts. I found moving the Index and Data files for InnoDB to different physical drives/controllers helps a lot.

Take a look in MySQL. Is there a query which is hung? I cannot remember the command but it's something to the effect of "show status" or "show query".

Also check the MySQL logs and system logs to see if anything is out of place. Those are the only places I can think to look at this point. Hope this helps a little.

**Tenzer** · 01-04-2008, 09:33

Originally posted by nelsonab

Take a look in MySQL. Is there a query which is hung? I cannot remember the command but it's something to the effect of "show status" or "show query".

You are probably talking about "show processlist"

**sebelk** · 01-04-2008, 17:26

Originally posted by nelsonab

It sounds like your problem is quite similar to others known performance issues with MySQL. A few things to consider, first have you optimized the configuration of MySQL? .

Thanks nelsonab, I am trying to tune mysql, so I copied /usr/share/doc/mysql-server-5.0.27/my-huge.cnf to /etc/my.cnf. Mysql starts as usual, but then I wanted to tweak enable the InnoDB parameters:

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

But I get the following in zabbix web page:

# Error in query [select * from nodes where nodetype=1 order by nodeid] [Incorrect information in file: './zabbix15/nodes.frm']
# mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource[/usr/local/share/zabbix/include/db.inc.php:411]
# Error in query [SELECT u.*,s.* FROM sessions s,users u WHERE s.sessionid='e062d85fcbcaffd249118a9cd69b6634' AND s.userid=u.userid AND ((s.lastaccess+u.autologout>1207061670) OR (u.autologout=0)) AND (u.userid div 100000000000000) in (0) ] [Incorrect information in file: './zabbix15/sessions.frm']
# mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource[/usr/local/share/zabbix/include/db.inc.php:411]
# Error in query [SELECT u.* FROM users u WHERE u.alias='guest' AND (u.userid div 100000000000000) in (0) ] [Incorrect information in file: './zabbix15/users.frm']
# mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource[/usr/local/share/zabbix/include/db.inc.php:411]
# Error in query [delete from sessions where sessionid='e062d85fcbcaffd249118a9cd69b6634'] [Incorrect information in file: './zabbix15/sessions.frm']
# Error in query [SELECT count(u.userid) as user_cnt FROM users u] [Incorrect information in file: './zabbix15/users.frm']
# mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource[/usr/local/share/zabbix/include/db.inc.php:411]
# Error in query [select * from nodes where nodeid=0] [Incorrect information in file: './zabbix15/nodes.frm']
# mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource[/usr/local/share/zabbix/include/db.inc.php:411]

Also, /var/log/mysqld.log looks as follows:
080401 12:00:08 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './zabbix15/items.frm'
080401 12:00:08 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './zabbix15/items.frm'
080401 12:00:08 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './zabbix15/hosts.frm'
080401 12:00:08 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './zabbix15/hosts.frm'
080401 12:00:08 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './zabbix15/items.frm'
080401 12:00:08 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './zabbix15/items.frm'
080401 12:00:08 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './zabbix15/hosts.frm'
080401 12:00:08 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './zabbix15/hosts.frm'
080401 12:00:08 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './zabbix15/items.frm'
080401 12:00:08 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './zabbix15/items.frm'

Why do I get these errors?

Thanks in advance!

**sebelk** · 01-04-2008, 18:07

Originally posted by Tenzer

You are probably talking about "show processlist"

**nelsonab** · 04-04-2008, 02:22

I'm not sure how to help on this one, so here's a stab in the dark. When you changed the settings on MySQL, did you delete the InnoDB index file? It's been a while for me but I remember I had to delete the Index file after I messed with a few settings. The index file is usually a fair bit (or a lot) smaller than the main data file. :-)

**sebelk** · 08-04-2008, 21:42

Well I restarted from scratch, so I remade the database, and problems arises again when I add screen (about 8 dynamic items for 20 hosts).

I changed the my.cnf to this:

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8
log-bin=mysql-bin
server-id = 1
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
innodb_buffer_pool_size = 384M
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

Please tell me if is a bug, if isn't how I do for zabbix be usable.

Thanks in advance...

**justincase** · 09-04-2008, 00:48

I saw this once before with screens, but I cannot reproduce it now, only because I cannot recall the details. It was on 1.4.4

Try setting these:

log=/var/log/mysql/query_log
log-slow-queries=/var/log/mysql/slow-query-log

The slow query log is good to run on any production db. You can set what is considered slow by time.

That will log the slow query that has the other process stuck in a locked state. Seems from your "show processlist" that the "copy to tmp table" process is holding up the works. Try "show full processlist" if the slow-query-log does not give you what you need to figure out why this is so slow. It may not be a tuning issue at all, but a very expensive (erroneous?) query.

**Alexei** · 09-04-2008, 06:58

You may also consider switching to InnoDB. It seems you use MyISAM currently.

**sebelk** · 09-04-2008, 16:00

Thanks, Problem lasts yet

**sebelk** · 09-04-2008, 17:58

Using MYSQL PERFORMANCE TUNING PRIMER

I've found a script in
http://www.day32.com/MySQL/ that it looks interesting, I've modified some parameters in my.cnf , but I don't understand how change Memory USAGE and Key Buffer:

-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

.....

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 5 sec.
You have 0 out of 189 that take longer than 5 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/...-recovery.html

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 0
Current threads_per_sec = 11
Historic threads_per_sec = 1
Threads created per/sec are overrunning threads cached
You should raise thread_cache_size

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 29
Historic max_used_connections = 29
The number of used connections is 29% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 1 G
Configured Max Per-thread Buffers : 2 G
Configured Max Global Buffers : 238 M
Configured Max Memory Limit : 2 G
Physical Memory : 500.71 M

Max memory limit exceeds 90% of physical memory

KEY BUFFER
No key reads?!
Seriously look into using some indexes
Current MyISAM index space = 223 K
Current key_buffer_size = 8 M
Key cache miss rate is 1 : 0
Key buffer fill ratio = 0 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 6 M
Current query_cache_used = 424 K
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 6.90 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 8 M
Current read_rnd_buffer_size = 15 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 2.00 M
You have had 6 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 8192 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 1024 tables
You have a total of 210 tables
You have 193 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 127 M
Current tmp_table_size = 64 M
Of 1363 temp tables, 49% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 1 M
Current table scan ratio = 23 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 0 : 3459
Your table locking seems to be fine

---End of Report---

any ideas?

**sebelk** · 09-04-2008, 20:42

Innodb Monitor Output

I run the script tuning-primer.sh with innodb as parameter and I get this:

1. row

Status:

080409 15:03:14 INNODB MONITOR OUTPUT

Per second averages calculated from the last 46 seconds

SEMAPHORES
OS WAIT ARRAY INFO: reservation count 379748, signal count 359397
Mutex spin waits 248686031, rounds 167742451, OS waits 45781
RW-shared spins 247506, OS waits 18976; RW-excl spins 290062, OS waits 11488

TRANSACTIONS

Trx id counter 0 2200490
Purge done for trx's n

< 0 2200265 undo n

< 0 0
History list length 104
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 20955, OS thread id 2769447824
MySQL thread id 1427, query id 1714570 localhost root
SHOW /*!50000 ENGINE */ INNODB STATUS
MySQL thread id 161, query id 1663602 localhost zabbix
MySQL thread id 95, query id 1708374 localhost zabbix
---TRANSACTION 0 2138195, not started, process no 20955, OS thread id 2773441424
MySQL thread id 93, query id 1545379 localhost zabbix
---TRANSACTION 0 2050905, not started, process no 20955, OS thread id 2773642128
MySQL thread id 92, query id 1317330 localhost zabbix
---TRANSACTION 0 1855126, not started, process no 20955, OS thread id 2773842832
MySQL thread id 91, query id 980642 localhost zabbix
---TRANSACTION 0 2167814, not started, process no 20955, OS thread id 2774043536
MySQL thread id 89, query id 1623404 localhost zabbix
---TRANSACTION 0 1440518, not started, process no 20955, OS thread id 2774244240
MySQL thread id 88, query id 55441 localhost zabbix
---TRANSACTION 0 2108850, not started, process no 20955, OS thread id 2775174032
MySQL thread id 84, query id 1468986 localhost zabbix
---TRANSACTION 0 2200408, not started, process no 20955, OS thread id 2762787728
MySQL thread id 51, query id 1714313 localhost zabbix
---TRANSACTION 0 2200481, not started, process no 20955, OS thread id 2768468880
MySQL thread id 44, query id 1714547 localhost zabbix
---TRANSACTION 0 2200469, not started, process no 20955, OS thread id 2768669584
MySQL thread id 43, query id 1714504 localhost zabbix
---TRANSACTION 0 2200389, not started, process no 20955, OS thread id 2767190928
MySQL thread id 41, query id 1714266 localhost zabbix
---TRANSACTION 0 2200453, not started, process no 20955, OS thread id 2779397008
MySQL thread id 42, query id 1714456 localhost zabbix
---TRANSACTION 0 2200396, not started, process no 20955, OS thread id 2758585232
MySQL thread id 40, query id 1714284 localhost zabbix
---TRANSACTION 0 2200308, not started, process no 20955, OS thread id 2758785936
MySQL thread id 39, query id 1714055 localhost zabbix
---TRANSACTION 0 2200374, not started, process no 20955, OS thread id 2779597712
MySQL thread id 38, query id 1714228 localhost zabbix
---TRANSACTION 0 2200367, not started, process no 20955, OS thread id 2779798416
MySQL thread id 37, query id 1714210 localhost zabbix
---TRANSACTION 0 2200349, not started, process no 20955, OS thread id 2779999120
MySQL thread id 36, query id 1714164 localhost zabbix
---TRANSACTION 0 2200305, not started, process no 20955, OS thread id 2780199824
MySQL thread id 35, query id 1714049 localhost zabbix
---TRANSACTION 0 2200324, not started, process no 20955, OS thread id 2780400528
MySQL thread id 34, query id 1714098 localhost zabbix
---TRANSACTION 0 2200341, not started, process no 20955, OS thread id 2780601232
MySQL thread id 33, query id 1714141 localhost zabbix
---TRANSACTION 0 2200260, not started, process no 20955, OS thread id 2773072784
MySQL thread id 32, query id 1713939 localhost zabbix
---TRANSACTION 0 2200274, not started, process no 20955, OS thread id 2775374736
MySQL thread id 31, query id 1713971 localhost zabbix
---TRANSACTION 0 2200315, not started, process no 20955, OS thread id 2777676688
MySQL thread id 30, query id 1714073 localhost zabbix
---TRANSACTION 0 2200241, not started, process no 20955, OS thread id 2780801936
MySQL thread id 29, query id 1713890 localhost zabbix
---TRANSACTION 0 2200234, not started, process no 20955, OS thread id 2781002640
MySQL thread id 28, query id 1713872 localhost zabbix
---TRANSACTION 0 2200191, not started, process no 20955, OS thread id 2831334288
MySQL thread id 27, query id 1713768 localhost zabbix
---TRANSACTION 0 2200414, not started, process no 20955, OS thread id 2831534992
MySQL thread id 26, query id 1714331 localhost zabbix
---TRANSACTION 0 2200265, not started, process no 20955, OS thread id 2781203344
MySQL thread id 25, query id 1713952 localhost zabbix
---TRANSACTION 0 2200208, not started, process no 20955, OS thread id 2779102096
MySQL thread id 24, query id 1713812 localhost zabbix
---TRANSACTION 0 2200180, not started, process no 20955, OS thread id 2781404048
MySQL thread id 23, query id 1713449 localhost zabbix
---TRANSACTION 0 2200416, not started, process no 20955, OS thread id 2781604752
MySQL thread id 22, query id 1714345 localhost zabbix
---TRANSACTION 0 2200162, not started, process no 20955, OS thread id 2781805456
MySQL thread id 21, query id 1713410 localhost zabbix
---TRANSACTION 0 2200202, not started, process no 20955, OS thread id 2782006160
MySQL thread id 20, query id 1713797 localhost zabbix
---TRANSACTION 0 2200169, not started, process no 20955, OS thread id 2782206864
MySQL thread id 19, query id 1713425 localhost zabbix
---TRANSACTION 0 2200211, not started, process no 20955, OS thread id 2782407568
MySQL thread id 18, query id 1713818 localhost zabbix
---TRANSACTION 0 2200221, not started, process no 20955, OS thread id 2782808976
MySQL thread id 16, query id 1713843 localhost zabbix
---TRANSACTION 0 2200277, not started, process no 20955, OS thread id 2783009680
MySQL thread id 15, query id 1713979 localhost zabbix
---TRANSACTION 0 2200214, not started, process no 20955, OS thread id 2783210384
MySQL thread id 14, query id 1713824 localhost zabbix
---TRANSACTION 0 2200485, not started, process no 20955, OS thread id 2783411088
MySQL thread id 13, query id 1714558 localhost zabbix
---TRANSACTION 0 2200287, not started, process no 20955, OS thread id 2783611792
MySQL thread id 12, query id 1714003 localhost zabbix
---TRANSACTION 0 2200101, not started, process no 20955, OS thread id 2783812496
MySQL thread id 11, query id 1713259 localhost zabbix
---TRANSACTION 0 2200483, not started, process no 20955, OS thread id 2784013200
MySQL thread id 10, query id 1714551 localhost zabbix
---TRANSACTION 0 2200130, not started, process no 20955, OS thread id 2784213904
MySQL thread id 9, query id 1713332 localhost zabbix
---TRANSACTION 0 2200413, not started, process no 20955, OS thread id 2784414608
MySQL thread id 8, query id 1714325 localhost zabbix
---TRANSACTION 0 2200415, not started, process no 20955, OS thread id 2784615312
MySQL thread id 7, query id 1714333 localhost zabbix
---TRANSACTION 0 2200104, not started, process no 20955, OS thread id 2784816016
MySQL thread id 6, query id 1713266 localhost zabbix
---TRANSACTION 0 2200115, not started, process no 20955, OS thread id 2785016720
MySQL thread id 5, query id 1713295 localhost zabbix
---TRANSACTION 0 2200440, not started, process no 20955, OS thread id 2785217424
MySQL thread id 4, query id 1714431 localhost zabbix
---TRANSACTION 0 2079343, not started, process no 20955, OS thread id 2831735696
MySQL thread id 3, query id 1392141 localhost zabbix
---TRANSACTION 0 2200489, ACTIVE 0 sec, process no 20955, OS thread id 2782608272 fetching rows, thread declared inside InnoDB 486
mysql tables in use 2, locked 0
MySQL thread id 17, query id 1714569 localhost zabbix Sending data
select count(*),min(nextcheck) from items i,hosts h where h.status=0 and h.disable_until<1207764194 and h.errors_from=0 and h.hostid=i.hostid and h.proxy_hostid=0 and i.status in (0,3) and i.type not in (2,7,9) and mod(i.itemid,35)=14 and i.key_ not in ('status','icmpping','icmppingsec','zabbix[log]') and h.hostid between 000000000000000 and 099999999999999
Trx read view will not see trx with id >= 0 2200490, sees < 0 2200490
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1707 OS file reads, 53999 OS file writes, 13058 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 13.35 writes/s, 7.72 fsyncs/s
INSERT BUFFER AND ADAPTIVE HASH INDEX
Ibuf: size 1, free list len 5, seg size 7,
1682 inserts, 2873 merged recs, 659 merges
Hash table size 643421, used cells 22312, node heap has 23 buffer(s)
134572.51 hash searches/s, 5026.39 non-hash searches/s
LOG
Log sequence number 0 400585017
Log flushed up to 0 400574320
Last checkpoint at 0 327779507
0 pending log writes, 0 pending chkp writes
10092 log i/o's done, 7.39 log i/o's/second

BUFFER POOL AND MEMORY

Total memory allocated 203072802; in additional pool allocated 3130752
Buffer pool size 9920
Free buffers 3372
Database pages 6525
Modified db pages 3516
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 2470, created 4055, written 96764
0.00 reads/s, 58.13 creates/s, 17.17 writes/s
Buffer pool hit rate 1000 / 1000

ROW OPERATIONS

1 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 20955, id 2805955472, state: sleeping
Number of rows inserted 838854, updated 228129, deleted 0, read 2820581574
13188.80 inserts/s, 29.50 updates/s, 0.00 deletes/s, 125118.65 reads/s
END OF INNODB MONITOR OUTPUT

Please tell me if I am doing something wrong, or if is bug of dynamic screens feature, thanks in advance!

**sebelk** · 10-04-2008, 14:30

My terrible and stupid mistake and undefined index problem

OK, you perhaps want to insult at me, because, I found that there was using the stable release front-end instead of 1.5 one. So I think that that was the mysql headaches. However I hope that my "research" had been useful for someone who wants to tweak database...

Well, I deleted old installation, and started again, compiling from sources, creating database, copying new frontend, etc.

Now I am in front of a new problem, when I want to create a template, it outputs:

" * Undefined index: proxy_hostid[/usr/local/share/zabbix/hosts.php:206]
* Error in query [insert into hosts (hostid,proxy_hostid,host,port,status,useip,dns,ip ,disable_until,available) values (10028,,'UP Switches 2',10050,3,0,'','0.0.0.0',0,0)] [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''UP Switches 2',10050,3,0,'','0.0.0.0',0,0)' at line 1]"

How can I fix it?

Thanks in advance

**Aly** · 10-04-2008, 16:42

Sounds like this: Link

Ad Widget

zabbix (mysql) eats up the cpu

zabbix (mysql) eats up the cpu

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment