MySQL optimize help needed

koolaquarian · May 10, 2012

Hello Everyone,

I am new here and i dont know much about optimizing mysql.

I have a dedicated server and mysql is taking too much of load and my websites opens slow...

please help me out...

My Server Details :

Total processors: 2

Processor #1

Intel(R) Pentium(R) Dual CPU E2160 @ 1.80GHz

Processor #2

Intel(R) Pentium(R) Dual CPU E2160 @ 1.80GHz

This is what happens when my website gets traffic...

I tried running mysqltuner.pl and this was the result :

Code:

>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> 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.62-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 368M (Tables: 472)
[--] Data in InnoDB tables: 208K (Tables: 13)
[--] Data in MEMORY tables: 248K (Tables: 2)
[!!] Total fragmented tables: 55

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 2h 7m 17s (6M q [33.973 qps], 118K conn, TX: 27B, RX: 550M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.4G (36% of installed RAM)
[OK] Slow queries: 0% (20/6M)
[OK] Highest usage of available connections: 11% (55/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/241.5M
[OK] Key buffer hit rate: 100.0% (113M cached / 51K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 472K sorts)
[!!] Joins performed without indexes: 87873
[!!] Temporary tables created on disk: 44% (229K on disk / 517K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 18K opened)
[OK] Open file limit used: 4% (113/2K)
[OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
[OK] InnoDB data size / buffer pool: 208.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
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
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)

Now this is by help of few guys who told me some commands to show these things.. i'll need step to step guidance... can anyone please help me out and solve this issue

I would be really thankful to you for your favour..

al0r · May 10, 2012

Can u use any caching in your scripts?
this prolem may be from your websites scripts.
First which version yours mysql? last? check it.
Then check your scripts which scripts u are using?

koolaquarian · May 10, 2012

Hey Alor,

I am using pligg and wordpress.
and Mysql version Currently installed is 5.1.62
also how do i do caching in my scripts??

al0r said:
Can u use any caching in your scripts?
this prolem may be from your websites scripts.
First which version yours mysql? last? check it.
Then check your scripts which scripts u are using?

al0r · May 10, 2012

I know wordpress, wordpress loading server when u get high traffic.
I recommen you upgrade mysql to MySQL 5.5
Then rebuild apache and use some caching plugins for wordpress.

koolaquarian · May 10, 2012

Bro, i think i am facing problem due to pligg site. i was facing this issue earlier also when i didnt have wordpress....
can you tell me about that?

al0r said:
I know wordpress, wordpress loading server when u get high traffic.
I recommen you upgrade mysql to MySQL 5.5
Then rebuild apache and use some caching plugins for wordpress.

al0r · May 10, 2012

and my recommend write script yourself, open source scripts not good for me with high-traffic.

al0r · May 10, 2012

I didn't used pligg, but u can rebuild apache with caching example:
Xcache for PHP

Are u sure maybe u are getting attack? how much visitors u have?

al0r · May 10, 2012

Can u check "Daily Process Log" from your WHM?
Which site loading server etc. (and which script)

koolaquarian · May 10, 2012

This was the daily process log :

User % CPU % MEM MySQL Processes
mysql 47.30 2.25 0.0
jack 3.79 2.54 3.0
lolbu 2.59 0.20 0.2
stylup 0.21 0.01 0.0
root 0.21 6.94 2.0

I have installed the caching plugin for wordpress but need help for my pligg site and also my.cnf only have 3 lines

[mysqld]
set-variable = max_connections=500
log-slow-queries
safe-show-database

koolaquarian · May 11, 2012

Mysql is updated to the latest version and this is the result when i ran mysqltuner

Can anyone please help me out ??

Thanks

Code:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.22-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 364M (Tables: 472)
[--] Data in InnoDB tables: 208K (Tables: 13)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 248K (Tables: 2)
[!!] Total fragmented tables: 55

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15h 25m 59s (3M q [60.947 qps], 53K conn, TX: 6B, RX: 294M)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 583.2M (15% of installed RAM)
[OK] Slow queries: 0% (377/3M)
[!!] Highest connection usage: 99%  (150/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/235.9M
[OK] Key buffer hit rate: 98.9% (2B cached / 28M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 4% (6K temp sorts / 144K sorts)
[!!] Joins performed without indexes: 4019
[OK] Temporary tables created on disk: 25% (33K on disk / 130K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 12% (400 open / 3K opened)
[OK] Open file limit used: 56% (579/1K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[OK] InnoDB data size / buffer pool: 208.0K/128.0M

-------- 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
    Reduce or eliminate persistent connections to reduce connection usage
    Adjust your join queries to always utilize indexes
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    thread_cache_size (start at 4)
    table_cache (> 400)

sardelich · May 18, 2012

edit your /etc/my.cnf and just add query_cache_size directive so your my.cnf looks like this:

[mysqld]
set-variable = max_connections=500
log-slow-queries
safe-show-database
query_cache_size=32M
thread_cache_size=4
table_cache=1024

You can go up above recomended 8M for query cache size since I see you have 4 gigs of ram or something like that. I.ve added few more directives that should help reduce the load on mysql.Let it run for 24 hours before runnin mysqltuner again,it really helps to wait and get the right results. Query cache should help you a lot,but you will get more results after changing my.cnf and restarting MySQL and waiting for 24 hours.

koolaquarian · May 18, 2012

are you online now as i need to ask you something before i edit it..

Waiting for your reply

Thanks

sardelich said:
edit your /etc/my.cnf and just add query_cache_size directive so your my.cnf looks like this:

[mysqld]
set-variable = max_connections=500
log-slow-queries
safe-show-database
query_cache_size=32M
thread_cache_size=4
table_cache=1024

You can go up above recomended 8M for query cache size since I see you have 4 gigs of ram or something like that. I.ve added few more directives that should help reduce the load on mysql.Let it run for 24 hours before runnin mysqltuner again,it really helps to wait and get the right results. Query cache should help you a lot,but you will get more results after changing my.cnf and restarting MySQL and waiting for 24 hours.

sardelich · May 18, 2012

I'm here...

Thread starter	Similar threads	Forum	Replies	Date
S	How to optimize mysql - help	Workarounds and Optimization	2	Mar 11, 2020
U	Help - optimize mysqltunner to reduce high cpu usage	Workarounds and Optimization	5	Nov 14, 2016
F	Optimize MySQL Help	Workarounds and Optimization	2	Jan 14, 2015
	MySQL 5.6 Optimize help	Workarounds and Optimization	4	Jul 31, 2014
T	Please help me to optimize mysql (my.cnf)	Workarounds and Optimization	2	Jul 23, 2014

Search

Search

MySQL optimize help needed

koolaquarian

Member

al0r

Member

koolaquarian

Member

al0r

Member

koolaquarian

Member

al0r

Member

al0r

Member

al0r

Member

koolaquarian

Member

koolaquarian

Member

sardelich

Well-Known Member

koolaquarian

Member

sardelich

Well-Known Member