Write Mysql on I/O in very big?

M

moji

New member
Joined
Mar 10, 2014
Messages
3
Our VPS on a 24-hour reading and writing more than 100GB, check with atop -l -D ,
problem is Mysql, my website over 24000 page and download center.
Screenshot Monitor
jsEBs.png

Code: 
[--] Data in MyISAM tables: 44M (Tables: 165)
[--] Data in InnoDB tables: 576K (Tables: 10)
[--] Data in MEMORY tables: 0B (Tables: 2)
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 38m 41s (480K q [81.230 qps], 2K conn, TX: 975M, RX: 43M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 146.0M global + 12.2M per thread (30 max threads)
[OK] Maximum possible memory usage: 513.5M (25% of installed RAM)
[OK] Slow queries: 0% (0/480K)
[OK] Highest usage of available connections: 20% (6/30)
[OK] Key buffer size / total MyISAM indexes: 120.0M/6.0M
[OK] Key buffer hit rate: 99.9% (112M cached / 109K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 26K sorts)
[!!] Joins performed without indexes: 3403
[!!] Temporary tables created on disk: 31% (7K on disk / 22K total)
[OK] Thread cache hit rate: 99% (6 created / 2K connections)
[OK] Table cache hit rate: 49% (237 open / 482 opened)
[OK] Open file limit used: 0% (399/65K)
[OK] Table locks acquired immediately: 99% (493K immediate / 493K locks)
[OK] InnoDB buffer pool / data size: 8.0M/576.0K
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)

and my.cnf

Code: 
    [mysqld]
    port            = 2411
    socket          = /var/lib/mysql/mysql.sock
    skip-external-locking
    key_buffer_size = 120M
    max_allowed_packet = 1M
    sort_buffer_size = 4M
    read_buffer_size = 4M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 50M
    thread_cache_size = 16384
    max_connections = 30
    connect_timeout = 100
    interactive_timeout = 180
    table_cache = 1024
    wait_timeout = 180
    #query_cache_size = 64M
    query_cache_type = 0
    #query_cache_limit = 1M
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8
    thread_stack = 131072
    open_files_limit = 500
    binlog_cache_size = 4096
    log-slow-queries = 1
    long_query_time = 10
    log-slow-queries = /var/log/mysql/vj.log
    max_heap_table_size = 16M
    tmp_table_size = 32M

    [mysqld_safe]
    log-error=/var/log/mysql-error.log

    innodb_buffer_pool_size = 8M
    innodb_additional_mem_pool_size = 2M
    innodb_doublewrite = 0
    innodb_log_file_size = 5M
    innodb_log_buffer_size = 2M
    # Uncomment the following if you are using InnoDB tables
    #innodb_data_home_dir = /var/lib/mysql
    #innodb_data_file_path = ibdata1:10M;ibdata2:15M:autoextend
    #innodb_log_group_home_dir = /var/lib/mysql
    #innodb_file_per_table
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    # Set .._log_file_size to 25 % of buffer pool size
    #innodb_flush_log_at_trx_commit = 0
    #innodb_lock_wait_timeout = 50
    #innodb_file_io_threads = 4
    #innodb_flush_method = O_DIRECT
please help me!
 
If you follow the best practices for database design and the tuning techniques for SQL operations, but your database is still slowed by heavy disk I/O activity, explore these low-level techniques related to disk I/O. If the Unix top tool or the Windows Task Manager shows that the CPU usage percentage with your workload is less than 70%, your workload is probably disk-bound.

When table data is cached in the InnoDB buffer pool, it can be processed over and over by queries without requiring any disk I/O. Specify the size of the buffer pool with the innodb_buffer_pool_size option. This memory area is important enough that busy databases often specify a size approximately 80% of the amount of physical memory. For more information, see Section 8.9.1, “The InnoDB Buffer Pool”.

In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync() call (which InnoDB uses by default) and similar methods is surprisingly slow. If database write performance is an issue, conduct benchmarks with the innodb_flush_method parameter set to O_DSYNC.

When using the InnoDB storage engine on Solaris 10 for x86_64 architecture (AMD Opteron), use direct I/O for InnoDB-related files, to avoid degradation of InnoDB performance. To use direct I/O for an entire UFS file system used for storing InnoDB-related files, mount it with the forcedirectio option; see mount_ufs(1M). (The default on Solaris 10/x86_64 is not to use this option.) To apply direct I/O only to InnoDB file operations rather than the whole file system, set innodb_flush_method = O_DIRECT. With this setting, InnoDB calls directio() instead of fcntl() for I/O to data files (not for I/O to log files).

When using the InnoDB storage engine with a large innodb_buffer_pool_size value on any release of Solaris 2.6 and up and any platform (sparc/x86/x64/amd64), conduct benchmarks with InnoDB data files and log files on raw devices or on a separate direct I/O UFS file system, using the forcedirectio mount option as described earlier. (It is necessary to use the mount option rather than setting innodb_flush_method if you want direct I/O for the log files.) Users of the Veritas file system VxFS should use the convosync=direct mount option.

Do not place other MySQL data files, such as those for MyISAM tables, on a direct I/O file system. Executables or libraries must not be placed on a direct I/O file system.

If you have additional storage devices available to set up a RAID configuration or symbolic links to different disks, Section 8.11.3, “Optimizing Disk I/O” for additional low-level I/O tips.



thanks


Yebazaar
 
You must log in or register to reply here.
Back
Top