'Re: mysql performance problems.'

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: mysql performance problems.
From:       "Kishore Jalleda" <kjalleda () gmail ! com>
Date:       2006-03-31 17:12:38
Message-ID: 78aaf6710603310912o5962ae6bxea52281139a81f7c () mail ! gmail ! com
[Download RAW message or body]


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@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_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/mysql?unsub=kjalleda@gmail.com
> 
> 



[prev in list] [next in list] [prev in thread] [next in thread]
Configure | About | News | Add a list | Sponsored by KoreLogic