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);
?>
