パラメータを変えてみましたー。
で、競艇DBも多いテーブルで400万件になってることだし
ついでにデータのパーティショニングをしようとおもいます。
→時間かかるので仕掛けてから出発します。
→失敗したらDBを使用しているサービス(競艇のやつとか)は利用できません(笑)ごめんなさい。該当者の方
mysqltunerというのを発見したので使ってみました。
環境はCentOS5の吊るしの環境です。
# wget http://mysqltuner.com/mysqltuner.pl
# perl mysqltuner.pl --help
MySQLTuner 1.0.1 - MySQL High Performance Tuning Script
Bug reports, feature requests, and downloads at http://mysqltuner.com/
Maintained by Major Hayden (major@mhtx.net) - Licensed under GPL
Important Usage Guidelines:
To run the script with the default options, run the script without arguments
Allow MySQL server to run for at least 24-48 hours before trusting suggestions
Some routines may require root level privileges (script will provide warnings)
You must provide the remote server's total memory when connecting to other servers
Connection and Authentication
--host <hostname> Connect to a remote host to perform tests (default: localhost)
--socket <socket> Use a different socket for a local connection
--port <port> Port to use for connection (default: 3306)
--user <username> Username to use for authentication
--pass <password> Password to use for authentication
Performance and Reporting Options
--skipsize Don't enumerate tables and their types/sizes (default: on)
(Recommended for servers with many tables)
--checkversion Check for updates to MySQLTuner (default: don't check)
--forcemem <size> Amount of RAM installed in megabytes
--forceswap <size> Amount of swap memory configured in megabytes
Output Options:
--nogood Remove OK responses
--nobad Remove negative/suggestion responses
--noinfo Remove informational responses
--nocolor Don't print output in color
#perl mysqltuner.pl
>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.45
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 463M (Tables: 84)
[--] Data in InnoDB tables: 73M (Tables: 10)
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 5
-------- Performance Metrics -------------------------------------------------
[--] Up for: 42d 15h 22m 52s (390K q [0.106 qps], 5K conn, TX: 165M, RX: 35M)
[--] Reads / Writes: 13% / 87%
[--] Total buffers: 34.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 309.0M (25% of installed RAM)
[OK] Slow queries: 0% (216/390K)
[OK] Highest usage of available connections: 21% (21/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/278.3M
[OK] Key buffer hit rate: 96.7% (342M cached / 11M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (75 temp sorts / 13K sorts)
[OK] Temporary tables created on disk: 14% (2K on disk / 16K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 36% (64 open / 173 opened)
[OK] Open file limit used: 9% (102/1K)
[OK] Table locks acquired immediately: 99% (349K immediate / 349K locks)
[!!] InnoDB data size / buffer pool: 73.3M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-bdb to MySQL configuration to disable BDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)
innodb_buffer_pool_size (>= 73M)
というわけでmy.cnfをお勧め設定にしてみます。
競艇システムのサーバ移行ですが、文字コードでふんづまっていました。
もともとlatin1のDBに
set names utf8;
して、utf8を無理やり入れていたため、mysqldumpできちんと吐かれない・・・。
結局PHPで全テーブル吐き出すことに。。。
現在INSERT中です。
DDLとDMLで全部で1GBくらいになったので、ちょい時間かかるかな？
しかし、うまくインポートできてない予感！
＃追記
テーブルをすべてutf8に移行完了。
23：00、23：05、23：30のバッチ未実行
登録:
投稿 (Atom)
