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]
