Can't get MySQL to use available memory (performance very slow)
Hi there, I'm having trouble configuring MySQL 3.23.36 running on a Sparc/SunOS 5.8 to use the available 1 gig of memory and was wondering if anyone might be able to help find a solution. /usr/ucb/ps -aux gives the stats: USER PID %CPU %MEM SZ RSS TT SSTART TIME COMMAND root 2120 36.6 7.044881669800 ?O Sep 11 16:46 /u01/opt/MySQL/lib when a single user is running a simple query like: SELECT count(id) FROM ind WHERE cust=1 AND email''; This query takes 8.29 seconds to run and there are less than 200,000 records in the ind table. Different queries don't change the percentage of memory being used, only the CPU usage seems to change. I tried adding an index to the ind table for the cust and email fields, but the query took even longer since about 175,000 records have nonempty email fields. mysqld is using the huge.cnf configuration file for 1G-2G systems (settings from file are listed at end of this message) I've also played around with tweaking command line settings via the safe_mysqld commands suggested via http://www.mysql.com/doc/S/e/Server_parameters.html such as shell safe_mysqld -O key_buffer=64M -O table_cache=256 \ -O sort_buffer=4M -O record_buffer=1M with little or no improvement. Can someone explain why MySQL isn't using more memory or point me in the right direction to improve MySQL's performance? Many thanks in advance, Joe Kaiping == cnf file settings: == [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=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= thread_concurrency=4 # Try number of CPU's*2 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 1 [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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't get MySQL to use available memory (performance very slow)
In the last episode (Sep 12), Joe Kaiping said: Hi there, I'm having trouble configuring MySQL 3.23.36 running on a Sparc/SunOS 5.8 to use the available 1 gig of memory and was wondering if anyone might be able to help find a solution. /usr/ucb/ps -aux gives the stats: USER PID %CPU %MEM SZ RSS TT SSTART TIME COMMAND root 2120 36.6 7.044881669800 ?O Sep 11 16:46 /u01/opt/MySQL/lib when a single user is running a simple query like: SELECT count(id) FROM ind WHERE cust=1 AND email''; This query takes 8.29 seconds to run and there are less than 200,000 records in the ind table. Different queries don't change the percentage of memory being used, only the CPU usage seems to change. Mysql does not cache table data for the MyISAM type, so you won't see any memory change there. What does an EXPLAIN SELECT.. print for the above query? I tried adding an index to the ind table for the cust and email fields, but the query took even longer since about 175,000 records have nonempty email fields. A single 2-column index, or two separate indexes? Mysql can only use one index per query, so a compound index would help the most here. If your problem truly is disk I/O, you can try using the InnoDB table type, which caches both index and table data in memory. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Can't get MySQL to use available memory (performance very slow)
Thanks for the quick reply, Dan. The EXPLAIN SELECT for that SELECT outputs: mysql EXPLAIN SELECT count(id) FROM ind WHERE cust=1 AND email''; +---+--+---+--+-+--++--- -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--++--- -+ | ind | ALL | NULL | NULL |NULL | NULL | 181831 | where used | +---+--+---+--+-+--++--- -+ I first tried using a 2 field index with slower results and then tried using separate single field indexes, but that was also slower than not using any index. The InnoDB table type sounds promising, so I'll start looking into that. If anyone else has other suggestions, they really are most appreciated!! Thanks again, Joe -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 12, 2001 9:17 AM To: Joe Kaiping Cc: [EMAIL PROTECTED] Subject: Re: Can't get MySQL to use available memory (performance very slow) In the last episode (Sep 12), Joe Kaiping said: Hi there, I'm having trouble configuring MySQL 3.23.36 running on a Sparc/SunOS 5.8 to use the available 1 gig of memory and was wondering if anyone might be able to help find a solution. /usr/ucb/ps -aux gives the stats: USER PID %CPU %MEM SZ RSS TT SSTART TIME COMMAND root 2120 36.6 7.044881669800 ?O Sep 11 16:46 /u01/opt/MySQL/lib when a single user is running a simple query like: SELECT count(id) FROM ind WHERE cust=1 AND email''; This query takes 8.29 seconds to run and there are less than 200,000 records in the ind table. Different queries don't change the percentage of memory being used, only the CPU usage seems to change. Mysql does not cache table data for the MyISAM type, so you won't see any memory change there. What does an EXPLAIN SELECT.. print for the above query? I tried adding an index to the ind table for the cust and email fields, but the query took even longer since about 175,000 records have nonempty email fields. A single 2-column index, or two separate indexes? Mysql can only use one index per query, so a compound index would help the most here. If your problem truly is disk I/O, you can try using the InnoDB table type, which caches both index and table data in memory. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Can't get MySQL to use available memory (performance very slow)
I believe the answer will be for you to create a single index on *just* the cust column. (I assume that a specific cust id occurs only once or a few times in the whole table.) This will allow MySQL to use that index to find the small set of records (quickly) where cust=1, then to screen the email value against that set of records. Cheers, Kent query,database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
