MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Dathan Vance Pattishall Date:November 18 2004 8:54pm
Subject:RE: Optimizing MySQL
View as plain text  
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:sbefort@stripped]
> Sent: Thursday, November 18, 2004 10:56 AM
> To: mysql@stripped
> 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_clients        247
> Aborted_connects    483
> 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_rnd                5230552
> 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_tables                        256
> Open_files                             323
> Open_streams                         0
> Opened_tables                     1022
> Questions                         30428972
> Qcache_queries_in_cache        0
> Qcache_inserts                        0
> Qcache_hits                             0
> Qcache_lowmem_prunes            0
> 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_join            0
> 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_immediate        31443397
> Table_locks_waited             20243
> Threads_cached                 4
> Threads_created                 2423
> Threads_connected            5
> 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

Thread
Optimizing MySQLShaun18 Nov
  • RE: Optimizing MySQLDathan Vance Pattishall18 Nov
    • Re: Optimizing MySQLShaun18 Nov
      • Re: Optimizing MySQLUgo Bellavance18 Nov
    • Re: Optimizing MySQLShaun18 Nov
  • Re: Optimizing MySQLSasha Pachev18 Nov