Hi! Can you give more details on the problematic inserts you're doing (table structure, indexes and insert command) ?
Also, do you believe your queries would benefit from MySQL's query cache? Maybe it's worth upgrading to version 4 and use this feature, even if you allocate just a small amount of memory for that. []s, Sergio Salvi. On Tue, 22 Jun 2004, Aram Mirzadeh wrote: > > > 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]
