Fine server tunning

[blacksea]

Current max_heap_table_size = 1.00 G
Current tmp_table_size = 1.00 G
Of 20397 temp tables, 40% were created on disk

I have 1G to max_heal_table_size and tmp_table_size and still 40% from tables are created on disk? any clue to avoid creating tables on disk?

[badboy29]

I have in my server these two options with 64m:

Code: Select all

-------- General Statistics --------------------------------------------------[--] Skipped version check for MySQLTuner script[OK] Currently running supported MySQL version 5.5.9-log[OK] Operating on 64-bit architecture -------- Storage Engine Statistics -------------------------------------------[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster[--] Data in InnoDB tables: 402M (Tables: 150)[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)[!!] Total fragmented tables: 26 -------- Performance Metrics -------------------------------------------------[--] Up for: 14h 10m 49s (1M q [22.330 qps], 454 conn, TX: 164M, RX: 180M)[--] Reads / Writes: 20% / 80%[--] Total buffers: 1.3G global + 832.0K per thread (600 max threads)[OK] Maximum possible memory usage: 1.8G (23% of installed RAM)[OK] Slow queries: 0% (1/1M)[OK] Highest usage of available connections: 5% (32/600)[OK] Key buffer size / total MyISAM indexes: 256.0M/226.7M[OK] Key buffer hit rate: 98.6% (509 cached / 7 reads)[!!] Query cache is disabled[OK] Sorts requiring temporary tables: 0% (77 temp sorts / 11K sorts)[!!] Joins performed without indexes: 1027[OK] Temporary tables created on disk: 0% (10 on disk / 1K total)[OK] Thread cache hit rate: 92% (32 created / 454 connections)[OK] Table cache hit rate: 96% (210 open / 217 opened)[OK] Open file limit used: 0% (19/8K)[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)[OK] InnoDB data size / buffer pool: 402.3M/1.0G -------- Recommendations -----------------------------------------------------General recommendations:    Run OPTIMIZE TABLE to defragment tables for better performance    MySQL started within last 24 hours - recommendations may be inaccurate    Adjust your join queries to always utilize indexesVariables to adjust:    query_cache_size (>= 8M)    join_buffer_size (> 256.0K, or always use indexes with joins) 

OFF: about "Joins performed without indexes: 1027" can slow the mysql server ?

[Tybex]

Thanks, very usefull information

[DonDrakon]

Write pls here your HDD array. What disks inserted in server? SATA or SCASII or SAS? What array u using?

[gabrielhw]

As the population supports this configuration?
The memory is well distributed among the three processes?



see anything wrong?

[regenx]

Hello, am running a server with 700 players around plus 100 + - offline traders, server machine has 24gb ddr3 ram, processor is a Intel Bi-Xeon E5504 8x2.0 ghz, am looking for some tricks to give more stability cause sometimes people have lag spikes, I would thank any configuration trick for it.

Ps: server is running on windows server 2008 and line is 1gbps.

thanks

LOL ) very funny. I fyou have lag on this machine maybe you need to change your datacenter )
Ps: server is running on windows server 2008 and line is 1gbps.

Practically it's impossible to have lag on that server machine especially with 1GB internet connection..

LOL again!! i think something is missing in you text

[Naminator_X_]

I believe his hard drives are lagging :/ If you have the money for such a machine ... why didn't you bought SSDs for the database and velociraptor for anything else ? Hybrid machines are getting very popular. Even the new Intel chipsets that is about to come can use SSDs as faster HDD cache ... which is epic ! Performance of HDD is spiking sky high. You know ... having server is not only about CPU and ram ... every part performance is important. Is the ram buffered or not ? How did you configured your thread pools ? Is the server using the whole potential ? And btw you are crazy to spend so much money on a private server when you have 0 knowledge of servers and programming :/ It is really sad ... that's a good wasted server