| Posted by DotAL, 06-17-2011, 08:14 AM | | Hello,
I got a server Procesor Intel i7, 8GB RAM, 1.5 TB HDD, RAID 1..
And i need to tweak MySQL for a better performance for the server !!
How can i do it ? any help ?
Thankyou.
|
| Posted by Michaelz, 06-17-2011, 08:24 AM | | What issues are you facing?
|
| Posted by cptechie, 06-17-2011, 08:30 AM | | Hello,
Are you using cPanel? I would really suggest you to go through the below given cPanel video on mysql optimization to get a better understanding on the topic.
URL : http://www.cpanel.net/videos/mysql-optimization/
|
| Posted by BeZazz, 06-17-2011, 09:01 AM | | Do a search here at WHT for mysqltuner it should help.
|
| Posted by Geoff Winans, 06-18-2011, 03:33 PM | | sady,
MySQL configurations are unique per machine based on the workload expected and TYPE of workload.
Do you make heavy use of InnoDB?
What about MyISAM tables?
The MySQL Tuner script will -not- give you an optimal configuration.
If you don't mind posting some general info, I can give you a better configuration (I'm a MySQL DBA by profession).
Info needed:
Are most of your tables InnoDB or MyISAM?
What is the total size of each?
Is this a dedicated MySQL Server or is this serving other purposes?
Is this for a Cart/Store? Blogs? Random websites?
|
| Posted by DotAL, 06-18-2011, 03:45 PM | | Hi Geoff,
Look from mysqltuner i got this "[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
"
So only InnoDB is active for now...
Each of what ? where i can find this ?
Its a Random Websites Wordpress, Joomla, DLE, Forums, etc..
I am working with mysqltuner to fix this.. and the loads of slq sometimes exeed the normal loads..
Thankyou for your help
|
| Posted by Geoff Winans, 06-18-2011, 03:59 PM | | Sady,
Most of those apps default to MyISAM.
Do you know offhand what version of MySQL you're running?
Here's a query you can run to find out data sizes:
SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table',
CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," MB") AS Size FROM INFORMATION_SCHEMA.TABLES;
Without knowing your sizes, here's a rough config:
under the [mysqld] tag, add/alter:
|
| Posted by DotAL, 06-18-2011, 04:18 PM | | As about the Version, i know only that is 5.1 i tried to get to know the real version but nothing
i think you want this
And some kind wired on some querys like..
this is from LiveZilla :O
Also this is my "my.cnf" what to modify ?
Thankyou i really aprecatie it
|
| Posted by Geoff Winans, 06-18-2011, 04:26 PM | | sady,
try the configuration I posted above.
It may work quite well.
MySQL is quite a beast to tame. To really get maximum performance, you need a fast machine with fast disks.
|
| Posted by DotAL, 06-18-2011, 04:27 PM | | Should i overwrite or just update the old ones with the new ones ...
Also i have i7, 8GB ram, SATA HDD Raid 1 ... i think this is good right ?
|
| Posted by DotAL, 06-18-2011, 04:34 PM | | Just did what you suggested to
From mysqltuner i got this..
and this
|
| Posted by Geoff Winans, 06-18-2011, 04:34 PM | | Single-drive machines are not optimal for a shared-mysql environment.
Optimal setup is a 12 Disk RAID10 w/ 32-64G of memory and the fastest multicore processor(s) you can get your hands on.
A single-drive server is going to get IO bound very, very fast.
Update the configuration - do not overwrite.
|
| Posted by DotAL, 06-18-2011, 04:36 PM | | Yeah, but thos kind of servers cost a loot more than this and the only thing i can do now is optimizing it ..
Thankyou so much i hope this works
|
| Posted by Geoff Winans, 06-18-2011, 04:38 PM | | Hope it helps.
If you're still seeing issues, let me know. there is more that can be done, but that involves bumping you over to a Percona build of MySQL and is not a task to be taken lightly.
|
| Posted by DotAL, 06-18-2011, 04:40 PM | | Thankyou ...
Hmm yeah but for now i think i am good thnx to you ..
|
| Posted by netmultiple, 06-26-2011, 03:44 PM | | Mysqltuner is the best idea to start with mysql optimization because It's will find the way to optimize base on your server usage
|
| Posted by mixmox, 06-26-2011, 06:23 PM | | after i run mysqltuner this warning show:
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
key_buffer_size (> 81.0M)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
what shuld i do now?
|
| Posted by ddns, 06-26-2011, 10:44 PM | | Run optimize table manually
- Re-run the test after 24H
- Enable slow query log - this is really easy to do in the config, and set it to something like 5 seconds. Then just look at the queries taking the most time to find out what might be lagging. If that is too much, lower it and keep repeating this process.
- Adjust tmp_table_size as suggested
- Add LIMITs to as many of your queires as you can
- Adjust the last 3 variables as suggested
Still it is best to wait 24H before relying on the data from the test because it could be skewed.
|
| Posted by mixmox, 06-29-2011, 11:01 AM | | mm. this is my log after 48 hours i run this tool again:
>> MySQLTuner 1.2.0 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.56
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 431M (Tables: 5326)
[--] Data in InnoDB tables: 160K (Tables: 10)
[--] Data in MEMORY tables: 6M (Tables: 19)
[!!] Total fragmented tables: 218
-------- Security Recommendations -------------------------------------------
[!!] User 'eximstats@localhost' has no password set.
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 16h 40m 9s (5M q [22.137 qps], 104K conn, TX: 49B, RX: 832M)
[--] Reads / Writes: 86% / 14%
[--] Total buffers: 58.0M global + 4.5M per thread (100 max threads)
[OK] Maximum possible memory usage: 508.0M (34% of installed RAM)
[OK] Slow queries: 0% (14/5M)
[OK] Highest usage of available connections: 14% (14/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/82.7M
[OK] Key buffer hit rate: 99.5% (22M cached / 115K reads)
[OK] Query cache efficiency: 70.2% (2M cached / 4M selects)
[!!] Query cache prunes per day: 94783
[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 127K sorts)
[!!] Temporary tables created on disk: 36% (158K on disk / 439K total)
[OK] Thread cache hit rate: 99% (14 created / 104K connections)
[!!] Table cache hit rate: 0% (1K open / 141K opened)
[OK] Open file limit used: 24% (2K/8K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 160.0K/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 16M)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
table_cache (> 1024)
|
|
 Add to Favourites
 Print this Article
|
