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 =3D "GenuineIntel" Id =3D 0x68a Stepping =3D 10
=
Features=3D0x383fbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE=
,MCA,CMOV,PAT,PSE36,MMX,FXSR,SSE>
real memory =3D 1073676288 (1023 MB)
avail memory =3D 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_v10 2.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=3D`date '+%Y-%m-%d' `
Current_Time=3D`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.ph p");
include_once("$BASE_path/includes/base_log_error.inc.php");
include_once("$BASE_path/includes/base_log_timing.inc.php");

$db =3D NewBASEDBConnection($DBlib_path, $DBtype);
$db_connect_method =3D 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/mysql?unsub=3Dgcdmg-mysql [at] m.gmane.org

Jacob, Raymond A Jr 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_v10 2.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.ph p");
> 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);
>?>
>
>
>
Jacob,
Have you turned on the slow query log ?
http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

Once you have logged slow queries, than you can run explain on them and
possible tune your indexes better.

walt

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql [at] m.gmane.org

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" <raymond.jacob [at] navy.mil> 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_v10 2.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.ph p");
> 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/mysql?unsub=gcdmg-mysql [at] m.gmane.org

------=_Part_10503_6325749.1143825158660
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

As others have suggested , turn your slow query log on in my.cnf , and set
your long-query_time, and you can view your slow queries in the *.log file
in your data dir, and then try to optimize them, you could also try mytop (
http://jeremy.zawodny.com/mysql/mytop/) , and check your queries in real
time..., also check SHOW FULL PROCESSLIST to see what state the query's are
in .....

Kishore Jalleda
http://kjalleda.googlepages.com/projects


On 3/29/06, Jacob, Raymond A Jr <raymond.jacob [at] navy.mil> 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, th=
e
> 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 =3D "GenuineIntel" Id =3D 0x68a Stepping =3D 10
>
> Features=3D0x383fbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE=
,MCA,CMOV,PAT,PSE36,MMX,FXSR,SSE>
> real memory =3D 1073676288 (1023 MB)
> avail memory =3D 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 u=
p
> 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_v10 2.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=3D`date '+%Y-%m-%d' `
> Current_Time=3D`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.ph p");
> include_once("$BASE_path/includes/base_log_error.inc.php");
> include_once("$BASE_path/includes/base_log_timing.inc.php");
>
> $db =3D NewBASEDBConnection($DBlib_path, $DBtype);
> $db_connect_method =3D 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/mysql?unsub=3Dkjalleda [at] gmail.co=
m
>
>

------=_Part_10503_6325749.1143825158660--

Folks,


I have a mysql 5.0 db with the following char sets:

mysql> show variables like '%char%';
+--------------------------+--------------------------+
| Variable_name | Value |
+--------------------------+--------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | C:\MySQL\share\charsets\ |
+--------------------------+--------------------------+


I have a column that stores currency symbols - two of which are the =
British
pound (=A3) and euro (=80) sign.

I can I export via mysqldump no with problem - the "=A3" sign appears in =
the
export file OK, although the euro is converted into an odd looking set =
of
chars

But when I import using the command line client like this: "mysql -u =
<user>
-p<pwd> <dbname> < datadump.sql", the =A3 sign and euro sign get changed =
into
"=C2=A3" and "=E2=82=AC" respectively.

I have tried forcing the encoding by using "mysql
--default-character-set=3Dlatin1 -u <user> -p<pwd> <dbname> < =
datadump.sql"
but this produces the same results.


Any ideas?


TIA - Adam


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql [at] m.gmane.org