Re: optimizing mySQL
Hello Chris, Chris [EMAIL PROTECTED] writes: Surendra Singhi wrote: I am using mySQL 5.0 and I have 2 tables with few hundred millions of records. To optimize things, I am using MyISAM tables, using the smallest possible data type and have set indexes. Now, the problem which I am facing is that mySql process is wasting lot of time in disk access the CPU and memory utilization is very low. Most of the table access are simple queries, with very few write operations. What can I do optimize things and make queries faster? My CPU is 64bit Athlon 3000, with 950 MB of RAM running Ubuntu Edgy Eft. I don't mind making mysql eat lot of CPU and RAM. Just want things to be much faster, and loaded into memory instead of slow disk access. The my.cnf file contains: key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K query_cache_limit = 1048576 query_cache_size= 16777216 query_cache_type= 1 [isamchk] key_buffer = 16M I am considering increasing these values, please advise on what should I set them. Also, any other tips will be extremely helpful. Completely depends on the queries you are running and the context of which they are run. A query that runs once a day won't matter if it's slow, a query that runs every time you do something will matter a lot. This page might give you some ideas: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ Enable the slow query log in mysql and triple check that you have the proper indexes in place. Thanks a lot, this link was indeed helpful, and I think I am able to optimize the database much better now. I have an update operation where I am able to update 10 million records in approx 2.5 mins. But when I tried to do the same update on say 40-50 million records, mysql takes forever to finish. Its the same table, and same update operation, i am just changing the range of ids using where clause. Why might this be happening, and how to solve this? Also, when a query takes too long I kill it by shutting down the server, is this safe or is there a better alternative (MyISAM tables)? Thanks. -- Surendra Singhi http://ssinghi.kreeti.com, http://www.kreeti.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimizing mySQL
Also, when a query takes too long I kill it by shutting down the server, is this safe or is there a better alternative (MyISAM tables)? Use show processlist to see the query that taking more time and use kill processid; for terminating the query . -- Praj Surendra Singhi wrote: Hello Chris, Chris [EMAIL PROTECTED] writes: Surendra Singhi wrote: I am using mySQL 5.0 and I have 2 tables with few hundred millions of records. To optimize things, I am using MyISAM tables, using the smallest possible data type and have set indexes. Now, the problem which I am facing is that mySql process is wasting lot of time in disk access the CPU and memory utilization is very low. Most of the table access are simple queries, with very few write operations. What can I do optimize things and make queries faster? My CPU is 64bit Athlon 3000, with 950 MB of RAM running Ubuntu Edgy Eft. I don't mind making mysql eat lot of CPU and RAM. Just want things to be much faster, and loaded into memory instead of slow disk access. The my.cnf file contains: key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K query_cache_limit = 1048576 query_cache_size= 16777216 query_cache_type= 1 [isamchk] key_buffer = 16M I am considering increasing these values, please advise on what should I set them. Also, any other tips will be extremely helpful. Completely depends on the queries you are running and the context of which they are run. A query that runs once a day won't matter if it's slow, a query that runs every time you do something will matter a lot. This page might give you some ideas: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ Enable the slow query log in mysql and triple check that you have the proper indexes in place. Thanks a lot, this link was indeed helpful, and I think I am able to optimize the database much better now. I have an update operation where I am able to update 10 million records in approx 2.5 mins. But when I tried to do the same update on say 40-50 million records, mysql takes forever to finish. Its the same table, and same update operation, i am just changing the range of ids using where clause. Why might this be happening, and how to solve this? Also, when a query takes too long I kill it by shutting down the server, is this safe or is there a better alternative (MyISAM tables)? Thanks.
Re: optimizing mySQL
I have an update operation where I am able to update 10 million records in approx 2.5 mins. But when I tried to do the same update on say 40-50 million records, mysql takes forever to finish. Its the same table, and same update operation, i am just changing the range of ids using where clause. Why might this be happening, and how to solve this? Sounds like you're either killing the disk or cpu. Have you tuned the filesystem using something like hdparm? vmstat/top will tell you what's going on here. Also, when a query takes too long I kill it by shutting down the server, is this safe or is there a better alternative (MyISAM tables)? As someone else mentioned open a new mysql connection and do show processlist; kill id; though I'm not sure what will happen when it's doing an update :/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimizing mySQL
Hi, I am using mySQL 5.0 and I have 2 tables with few hundred millions of records. To optimize things, I am using MyISAM tables, using the smallest possible data type and have set indexes. Now, the problem which I am facing is that mySql process is wasting lot of time in disk access the CPU and memory utilization is very low. Most of the table access are simple queries, with very few write operations. What can I do optimize things and make queries faster? My CPU is 64bit Athlon 3000, with 950 MB of RAM running Ubuntu Edgy Eft. I don't mind making mysql eat lot of CPU and RAM. Just want things to be much faster, and loaded into memory instead of slow disk access. The my.cnf file contains: key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K query_cache_limit = 1048576 query_cache_size= 16777216 query_cache_type= 1 [isamchk] key_buffer = 16M I am considering increasing these values, please advise on what should I set them. Also, any other tips will be extremely helpful. Thanks a lot. -- Surendra Singhi http://ssinghi.kreeti.com, http://www.kreeti.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimizing mySQL
Surendra Singhi wrote: Hi, I am using mySQL 5.0 and I have 2 tables with few hundred millions of records. To optimize things, I am using MyISAM tables, using the smallest possible data type and have set indexes. Now, the problem which I am facing is that mySql process is wasting lot of time in disk access the CPU and memory utilization is very low. Most of the table access are simple queries, with very few write operations. What can I do optimize things and make queries faster? My CPU is 64bit Athlon 3000, with 950 MB of RAM running Ubuntu Edgy Eft. I don't mind making mysql eat lot of CPU and RAM. Just want things to be much faster, and loaded into memory instead of slow disk access. The my.cnf file contains: key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K query_cache_limit = 1048576 query_cache_size= 16777216 query_cache_type= 1 [isamchk] key_buffer = 16M I am considering increasing these values, please advise on what should I set them. Also, any other tips will be extremely helpful. Completely depends on the queries you are running and the context of which they are run. A query that runs once a day won't matter if it's slow, a query that runs every time you do something will matter a lot. This page might give you some ideas: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ Enable the slow query log in mysql and triple check that you have the proper indexes in place. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing mysql client/server interactions
Hi All, Our current setup has the mysql server and the client app run on the same host. We are using mysql++ (ports/mysql++-1.7.9) for the database client api. The application returns huge data sets in the order of 50K. When we do a show processlist most of the threads spend their time in the state sending data. With the current setup data seems to go up and down the network stack for sending data to a client on the same host. Can this be short circuited somehow for better performance. Thanx Alex
Optimizing MySQL
Hello, I was wondering if a more knowledgeable person could help me out with my configuration and let me know how I could further optimize MySQL. Here's the hardware on my dedicated server: Processor #1 Vendor: GenuineIntel Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.40GHz Processor #1 speed: 2400.152 MHz Processor #1 cache size: 1024 KB Memory 512 MB Here's what I get for the command SHOW STATUS: Aborted_clients247 Aborted_connects483 Bytes_received 531539854 Bytes_sent 503095410 Connections 450758 Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Delayed_insert_threads 0 Delayed_writes 0 Delayed_errors 0 Flush_commands 1 Handler_commit 0 Handler_delete 27837 Handler_read_first 670529 Handler_read_key 285579436 Handler_read_next 394084433 Handler_read_prev 680815 Handler_read_rnd5230552 Handler_read_rnd_next 58229817 Handler_rollback 0 Handler_update 384098 Handler_write 77442968 Key_blocks_used 50333 Key_read_requests 1081940322 Key_reads 45598 Key_write_requests 66458416 Key_writes 41372551 Max_used_connections 154 Not_flushed_key_blocks 0 Not_flushed_delayed_rows 0 Open_tables256 Open_files 323 Open_streams 0 Opened_tables 1022 Questions 30428972 Qcache_queries_in_cache0 Qcache_inserts0 Qcache_hits 0 Qcache_lowmem_prunes0 Qcache_not_cached 0 Qcache_free_memory 0 Qcache_free_blocks 0 Qcache_total_blocks 0 Rpl_status NULL Select_full_join 268 Select_full_range_join0 Select_range 66211 Select_range_check 0 Select_scan 151459 Slave_open_temp_tables 0 Slave_running OFF Slow_launch_threads 2 Slow_queries 15783 Sort_merge_passes 0 Sort_range 476962 Sort_rows 5241809 Sort_scan 283556 Table_locks_immediate31443397 Table_locks_waited 20243 Threads_cached 4 Threads_created 2423 Threads_connected5 Threads_running 1 Uptime 771502 Here are the complete contents of my my.cnf file [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = max_connections=500 set-variable = ft_min_word_len=1 log-slow-queries=/var/log/slow-queries.log set-variable = long_query_time=1 safe-show-database Thanks a lot for your help! http://www.hdtv-info.org http://www.entertainment-news.org http://www.political-news.org
RE: Optimizing MySQL
Look at Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Increase tmp_table_size = 64M: it's used to stop going to disk and some internal mysql operations. Handler_read_rnd_next 58229817 Your tables are not index properly, your doing a lot of table scans. Your biggest perf. Gain will come from changing your schema and or optimizing your queries. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Shaun [mailto:[EMAIL PROTECTED] Sent: Thursday, November 18, 2004 10:56 AM To: [EMAIL PROTECTED] Subject: Optimizing MySQL Hello, I was wondering if a more knowledgeable person could help me out with my configuration and let me know how I could further optimize MySQL. Here's the hardware on my dedicated server: Processor #1 Vendor: GenuineIntel Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.40GHz Processor #1 speed: 2400.152 MHz Processor #1 cache size: 1024 KB Memory 512 MB Here's what I get for the command SHOW STATUS: Aborted_clients247 Aborted_connects483 Bytes_received 531539854 Bytes_sent 503095410 Connections 450758 Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Delayed_insert_threads 0 Delayed_writes 0 Delayed_errors 0 Flush_commands 1 Handler_commit 0 Handler_delete 27837 Handler_read_first 670529 Handler_read_key 285579436 Handler_read_next 394084433 Handler_read_prev 680815 Handler_read_rnd5230552 Handler_read_rnd_next 58229817 Handler_rollback 0 Handler_update 384098 Handler_write 77442968 Key_blocks_used 50333 Key_read_requests 1081940322 Key_reads 45598 Key_write_requests 66458416 Key_writes 41372551 Max_used_connections 154 Not_flushed_key_blocks 0 Not_flushed_delayed_rows 0 Open_tables256 Open_files 323 Open_streams 0 Opened_tables 1022 Questions 30428972 Qcache_queries_in_cache0 Qcache_inserts0 Qcache_hits 0 Qcache_lowmem_prunes0 Qcache_not_cached 0 Qcache_free_memory 0 Qcache_free_blocks 0 Qcache_total_blocks 0 Rpl_status NULL Select_full_join 268 Select_full_range_join0 Select_range 66211 Select_range_check 0 Select_scan 151459 Slave_open_temp_tables 0 Slave_running OFF Slow_launch_threads 2 Slow_queries 15783 Sort_merge_passes 0 Sort_range 476962 Sort_rows 5241809 Sort_scan 283556 Table_locks_immediate31443397 Table_locks_waited 20243 Threads_cached 4 Threads_created 2423 Threads_connected5 Threads_running 1 Uptime 771502 Here are the complete contents of my my.cnf file [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = max_connections=500 set-variable = ft_min_word_len=1 log-slow-queries=/var/log/slow-queries.log set-variable = long_query_time=1 safe-show-database Thanks a lot for your help! http://www.hdtv-info.org http://www.entertainment-news.org http://www.political-news.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing MySQL
Shaun wrote: Hello, I was wondering if a more knowledgeable person could help me out with my configuration and let me know how I could further optimize MySQL. Here's the hardware on my dedicated server: Processor #1 Vendor: GenuineIntel Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.40GHz Processor #1 speed: 2400.152 MHz Processor #1 cache size: 1024 KB Memory 512 MB Here's what I get for the command SHOW STATUS: Shaun: Once every 5 seconds on average you run a select that does a full scan which on average examines about 350-400 rows. Once every 2 seconds or so you have a query that creates a temporary table. Enable log-long-format and try to figure out what those queries are. See if you can optimize the ones that are scanning a lot of rows by adding a key, or by convincing your developers to re-write them. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing MySQL
Thanks for your help Dathan, I will make the required changes. I just have one other problem. I'm not sure what queries don't use an index. I've attmepted to turn on the slow-queries-log, but nothing ever shows up in the file. Here's what I have in the my.cnf [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = max_connections=500 set-variable = ft_min_word_len=1 long_query_time=1 log-long-format log-slow-queries = /var/log/slow.log Do you have any idea why my slow query log isn't working? Do I have something wrong in my syntax? I'm using MySQL 4.0. Thanks for your help, I really appreciate it. http://www.hdtv-info.org http://www.entertainment-news.org http://www.political-news.org Look at Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Increase tmp_table_size = 64M: it's used to stop going to disk and some internal mysql operations. Handler_read_rnd_next 58229817 Your tables are not index properly, your doing a lot of table scans. Your biggest perf. Gain will come from changing your schema and or optimizing your queries. DVP Dathan Vance Pattishall http://www.friendster.com Hello, I was wondering if a more knowledgeable person could help me out with my configuration and let me know how I could further optimize MySQL. Here's the hardware on my dedicated server: Processor #1 Vendor: GenuineIntel Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.40GHz Processor #1 speed: 2400.152 MHz Processor #1 cache size: 1024 KB Memory 512 MB Here's what I get for the command SHOW STATUS: Aborted_clients247 Aborted_connects483 Bytes_received 531539854 Bytes_sent 503095410 Connections 450758 Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Delayed_insert_threads 0 Delayed_writes 0 Delayed_errors 0 Flush_commands 1 Handler_commit 0 Handler_delete 27837 Handler_read_first 670529 Handler_read_key 285579436 Handler_read_next 394084433 Handler_read_prev 680815 Handler_read_rnd5230552 Handler_read_rnd_next 58229817 Handler_rollback 0 Handler_update 384098 Handler_write 77442968 Key_blocks_used 50333 Key_read_requests 1081940322 Key_reads 45598 Key_write_requests 66458416 Key_writes 41372551 Max_used_connections 154 Not_flushed_key_blocks 0 Not_flushed_delayed_rows 0 Open_tables256 Open_files 323 Open_streams 0 Opened_tables 1022 Questions 30428972 Qcache_queries_in_cache0 Qcache_inserts0 Qcache_hits 0 Qcache_lowmem_prunes0 Qcache_not_cached 0 Qcache_free_memory 0 Qcache_free_blocks 0 Qcache_total_blocks 0 Rpl_status NULL Select_full_join 268 Select_full_range_join0 Select_range 66211 Select_range_check 0 Select_scan 151459 Slave_open_temp_tables 0 Slave_running OFF Slow_launch_threads 2 Slow_queries 15783 Sort_merge_passes 0 Sort_range 476962 Sort_rows 5241809 Sort_scan 283556 Table_locks_immediate31443397 Table_locks_waited 20243 Threads_cached 4 Threads_created 2423 Threads_connected5 Threads_running 1 Uptime 771502 Here are the complete contents of my my.cnf file [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = max_connections=500 set-variable = ft_min_word_len=1 log-slow-queries=/var/log/slow-queries.log set-variable = long_query_time=1 safe-show-database Thanks a lot for
Re: Optimizing MySQL
Shaun wrote: Thanks for your help Dathan, I will make the required changes. I just have one other problem. I'm not sure what queries don't use an index. I've attmepted to turn on the slow-queries-log, but nothing ever shows up in the file. Here's what I have in the my.cnf [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = max_connections=500 set-variable = ft_min_word_len=1 long_query_time=1 log-long-format log-slow-queries = /var/log/slow.log Do you have any idea why my slow query log isn't working? Can the mysql user write to this file? --log-long-format should show you queries that don't use an index. Do I have something wrong in my syntax? I'm using MySQL 4.0. Thanks for your help, I really appreciate it. http://www.hdtv-info.org http://www.entertainment-news.org http://www.political-news.org Look at Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Increase tmp_table_size = 64M: it's used to stop going to disk and some internal mysql operations. Handler_read_rnd_next 58229817 Your tables are not index properly, your doing a lot of table scans. Your biggest perf. Gain will come from changing your schema and or optimizing your queries. DVP Dathan Vance Pattishall http://www.friendster.com Hello, I was wondering if a more knowledgeable person could help me out with my configuration and let me know how I could further optimize MySQL. Here's the hardware on my dedicated server: Processor #1 Vendor: GenuineIntel Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.40GHz Processor #1 speed: 2400.152 MHz Processor #1 cache size: 1024 KB Memory 512 MB Here's what I get for the command SHOW STATUS: Aborted_clients247 Aborted_connects483 Bytes_received 531539854 Bytes_sent 503095410 Connections 450758 Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Delayed_insert_threads 0 Delayed_writes 0 Delayed_errors 0 Flush_commands 1 Handler_commit 0 Handler_delete 27837 Handler_read_first 670529 Handler_read_key 285579436 Handler_read_next 394084433 Handler_read_prev 680815 Handler_read_rnd5230552 Handler_read_rnd_next 58229817 Handler_rollback 0 Handler_update 384098 Handler_write 77442968 Key_blocks_used 50333 Key_read_requests 1081940322 Key_reads 45598 Key_write_requests 66458416 Key_writes 41372551 Max_used_connections 154 Not_flushed_key_blocks 0 Not_flushed_delayed_rows 0 Open_tables256 Open_files 323 Open_streams 0 Opened_tables 1022 Questions 30428972 Qcache_queries_in_cache0 Qcache_inserts0 Qcache_hits 0 Qcache_lowmem_prunes0 Qcache_not_cached 0 Qcache_free_memory 0 Qcache_free_blocks 0 Qcache_total_blocks 0 Rpl_status NULL Select_full_join 268 Select_full_range_join0 Select_range 66211 Select_range_check 0 Select_scan 151459 Slave_open_temp_tables 0 Slave_running OFF Slow_launch_threads 2 Slow_queries 15783 Sort_merge_passes 0 Sort_range 476962 Sort_rows 5241809 Sort_scan 283556 Table_locks_immediate31443397 Table_locks_waited 20243 Threads_cached 4 Threads_created 2423 Threads_connected5 Threads_running 1 Uptime 771502 Here are the complete contents of my my.cnf file [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = max_connections=500 set-variable = ft_min_word_len=1 log-slow-queries=/var/log/slow-queries.log set-variable = long_query_time=1 safe-show-database Thanks a lot for your help! http://www.hdtv-info.org http://www.entertainment-news.org http://www.political-news.org -- MySQL
Re: Optimizing MySQL
Thanks, I changed the permissions, restarted, and everything is working great now. Thanks a lot. Shaun wrote: Thanks for your help Dathan, I will make the required changes. I just have one other problem. I'm not sure what queries don't use an index. I've attmepted to turn on the slow-queries-log, but nothing ever shows up in the file. Here's what I have in the my.cnf [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = max_connections=500 set-variable = ft_min_word_len=1 long_query_time=1 log-long-format log-slow-queries = /var/log/slow.log Do you have any idea why my slow query log isn't working? Can the mysql user write to this file? --log-long-format should show you queries that don't use an index. Do I have something wrong in my syntax? I'm using MySQL 4.0. Thanks for your help, I really appreciate it. http://www.hdtv-info.org http://www.entertainment-news.org http://www.political-news.org Look at Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Increase tmp_table_size = 64M: it's used to stop going to disk and some internal mysql operations. Handler_read_rnd_next 58229817 Your tables are not index properly, your doing a lot of table scans. Your biggest perf. Gain will come from changing your schema and or optimizing your queries. DVP Dathan Vance Pattishall http://www.friendster.com Hello, I was wondering if a more knowledgeable person could help me out with my configuration and let me know how I could further optimize MySQL. Here's the hardware on my dedicated server: Processor #1 Vendor: GenuineIntel Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.40GHz Processor #1 speed: 2400.152 MHz Processor #1 cache size: 1024 KB Memory 512 MB Here's what I get for the command SHOW STATUS: Aborted_clients247 Aborted_connects483 Bytes_received 531539854 Bytes_sent 503095410 Connections 450758 Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Delayed_insert_threads 0 Delayed_writes 0 Delayed_errors 0 Flush_commands 1 Handler_commit 0 Handler_delete 27837 Handler_read_first 670529 Handler_read_key 285579436 Handler_read_next 394084433 Handler_read_prev 680815 Handler_read_rnd5230552 Handler_read_rnd_next 58229817 Handler_rollback 0 Handler_update 384098 Handler_write 77442968 Key_blocks_used 50333 Key_read_requests 1081940322 Key_reads 45598 Key_write_requests 66458416 Key_writes 41372551 Max_used_connections 154 Not_flushed_key_blocks 0 Not_flushed_delayed_rows 0 Open_tables256 Open_files 323 Open_streams 0 Opened_tables 1022 Questions 30428972 Qcache_queries_in_cache0 Qcache_inserts0 Qcache_hits 0 Qcache_lowmem_prunes0 Qcache_not_cached 0 Qcache_free_memory 0 Qcache_free_blocks 0 Qcache_total_blocks 0 Rpl_status NULL Select_full_join 268 Select_full_range_join0 Select_range 66211 Select_range_check 0 Select_scan 151459 Slave_open_temp_tables 0 Slave_running OFF Slow_launch_threads 2 Slow_queries 15783 Sort_merge_passes 0 Sort_range 476962 Sort_rows 5241809 Sort_scan 283556 Table_locks_immediate31443397 Table_locks_waited 20243 Threads_cached 4 Threads_created 2423 Threads_connected5 Threads_running 1 Uptime 771502 Here are the complete contents of my my.cnf file [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M
Re: Optimizing Mysql for large tables
Joseph, How big your table files are? Are they MyISAM or Innodb ? Do you have indexes? How much memory do you have? Is your MySQL running on a dedicated server or do you run anything else on your db server? This questions needs to be answered before suggesting anything logical. But general suggestions can be : 1 - Add indexes to your tables and use that indexes when you're selecting rows. 2 - increase key_buffer_size to 32 or 64 or even more depending on your server memory 3- Add memory to your server if you have less then 256 Mb and willing to have fast db performance. Kayra Otaner On Thu, 2003-08-21 at 13:50, Joseph Norris wrote: Group, I have been working with Mysql for about 5 years - mainly in LAMP shops. The tables have been between 20-100 thousand records size. Now I have a project where the tables are in the millions of records. This is very new to me and I am noticing that my queries are really sloww! What are the options that I have to speed my queries on the mysql side with regards to the my.cnf file. I have a fair understanding of sql optimization and I understand explain. I just want to see if there is something that I can do with the server also. Thanks to all. #Joseph Norris (Perl - what else is here?/Linux/CGI/Mysql) print @c=map chr $_+100,(6,17,15,16,-68,-3,10,11,16,4,1,14,-68,12,1,14,8, -68,4,-3,-1,7,1,14,-68,-26,11,15,1,12,4,-68,-22,11,14,14,5,15,-90); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing Mysql for large tables
Group, I have been working with Mysql for about 5 years - mainly in LAMP shops. The tables have been between 20-100 thousand records size. Now I have a project where the tables are in the millions of records. This is very new to me and I am noticing that my queries are really sloww! What are the options that I have to speed my queries on the mysql side with regards to the my.cnf file. I have a fair understanding of sql optimization and I understand explain. I just want to see if there is something that I can do with the server also. Thanks to all. #Joseph Norris (Perl - what else is here?/Linux/CGI/Mysql) print @c=map chr $_+100,(6,17,15,16,-68,-3,10,11,16,4,1,14,-68,12,1,14,8, -68,4,-3,-1,7,1,14,-68,-26,11,15,1,12,4,-68,-22,11,14,14,5,15,-90); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing mySQL and Optimizing this query
On Tue, Jan 22, 2002 at 10:12:55AM -0600, Solsberry, Glendon wrote: I have mySQL v3.23 running on a Mandrake 8.1 box (AMD 1600XP, 512MB RAM). The problem is that the main query (listed below) takes approximately 3 hours to run. [snip] Wow! Thanks for all the detail. The query is this: SELECT SQL_BIG_RESULT tblNew.username, (tblNew.wucount - tblMain.wucount), tblNew.wucount, tblMain.wucount FROM tblNew LEFT OUTER JOIN tblMain ON (DATE_SUB(tblNew.lastupdate, INTERVAL $hourssinceNoon HOUR) = tblMain.lastupdate) ORDER BY tblNew.wucount DESC Where $hourssinceNoon is the number of hours since noon yesterday. tblNew typically has approximately 4500 records in it. tblMain has records added every 4 hours, which is all the records from tblNew. Can you show us the output of EXPLAIN on that query? That may help figure this out. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 513,761,222 queries (265/sec. avg) - 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
Optimizing mySQL and Optimizing this query
I have mySQL v3.23 running on a Mandrake 8.1 box (AMD 1600XP, 512MB RAM). The problem is that the main query (listed below) takes approximately 3 hours to run. my.cnf looks like: [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=128M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= myisam_sort_buffer+size=64M set-variable= thread_cache=8 set-variable= net_buffer_length=8K log-bin server-id = 1 [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=64M set-variable= sort_buffer=64M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=64M set-variable= sort_buffer=64M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout We're using MyISAM tables. The table structure is this: tblMain - oldrank int(11) primary key username varchar(255) binary wucount bigint lastupdate datetime tblNew - uID int(11) auto_increment primary key username varchar(255) binary wucount bigint lastupdate datetime We have an index on tblMain.username. The query is this: SELECT SQL_BIG_RESULT tblNew.username, (tblNew.wucount - tblMain.wucount), tblNew.wucount, tblMain.wucount FROM tblNew LEFT OUTER JOIN tblMain ON (DATE_SUB(tblNew.lastupdate, INTERVAL $hourssinceNoon HOUR) = tblMain.lastupdate) ORDER BY tblNew.wucount DESC Where $hourssinceNoon is the number of hours since noon yesterday. tblNew typically has approximately 4500 records in it. tblMain has records added every 4 hours, which is all the records from tblNew. I was hoping anyone could help me out with some optimizations that could be done. We've already run optimize table tblMain, and that gave us almost an hours worth of performance gain. The output of SHOW STATUS is printed below as well. Any optimization ideas/techniques will be appreciated... +--++ | Variable_name| Value | +--++ | Aborted_clients | 1 | | Aborted_connects | 0 | | Bytes_received | 7277626| | Bytes_sent | 1769127| | Connections | 18 | | Created_tmp_disk_tables | 0 | | Created_tmp_tables | 178| | Created_tmp_files| 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_delete | 0 | | Handler_read_first | 8640 | | Handler_read_key | 0 | | Handler_read_next| 0 | | Handler_read_prev| 0 | | Handler_read_rnd | 13544 | | Handler_read_rnd_next| 2759592937 | | Handler_update | 0 | | Handler_write| 89261 | | Key_blocks_used | 773| | Key_read_requests| 177906 | | Key_reads| 344| | Key_write_requests | 50117 | | Key_writes | 36335 | | Max_used_connections | 1 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 3 | | Open_files | 7 | | Open_streams | 0 | | Opened_tables| 30 | | Questions| 44708 | | Select_full_join | 356| | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 367| | Slave_running| OFF| | Slave_open_temp_tables | 0 | | Slow_launch_threads | 0 | | Slow_queries | 355| | Sort_merge_passes| 0 | | Sort_range | 0 | | Sort_rows| 13544 | | Sort_scan| 178| | Table_locks_immediate| 53651 | | Table_locks_waited | 1 | | Threads_cached | 0 | | Threads_created | 2 | | Threads_connected| 2 | | Threads_running | 2 | | Uptime | 119195 | +--++ 54 rows in set (0.00 sec) Any ideas? This communication is confidential and may be legally privileged. If you are not the intended recipient, (i) please do not read or disclose to others, (ii) please notify the sender by reply mail, and (iii) please delete this communication from your system. Failure to follow this process may be unlawful. Thank
Optimizing mySQL for volume
We are trying to optimize mySQL. Right now, the max_connections is set to 100. We are only able to open 99 concurrent connections to the db, and even then, some requests are unable to get a connection. We'd like to be able to scale that up, but mySQL doesn't like what we've done, as it won't start. How do we optimize mySQL for connections? We have moved the mysqld process up on priority (renice -3 -u mysql). We have tried to: specify the max_connections variable. It's commented out (see below) because mySQL won't start with this directive. We also tried placing it in the safe_mysqld section, but it doesn't get recognized when we restart mysql and do a mysqladmin variables command. What do we need to do to increase this? We have also tried to specify the open-files-limit variable, however, this doesn't appear to be recognized either. Here is a copy of the my.cnf file we are using: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin log-update = /var/log/gitgdb.log #max_connections=250 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid open-files-limit=8192 renice -3 -u mysql Any suggestions would be greatly appreciated. Kim - 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: Optimizing mySQL for volume
On Thu, Dec 27, 2001 at 10:38:42AM -0600, Kim Albee wrote: We are trying to optimize mySQL. Right now, the max_connections is set to 100. We are only able to open 99 concurrent connections to the db, and even then, some requests are unable to get a connection. We'd like to be able to scale that up, but mySQL doesn't like what we've done, as it won't start. How do we optimize mySQL for connections? Increase the max_connections variable as described in the manual. We have moved the mysqld process up on priority (renice -3 -u mysql). Shouldn't be necessary at all. We have tried to: specify the max_connections variable. It's commented out (see below) because mySQL won't start with this directive. We also tried placing it in the safe_mysqld section, but it doesn't get recognized when we restart mysql and do a mysqladmin variables command. What do we need to do to increase this? When MySQL didn't start, what did the error log say? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 28 days, processed 596,190,310 queries (243/sec. avg) - 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: Optimizing mySQL for volume
You need to set the max_connections variable like this in the my.cnf file: set-variable = max_connections=250 -- Jason On Thu, 2001-12-27 at 11:38, Kim Albee wrote: We are trying to optimize mySQL. Right now, the max_connections is set to 100. We are only able to open 99 concurrent connections to the db, and even then, some requests are unable to get a connection. We'd like to be able to scale that up, but mySQL doesn't like what we've done, as it won't start. How do we optimize mySQL for connections? We have moved the mysqld process up on priority (renice -3 -u mysql). We have tried to: specify the max_connections variable. It's commented out (see below) because mySQL won't start with this directive. We also tried placing it in the safe_mysqld section, but it doesn't get recognized when we restart mysql and do a mysqladmin variables command. What do we need to do to increase this? We have also tried to specify the open-files-limit variable, however, this doesn't appear to be recognized either. Here is a copy of the my.cnf file we are using: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin log-update = /var/log/gitgdb.log #max_connections=250 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid open-files-limit=8192 renice -3 -u mysql Any suggestions would be greatly appreciated. Kim - 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 sql - 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
Optimizing MySQL
I have a MySQL myISAM database that has about 300 MB of information. Recently the index files became corrupted and I ran the myisamchk *.MYI -r -S to repair them. Ever since then mySQLd process has been using up a ton of processor time. And some of the queries that I run have started taking a long time in the tables that I access frequently. The database also starting growing at that time as well so I am not sure what the problem is. Anyone run into a database that seems to be running very inefficiently and figure a way to optimize it? THe indexes don't seem to be the problem. Any and all suggestions are very much appreciated. Michael Blood [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: Optimizing MySQL
Recently the index files became corrupted and I ran the myisamchk *.MYI -r -S to repair them. Try an OPTIMIZE TABLE or ANALYZE TABLE. Your queries may be using the incorrect indexes since the statistics have not been updated. You can also do this: myisamchk *.MYI -a Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - 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: Optimizing MySQL
I'm not an expert, but I've never heard of a db getting slower after an index rebuild. It's possible that's just a red herring. You've been speaking generally about things being slower, I'd suggest you get specific. Track down the queries are slow now, use EXPLAIN on them to see what indexes that are using (or not). There a way to log slow queries in MySQL, that might help. Perhaps usage has just shifted and there are now a lot of writers and readers to the same table - run mysqladmin pr and look to see if some servers are waiting due to table locks. mysqladmin -i3 pr is good to do continuous monitoring. -bill - 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: Optimizing MySQL
I have used optimize table on several tables however it has not affected the speed of the queries or the large amount of processor resources that are being used. Have you heard of a way to rebuild a table more efficiently? Go back and do this: myisamchk -o *MYI myisamchk -a *MYI myisamchk -S *MYI myisamchk -R1 *MYI Do them as sepparate commands. It will take a while. Look for messages from myisamchk about the tables. Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - 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
Optimizing Mysql for CGI Search Engine
Hi all, I have been using Mysql to power my online database search engine(CGI). IT contains more than 500K rows of records(12 columns). Is there a trick to optimize Mysql Table( by using Index , temporary table??) so that the search engine could get the result in shorter time? Should I consider using Java to replace existing CGI?? I appreciated if any one of you could provide a little suggestion, trick, or online tutorial. Have a Nice day :-) Tanpy
