Alphabetic Ordering Hungs Mysql

[mondaka]

Testing my new cacti installation ... if i selected in graph tree, sorting type: Alphabetic Ordering, mysqld process goes 99%. Meanwhile with no sorting option, CACTI works excellent. By the way this problem appear with a tree of 300 leaves.

Id try tuning MYSQL following some tips from this board and from MYSQL forum but they dont work (qcache, key_bufer_size, table_cache, read_rnd_buffer_size).

Any workaround different of no sorting selection..?

In general, i have to say the upgrade was excellent, in v0.8.5 polling process take 300 secs, with v0.8.6d and cactid the polling process take 22 secs !!!!

Leo

Hosts:33
Data Queries: 10
Data Sources: 1500+
SUN Ultra 5/10
UltraSparcIIi 400Mhz
512MB RAM
| Solaris 8 | cacti 0.8.6d | cactid 0.8.6d | mysql 4.1.12-log | php 5.0.4 | net-snmp 5.2.1 | rrdtool 1.2.6 | apache 2.0.53 |

[rony]

Run a check on the cacti tables, graph_tree and graph_tree_items.

[TheWitness]

Tony, I suspect a BUZZ word here!

Larry

[rony]

You could be correct..

[mondaka]

I converted my cacti tables to innodb and now its a little faster. Do i have to roll back myisam?

Below is the output check while a cacti graph_tree save was running.

Leo
_______
Hosts:33
Data Queries: 10
Data Sources: 1500+
SUN Ultra 5/10
UltraSparcIIi 400Mhz
512MB RAM
| Solaris 8 | cacti 0.8.6d | cactid 0.8.6d | mysql 4.1.12-log | php 5.0.4 | net-snmp 5.2.1 | rrdtool 1.2.6 | apache 2.0.53 |
__________


mysql> check table graph_tree_items;
+------------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------------+-------+----------+----------+
| cacti.graph_tree_items | check | status | OK |
+------------------------+-------+----------+----------+
1 row in set (0.01 sec)

mysql> check table graph_tree;
+------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------+-------+----------+----------+
| cacti.graph_tree | check | status | OK |
+------------------+-------+----------+----------+
1 row in set (0.00 sec)

mysql> show innodb status\g
| Status
|
=====================================
050527 14:14:33 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 47 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 306, signal count 306
Mutex spin waits 1215, rounds 11210, OS waits 161
RW-shared spins 198, OS waits 99; RW-excl spins 46, OS waits 46
------------
TRANSACTIONS
------------
Trx id counter 0 27387
Purge done for trx's n: o < 0 27342 undo n: o < 0 0
History list length 34
Total number of lock structs in row lock hash table 61
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 27088, not started, OS thread id 13
MySQL thread id 132, query id 15334 localhost root
show innodb status
---TRANSACTION 0 27386, ACTIVE 0 sec, OS thread id 12 fetching rows, thread declared inside InnoDB 1
89
mysql tables in use 1, locked 1
62 lock struct(s), heap size 5504
MySQL thread id 131, query id 15333 localhost cacti Updating
update graph_tree_items set order_key = CONCAT('x',order_key) where order_key like '002003058%%' and
order_key != '0020030580000000000000000000000000000000000000000000000000000000000000000000000000000
00000' and graph_tree_id = '13'
--------
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
119 OS file reads, 7666 OS file writes, 6429 OS fsyncs
1.57 reads/s, 16384 avg bytes/read, 14.34 writes/s, 11.98 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 1106407, used cells 6820, node heap has 8 buffer(s)
0.00 hash searches/s, 40.89 non-hash searches/s
---
LOG
---
Log sequence number 0 25196652
Log flushed up to 0 25196652
Last checkpoint at 0 25188139
0 pending log writes, 0 pending chkp writes
6139 log i/o's done, 11.49 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 317167466; in additional pool allocated 1947008
Buffer pool size 16384
Free buffers 15204
Database pages 1172
Modified db pages 14
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 156, created 1016, written 3188
1.57 reads/s, 0.00 creates/s, 3.74 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
Main thread id 10, state: sleeping
Number of rows inserted 63000, updated 22978, deleted 0, read 23154585
0.00 inserts/s, 10.83 updates/s, 0.00 deletes/s, 53257.33 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set, 1 warning (0.05 sec)

mysql>

[TheWitness]

No, I'm brain dead. Thinking of something different. (I think...)

EDIT: Tony, we need an index. Can you accomodate in the 0.8.6d -> 0.8.6e code?

TheWitness

[rony]

I think the index is already there, I will have to check.

[TheWitness]

I'm looking over it too. I decided not to do what I was planning on doing.

Larry

[TheWitness]

Tony,

I have looked things over, outside of the pK's, we need indexes on the following fields:

graph_local.host_id < missing
graph_local.graph_template_id < missing
graph_local.snmp_query_id < missing
graph_local.snmp_index < missing

graph_templates_graph.local_graph_id < ok
graph_templates_graph.graph_template_id < ok
graph_templates_graph.title_cache < missing

user_auth_perms.item_id < ok
user_auth_perms.type < ok
user_auth_perms.user_id < ok

graph_tree_items.host_id < missing
graph_tree_items.local_graph_id < missing
graph_tree_items.graph_tree_id < ok
graph_tree_items.order_key < missing

graph_templates.name < missing

snmp_query.name < missing

I don't know which ones have been created yet though.

EDIT: Modified to show missing indexes

Larry