Hi,
I have a database that inserts syslog data at a rate of roughly 1-2 million rows per day.
When I first hit my search page (php) it's taking about 30 seconds to calculate the hosts.
This server currently takes in syslog data at about 5-10 messages per second for ~2500 hosts.
Eventually, this server (or one with better hardware) may need to handle up to 30,000 hosts (60-100 mps)
Can mysql handle this or should I look at other alternatives now?
As for the immediate needs, I know there's a way to make this faster, but don't know how.
###################
# Here's the php query:
###################
$hostarray = array();
$sql="select distinct host from ".LOGTABLEBASENAME.$logFil
eExt;
$queryresult = perform_query($sql);
while ($line = mysql_fetch_array($queryre
sult, MYSQL_ASSOC)) {
array_push($hostarray, $line[host]);
}
###################
# Here's my my.cnf:
###################
set-variable = innodb_log_files_in_group=
2
# see the innodb config docs, the other options are not always safe
innodb_flush_log_at_trx_co
mmit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
###################
# Top output
###################
top - 04:25:53 up 3 days, 21:52, 3 users, load average: 0.64, 0.49, 0.21
Tasks: 75 total, 2 running, 73 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.7% us, 0.3% sy, 0.0% ni, 97.0% id, 2.0% wa, 0.0% hi, 0.0% si
Mem: 1033652k total, 1020544k used, 13108k free, 1228k buffers
Swap: 2939884k total, 1756416k used, 1183468k free, 338804k cached
###################
# Mytop output
###################
MySQL on localhost (4.1.14-log) up 0+17:53:34 [04:26:32]
Queries: 383.7k qps: 6 Slow: 11.0 Se/In/Up/De(%): 07/81/02/01
qps now: 8 Slow qps: 0.0 Threads: 10 ( 1/ 0) 00/95/00/00
Cache Hits: 16.6k Hits/s: 0.3 Hits now: 0.0 Ratio: 61.5% Ratio now: 0.0%
Key Efficiency: 99.4% Bps in/out: 1.9k/15.0k Now in/out: 3.0k/ 1.1k
###################
# SQL Table:
###################
DROP TABLE IF EXISTS `logs`;
CREATE TABLE `logs` (
`host` varchar(32) default NULL,
`facility` varchar(10) default NULL,
`priority` varchar(10) default NULL,
`level` varchar(10) default NULL,
`tag` varchar(10) default NULL,
`datetime` datetime default NULL,
`program` varchar(15) default NULL,
`msg` text,
`seq` bigint(20) unsigned NOT NULL auto_increment,
PRIMARY KEY (`seq`),
KEY `host` (`host`),
KEY `program` (`program`),
KEY `datetime` (`datetime`),
KEY `priority` (`priority`),
KEY `facility` (`facility`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
###################
# Memory info:
###################
cat /proc/meminfo
MemTotal: 1033652 kB
MemFree: 13232 kB
Buffers: 18120 kB
Cached: 306128 kB
SwapCached: 21804 kB
Active: 687468 kB
Inactive: 297868 kB
HighTotal: 129476 kB
HighFree: 120 kB
LowTotal: 904176 kB
LowFree: 13112 kB
SwapTotal: 2939884 kB
SwapFree: 1193744 kB
Dirty: 42324 kB
Writeback: 0 kB
Mapped: 659960 kB
Slab: 24768 kB
CommitLimit: 3456708 kB
Committed_AS: 2532236 kB
PageTables: 3076 kB
VmallocTotal: 114680 kB
VmallocUsed: 4472 kB
VmallocChunk: 109648 kB
###################
# CPU Info
###################
cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Pentium(R) 4 CPU 2.00GHz
stepping : 7
cpu MHz : 2000.324
cache size : 512 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid
bogomips : 3956.73
###################
# END
###################
