We have an internal SNMP monitoring system that is monitoring about 10,000 devices. Each device is pinged then pulled for about an average of 25-30 elements. Each of the ping results and elements are then stored in text file, then another system picks them up (NFS) and inserts them into a MyISAM (3.23.54) database. The data is kept for 13 weeks.
The database system is a Xeon 4 way, 12GB of ram with a striped raid array dedicated to the database files and its indexes and such.
Every 5 minutes another process goes through the last set of inserts and compares them for any threshold breaches, so the entire last set of data is looked at.
We're falling behind on the inserts because the system can't seem to handle the amount of inserts, the front end that generates the web pages based on the previous records is dogging down.
I have read the regular optimizations papers and have done as much as I felt safe, are there any huge database optimization papers? Anything I should be looking at?
Here is the relavent my.cnf entries:
set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = thread_concurrency=8
[mysql] no-auto-rehash
[isamchk] set-variable = key_buffer=128M set-variable = sort_buffer=128M set-variable = read_buffer=2M set-variable = write_buffer=2M
[myisamchk] set-variable = key_buffer=128M set-variable = sort_buffer=128M set-variable = read_buffer=2M set-variable = write_buffer=2M
And here is my top output:
MySQL on 1.2.3.4 (3.23.54) up 2+06:36:05 [13:10:01]
Queries: 191.5M qps: 1022 Slow: 296.0 Se/In/Up/De(%): 22/10/62/00
qps now: 147 Slow qps: 0.0 Threads: 9 ( 2/ 7) 50/00/00/00
Key Efficiency: 96.7% Bps in/out: 4.0k/ 1.6k Now in/out: 6.2k/767.7k
Any suggestions would be greatly appreciated.
Aram
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
