Re: Query Issue on Large DB
Hello. > 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 What version of MySQL do you use? I'm not familiar with msyslog, so what queries takes such long time? In what state does the weird queries spend their time? Please send complete output of SHOW STATUS and SHOW VARIABLES. See: http://dev.mysql.com/doc/mysql/en/show-processlist.html http://dev.mysql.com/doc/mysql/en/slow-query-log.html "Jennifer Fountain" <[EMAIL PROTECTED]> wrote: > 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 sharedbuffers=20 > cached=20 > Mem: 38896163871504 18112 0 21336=20 > 3654652=20 > -/+ buffers/cache: 1955163694100=20 > Swap: 4194232 04194232=20 > > > > Here is a copy of my.cnf: > > [mysqld] > datadir=3D/data > socket=3D/var/lib/mysql/mysql.sock > skip-locking > set-variable=3D key_buffer_size=3D384M > set-variable=3D max_allowed_packet=3D1M > set-variable=3D table_cache=3D512 > set-variable=3D sort_buffer=3D2M > set-variable=3D record_buffer=3D2M > set-variable=3D thread_cache=3D8 > set-variable=3D read_rnd_buffer_size=3D2M > # Try number of CPU's*2 for thread_concurrency > set-variable=3D thread_concurrency=3D8 > set-variable=3D myisam_sort_buffer_size=3D64M > log-bin > server-id =3D 1=20 > > [mysql.server] > user=3Dmysql > basedir=3D/var/lib > > [safe_mysqld] > err-log=3D/var/log/mysqld.log > pid-file=3D/var/run/mysqld/mysqld.pid > > > [mysqldump] > quick > set-variable=3D max_allowed_packet=3D16M > > [mysql] > no-auto-rehash > # Remove the next comment character if you are not familiar with SQL > #safe-updates > > [isamchk] > set-variable=3D key_buffer=3D256M > set-variable=3D sort_buffer=3D256M > set-variable=3D read_buffer=3D2M > set-variable=3D write_buffer=3D2M > > [myisamchk] > set-variable=3D key_buffer=3D256M > set-variable=3D sort_buffer=3D256M > set-variable=3D read_buffer=3D2M > set-variable=3D write_buffer=3D2M > > [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=20 > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Issue on Large DB
Hi Jenn, I suggest you use of this query : SELECT host, date, time, message FROM syslogTB WHERE host = '$host' ORDER BY date DESC,time DESC LIMIT " . $start . ", " . $numresults . ""); at the second stage, try adding an index date_time on syslogTB(date DESC,time DESC, host); Mathias Selon Jennifer Fountain <[EMAIL PROTECTED]>: > 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 + show indexes from > ? > > 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 sharedbuffers > > cached > > Mem: 38896163871504 18112 0 21336 > > 3654652 > > -/+ buffers/cache: 1955163694100 > > Swap: 4194232 04194232 > > > > > > > > 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]
RE: Query Issue on Large DB
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 + show indexes from ? 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 sharedbuffers > cached > Mem: 38896163871504 18112 0 21336 > 3654652 > -/+ buffers/cache: 1955163694100 > Swap: 4194232 04194232 > > > > 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]
Re: Query Issue on Large DB
Hi, have you a query sample + show create table + show indexes from ? 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 sharedbuffers > cached > Mem: 38896163871504 18112 0 21336 > 3654652 > -/+ buffers/cache: 1955163694100 > Swap: 4194232 04194232 > > > > 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]
Query Issue on Large DB
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 sharedbuffers cached Mem: 38896163871504 18112 0 21336 3654652 -/+ buffers/cache: 1955163694100 Swap: 4194232 04194232 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]
