HOWTO: Optimize MySQL 4.x

chartmusic said:
Good news, now have upgraded to 2GB of RAM.

After 24 hours here is free values.
Code: 
free
             total       used       free     shared    buffers     cached
Mem:       2073516    1519256     554260          0      61064    1067596
-/+ buffers/cache:     390596    1682920
Swap:      2096472          0    2096472
Click to expand...

Day two

Code: 
free
             total       used       free     shared    buffers     cached
Mem:       2073516    1799728     273788          0      59436    1268384
-/+ buffers/cache:     471908    1601608
Swap:      2096472          0    2096472

Not really happy that memory free has gone down since yesterday. Wondering if Swap will start to be used next and then perhaps site slowdown? Any thoughts?
 
chartmusic said:
Day two

Code: 
free
             total       used       free     shared    buffers     cached
Mem:       2073516    1799728     273788          0      59436    1268384
-/+ buffers/cache:     471908    1601608
Swap:      2096472          0    2096472
Click to expand...
There are many variables invloved in memory use and from what you posted, I don't see any problems. If you suspect that there is a memory leak, you should contact your Data Center to test your memory.
 
Should a dual core count as 2 processors? I just upgraded to a pentium dual core 2.8 GHz and 2 GB RAM.

I have some mysql intensive sites with joomla and so on. With my celeron mysql was crashing but this optimization helped alot.
http://help.directadmin.com/item.php?id=44

I am wondering how to optimize with upgrade.
 
whistler said:
Should a dual core count as 2 processors? I just upgraded to a pentium dual core 2.8 GHz and 2 GB RAM.

I have some mysql intensive sites with joomla and so on. With my celeron mysql was crashing but this optimization helped alot.
http://help.directadmin.com/item.php?id=44

I am wondering how to optimize with upgrade.
Click to expand...

Dual core means that there are two processors on one die so yes your Pentium D will count as two processors.
 
The same as MySQL 4.1 :) Edit /etc/my.cnf and optimize what you want.
 
Fantastic performance improvement :D

I have a dual core, but with hyperthreading so it thinks it has 4 of them or something.. anyway, used thread_concurrency=4
and the rest.

Also added:
Code: 
log_slow_queries=/var/log/mysql-slow-queries.log
long_query_time=10
Because mysql> status
showed me I had 30 slow queries..

Unfortunately since I restarted MySql those queries were gone, so I haven't had the chance yet to see which queries on which clients are slowing the server down.

I guess I'll need to wait, but the speed of forums on my clients has defenately improved.
 
Bugger...

# cat /var/log/mysql-slow-queries.log
cat: /var/log/mysql-slow-queries.log: No such file or directory
Click to expand...

That went well... :(

I have no idea what the slow queries are, so I can't tackle them..
And I don't know why it didn't log those queries :(
 
Interesting..

I think mysql didn't have permission to write to that logfile...

So I created it, and chown'd it..
# chown mysql:root /var/log/mysql-slow-queries.log
# chmod 660 /var/log/mysql-slow-queries.log
Click to expand...

or, if you use the default filename:
# chown mysql:root /var/log/mysql-slow.log
# chmod 660 /var/log/mysql-slow.log
Click to expand...

Let's wait and see...


btw, it's not recommended to turn these logs on, as they will slow down ur server..
But if you want to tackle these slow queries, than it's a good thing to use for a day or 2..

Next to that... adding memory ain't the best solution to speed up mysql.
It's usually the users that have crappy queries.. Not even 6Gb of ram would fix that..

So checking these slow queries and contacting the user abt it is a good way to go :)
 
Last edited:
may i know the server spec as below how to optimize it ?

Processor Speed (MHz) Pentium IV 3.0
Total Memory 1025712 kB

can somebody guide for me ?

thank you.
 
seachen,

Optimization for MySQL is not cut and dried. You try recommendations and see if they help. And tweak them, as your server will run differently from others.

Look above in this thread; you'll find some suggestions.

In general the best way to speed up a server running 1 GB of memory is to add another 1 GB of memory.

Jeff
 
I have found that if it is a heavy used database, often putting it on its own system and spreading it across as many spindles as possible with as much memory as possible is the best solution. This way your data is mostly loaded in memory, with occasional writes.
 
I have done the optimization on two of my boxes (CentOS) now and it went well so far. On my third box (Debian) the my.cnf is located at /etc/mysql/my.cnf and I wounder if this is the file to edit or if there should be a working my.cnf in /etc .
How can I check that the my.cnf that acctually is on the box is working its wounders? :confused:
 
having mysql on separate server mean latency time is going to slow down performance no?
 
Yes, perhaps. But whether or not it will be noticeable depends on how much load it takes of your shared hosting server.

Jeff
 
Hello,
I get this error:
ERROR! MySQL manager or server PID file could not be found!
Starting MySQL ERROR! Couldn't find MySQL manager (/var/lib/bin/mysqlmanager) or server (/var/lib/bin/mysqld_safe)
Click to expand...
How to fix it?
 
Hello,

The reason you still haven't got an answer is might be that you posted your question in a wrong thread and sub-forum. Thus you'd better start a new thread in a proper sub-forum.

Note, you should provide us with information what did precede the error?
 
You must log in or register to reply here.
Back
Top