DLE Heavy cpu load

[downfreak]

Hi,
i am running a DLE portal.
It have around 700K posts on it and its taking a lot of resources.

I cannot even run it on a server with 12Gb Ram + quadcore processor.

Is there a way to reduce these loads?

When i do command "top" on ssh, it shows that mysql is using 300%+ of resources?

Anyone know any fix for it?
thx

downfreak Reviewed by downfreak on 22nd Jun 2011. DLE Heavy cpu load Hi, i am running a DLE portal. It have around 700K posts on it and its taking a lot of resources. I cannot even run it on a server with 12Gb Ram + quadcore processor. Is there a way to reduce these loads? When i do command "top" on ssh, it shows that mysql is using 300%+ of resources? Rating: 5

[sady92]

You need to Tweak the file /etc/my.cnf (mysql configuration) so it wont give you mysql problems...

[soft2050]

Me too having problems like this

@sady92

Any description on what to tweak in there?

[sady92]

Well, open SSH use

/usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

this command to get suggestions what to optimizate otherwise you will have problems...

this is not a thing that can be righ away you need to test it every 1-3 days and see what this script suggest to you.

[downfreak]

when i run mysqltuner.pl this is what i get :

-------- Recommendations -----------------------------------------------------
General recommendations:
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
Increase table_cache gradually to avoid file descriptor limits
Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
query_cache_size (>= 8M)
sort_buffer_size (> 63K)
read_rnd_buffer_size (> 252K)
thread_cache_size (start at 4)
table_cache (> 4)


so what i need to do now in my.cnf?

[sady92]

Add this

query_cache_size=16M
sort_buffer_size=128K
read_rnd_buffer_size=512K
thread_cache_size=64
table_cache=512

Then Restart MySQL, and after 1-3 days run it again.

[downfreak]

Well, open SSH use



this command to get suggestions what to optimizate otherwise you will have problems...

this is not a thing that can be righ away you need to test it every 1-3 days and see what this script suggest to you.

this is my new my.conf now :

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K
skip-bdb
skip-innodb
query_cache_size=16M
read_rnd_buffer_size=512K
thread_cache_size=64

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 128K

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

I run the mysqltuner again and i get this :

-------- 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
Your applications are not closing MySQL connections properly
Variables to adjust:
key_buffer_size (> 1.0G)
query_cache_limit (> 1M, or use smaller result sets)

any idea what else i need to adjust?
thx

[sady92]

Look dont change anything wait some days.

[downfreak]

so you reckon its the mysql itself?

not the source code?

[Keosoft90]

optimize search , when search , it's will select many table, make index and optimize search only at index