Is tat query is the problem ? Then turn on your slow queies and try optimizing those slow queries ?
Post your queries and table description for further help :) --Praj On Wed, 29 Mar 2006 12:33:20 -0500 "Jacob, Raymond A Jr" <[EMAIL PROTECTED]> wrote: > > After a 23days of running mysql, I have a 3GB database. When I use an > application > called base(v.1.2.2) a web based intrusion detection analysis console, the > mysqld utilization > shoots up to over 90% and stays there until the application times out or is > terminated. > > Question: Have I made some error in configuration? > > When I don't run the application base, mysqld utilization is between 30-50%. > Question: What hardware do I need to speed up queries? > > Question: How do determine if the query is the problem? > > Data: > I used my-large.cnf as the basis of my.cnf. > > Hardware and OS info: > ... > FreeBSD 6.0-RELEASE-p5 #0: > ... > CPU: Intel Pentium III (997.46-MHz 686-class CPU) > Origin = "GenuineIntel" Id = 0x68a Stepping = 10 > > Features=0x383fbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,MMX,FXSR,SSE> > real memory = 1073676288 (1023 MB) > avail memory = 1041784832 (993 MB) > > > Observations: > Disk Space used: > du -am /var/db/mysql | sort -nr | head -20 > 5259 mysql/ > 3055 mysql/snort > 2184 mysql/snort_archive > 1546 mysql/snort_archive/data.MYD > 1546 mysql/snort/data.MYD > 560 mysql/snort/acid_event.MYI > 311 mysql/snort/acid_event.MYD > 132 mysql/snort_archive/event.MYI > 132 mysql/snort/event.MYI > 116 mysql/snort_archive/iphdr.MYI > 116 mysql/snort/iphdr.MYI > 112 mysql/snort_archive/iphdr.MYD > 112 mysql/snort/iphdr.MYD > 74 mysql/snort_archive/event.MYD > 74 mysql/snort/event.MYD > 42 mysql/snort_archive/data.MYI > 42 mysql/snort/data.MYI > 40 mysql/snort_archive/icmphdr.MYI > 40 mysql/snort/icmphdr.MYI > 35 mysql/snort_archive/icmphdr.MYD > ... > >> snort is 3GB > >> snort_archive is 2GB(snort_archive acid and base tables have not been > >> built that is why snort archive is smaller) > > When the application searches the database, the mysqld utilization goes up to > over 90% until the application > times out. > > top > last pid: 44263; load averages: 0.95, 0.89, 0.76 up 25+23:49:44 > 16:07:17 > 49 processes: 2 running, 47 sleeping > > Mem: 173M Active, 608M Inact, 186M Wired, 29M Cache, 111M Buf, 1660K Free > Swap: 2048M Total, 156K Used, 2048M Free > > > PID USERNAME THR PRI NICE SIZE RES STATE TIME WCPU COMMAND > 31890 mysql 15 20 0 103M 79032K kserel 768:38 93.46% mysqld > 49138 www 1 4 0 17432K 12848K accept 0:23 0.00% httpd > 46759 www 1 20 0 16584K 12084K lockf 0:21 0.00% httpd > 46764 www 1 4 0 16632K 12072K accept 0:21 0.00% httpd > 46763 www 1 4 0 16580K 12012K accept 0:20 0.00% httpd > 46760 www 1 4 0 17452K 12872K accept 0:19 0.00% httpd > 46762 www 1 4 0 16568K 12000K accept 0:19 0.00% httpd > 46761 www 1 4 0 16608K 12088K sbwait 0:17 0.00% httpd > 68456 www 1 4 0 16572K 11980K accept 0:17 0.00% httpd > 68457 www 1 4 0 16724K 11824K accept 0:17 0.00% httpd > 68458 www 1 4 0 16980K 11920K accept 0:17 0.00% httpd > > Processes that run in the background: > I run an update process in the background with hope that if I > process the alerts from the snort table on a regular basis.o > I won't have process a large number( 44,000) alerts first thing in the > morning. > The update process inserts records into the acid table > that result from the join of certain fields from the snort tables. > (Schema at http://www.andrew.cmu.edu/user/rdanyliw/snort/acid_db_er_v102.html > ) > > rabid# cat /var/log/base-update.2006-03-28.log > 2006-03-28, 00:05:00, Added 3808 alert(s) to the Alert cache > 2006-03-28, 01:05:00, Added 5855 alert(s) to the Alert cache > 2006-03-28, 02:05:00, Added 4096 alert(s) to the Alert cache > 2006-03-28, 03:05:00, Added 4473 alert(s) to the Alert cache > 2006-03-28, 04:05:00, Added 4378 alert(s) to the Alert cache > 2006-03-28, 05:05:00, Added 4087 alert(s) to the Alert cache > 2006-03-28, 06:05:00, Added 5163 alert(s) to the Alert cache > 2006-03-28, 07:05:00, Added 4789 alert(s) to the Alert cache > 2006-03-28, 08:05:00, Added 4411 alert(s) to the Alert cache > 2006-03-28, 09:05:00, Added 4830 alert(s) to the Alert cache > 2006-03-28, 10:05:00, Added 4739 alert(s) to the Alert cache > 2006-03-28, 11:05:00, Added 5360 alert(s) to the Alert cache > 2006-03-28, 12:05:00, Added 7305 alert(s) to the Alert cache > 2006-03-28, 13:05:00, Added 8481 alert(s) to the Alert cache > 2006-03-28, 14:05:00, Added 60731 alert(s) to the Alert cache > 2006-03-28, 15:05:00, Added 44328 alert(s) to the Alert cache > 2006-03-28, 15:50:00, Added 13742 alert(s) to the Alert cache > 2006-03-28, 15:55:00, Added 607 alert(s) to the Alert cache > 2006-03-28, 16:00:00, Added 938 alert(s) to the Alert cache > 2006-03-28, 16:05:00, Added 1348 alert(s) to the Alert cache > 2006-03-28, 16:10:02, Added 687 alert(s) to the Alert cache > 2006-03-28, 16:15:00, Added 1168 alert(s) to the Alert cache > 2006-03-28, 16:20:00, Added 1760 alert(s) to the Alert cache > 2006-03-28, 16:25:00, Added 814 alert(s) to the Alert cache > 2006-03-28, 16:30:01, Added 617 alert(s) to the Alert cache > 2006-03-28, 16:35:00, Added 1075 alert(s) to the Alert cache > 2006-03-28, 16:40:00, Added 826 alert(s) to the Alert cache > 2006-03-28, 16:45:00, Added 1885 alert(s) to the Alert cache > 2006-03-28, 16:50:00, Added 1030 alert(s) to the Alert cache > 2006-03-28, 16:55:00, Added 914 alert(s) to the Alert cache > 2006-03-28, 17:00:00, Added 753 alert(s) to the Alert cache > 2006-03-28, 17:05:00, Added 531 alert(s) to the Alert cache > rabid# > > rabid# crontab -l > > ... > */5 * * * * /usr/local/etc/base-update.sh > > rabid# cat /usr/local/etc/base-update.sh > > #!/bin/sh > Current_Date=`date '+%Y-%m-%d' ` > Current_Time=`date '+%H:%M:%S'` > echo "$Current_Date, $Current_Time, `/usr/local/bin/php > /usr/local/www/base/update.php | \ > sed 's/^.*Added/Added/;s#</FONT><br>##'`" >> > /var/log/base-update.${Current_Date}.log > rabid# > rabid# pwd > /usr/local/www/base > rabid# cat update.php > > <?php > include("base_conf.php"); > include_once("$BASE_path/includes/base_auth.inc.php"); > include_once("$BASE_path/includes/base_db.inc.php"); > include_once("$BASE_path/includes/base_output_html.inc.php"); > include_once("$BASE_path/base_common.php"); > include_once("$BASE_path/base_db_common.php"); > include_once("$BASE_path/includes/base_cache.inc.php"); > include_once("$BASE_path/includes/base_state_criteria.inc.php"); > include_once("$BASE_path/includes/base_log_error.inc.php"); > include_once("$BASE_path/includes/base_log_timing.inc.php"); > > $db = NewBASEDBConnection($DBlib_path, $DBtype); > $db_connect_method = 1; > $db->baseDBConnect($db_connect_method, $alert_dbname, $alert_host, > $alert_port, $alert_user, $alert_passw > ord); > > UpdateAlertCache($db); > ?> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
