Here how I created the table:
CREATE TABLE syslogTB (
facility char(10), # OPTIONAL field for facility
priority char(10), # OPTIONAL field for priority
date date, # date of this log message
time time, # time of this message
host varchar(128), # host logging, If you have a host with
# 128 characters you probably
# have other issues to worry about than
#someone being l33t. 8-)
message text,
INDEX host_index (host),
INDEX date_index (date),
INDEX message_index (message (50)) , #Index the first 50 characters
seq int unsigned auto_increment primary key # optional sequencenumber
);
And here is the query
SELECT host, date, time, message FROM syslogTB WHERE host = '$host' AND
date LIKE '%$date%' AND message LIKE '%$message%' ORDER BY date
DESC,time DESC LIMIT " . $start . ", " . $numresults . "");
Thanks
Jenn
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Saturday, May 21, 2005 1:56 PM
To: Jennifer Fountain
Cc: mysql@lists.mysql.com
Subject: Re: Query Issue on Large DB
Hi,
have you a query sample + show create table <TBL> + show indexes from
<TBL> ?
Mathias
Selon Jennifer Fountain <[EMAIL PROTECTED]>:
> Hi,
> I am new to the list and mysql for that matter. I am trying to
> configure a centralized syslog server using mysql, msyslog and php.
> Works great until the DB grows to about 2GB. After the database gets
> over 2GB, running queries on a particular hostname (that has a lot of
> records) takes a long time and drains memory to the point I have to
> reboot the system. Here is the status after a single large query is
> ran:
>
> total used free shared buffers
> cached
> Mem: 3889616 3871504 18112 0 21336
> 3654652
> -/+ buffers/cache: 195516 3694100
> Swap: 4194232 0 4194232
>
>
>
> Here is a copy of my.cnf:
>
> [mysqld]
> datadir=/data
> socket=/var/lib/mysql/mysql.sock
> skip-locking
> set-variable = key_buffer_size=384M
> set-variable = max_allowed_packet=1M
> set-variable = table_cache=512
> set-variable = sort_buffer=2M
> set-variable = record_buffer=2M
> set-variable = thread_cache=8
> set-variable = read_rnd_buffer_size=2M
> # Try number of CPU's*2 for thread_concurrency
> set-variable = thread_concurrency=8
> set-variable = myisam_sort_buffer_size=64M
> log-bin
> server-id = 1
>
> [mysql.server]
> user=mysql
> basedir=/var/lib
>
> [safe_mysqld]
> err-log=/var/log/mysqld.log
> pid-file=/var/run/mysqld/mysqld.pid
>
>
> [mysqldump]
> quick
> set-variable = max_allowed_packet=16M
>
> [mysql]
> no-auto-rehash
> # Remove the next comment character if you are not familiar with SQL
> #safe-updates
>
> [isamchk]
> set-variable = key_buffer=256M
> set-variable = sort_buffer=256M
> set-variable = read_buffer=2M
> set-variable = write_buffer=2M
>
> [myisamchk]
> set-variable = key_buffer=256M
> set-variable = sort_buffer=256M
> set-variable = read_buffer=2M
> set-variable = write_buffer=2M
>
> [mysqlhotcopy]
> interactive-timeout
>
> Any thoughts as to what I am missing? Thanks for any information!
>
> Kind Regards,
>
> Jennifer Fountain
> Systems Administrator/Security
> R&B Distribution
> 3400 E Walnut Street
> Colmar, PA 18915
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
