MySql Optimization
Asked Modified Viewed 3,801 times
asked
I would be interested in optimizing my mysql database,is there any way to do this?My site is on a VPS,so I can do all sorts of modifications.
answered
thanks smokeman,but I'm quite unsure what kind of changes it'll really make in the server performance,I have a database of ~18MB and this script only optimized a couple of KB.
The size of the DB no matters at all. Our DB is 32 mb.
It should only optimize a couple of kb. Because it's not the hole DB that needs optimizing.
It will only optimize that tables in the databas that has overflow. Luckely it's only some tables.
If it was all tables in the databas that needs optimizing - then something is wrong.
With other words: It works like a charm.
But if you don't want to use it - then don't. Noone is forcing you to do it.
It should only optimize a couple of kb. Because it's not the hole DB that needs optimizing.
It will only optimize that tables in the databas that has overflow. Luckely it's only some tables.
If it was all tables in the databas that needs optimizing - then something is wrong.
With other words: It works like a charm.
But if you don't want to use it - then don't. Noone is forcing you to do it.
Edited by smokeman on 28-11-2009 18:05,
Wanabo
Wanabo 10
- Senior Member, joined since
- Contributed 598 posts on the community forums.
- Started 94 threads in the forums
I have tried to optimize my settings for mysql on my VPS.
In etc/my.cnf you can find lines like below.
Please do not copy and paste these lines in your my.cnf because they are optimized for my situation. (available memory etc.)
Just google for my.cnf, read a lot about it and when you understand the effect do some experimenting.
Backup your original my.cnf
In etc/my.cnf you can find lines like below.
Please do not copy and paste these lines in your my.cnf because they are optimized for my situation. (available memory etc.)
Just google for my.cnf, read a lot about it and when you understand the effect do some experimenting.
Backup your original my.cnf
Quote
max_connections = 400
key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 8M
table_cache = 2048
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 8M
query_cache_size = 32M
query_cache_type = 1
tmp_table_size = 64M
key_buffer_size = 64M
skip-innodb
thread_concurrency=4
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 32M
sort_buffer = 32M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
answered
answered
@smokeman - I use VPS and,that is already available trough SSH,you only need to type the command : htop and you can see all stats in real time.You can see CPU usage,Memory Usage,Active Connections and a few more things,and if you type only:top it'll show you the stats of mysql,apache and other components that are used.Don't know if you knew that.But if I missunderstood please let me know.
Hi again Mystikal.
Hm, actually I don't know what you're talking about.
What is VSP & SSH ?
Can I see data from my server with that commands you're talking about - and if yes so how ?
Hm, actually I don't know what you're talking about.
What is VSP & SSH ?
Can I see data from my server with that commands you're talking about - and if yes so how ?
answered
VPS=virtual private server
SSH=Secure Shell or SSH is a network protocol that allows data to be exchanged using a secure channel between two networked devices.[1] Used primarily on Linux and Unix based systems to access shell accounts.
But I'll send you a screenshot,of what I'm talking about here.
In the screen above you can see the ssh software running the htop command.
You get SSH reguralely if you parchause a VPS server,and trough it you can install all sorts of stuff by giving just commands,it's pretty cool actually :)
SSH=Secure Shell or SSH is a network protocol that allows data to be exchanged using a secure channel between two networked devices.[1] Used primarily on Linux and Unix based systems to access shell accounts.
But I'll send you a screenshot,of what I'm talking about here.
In the screen above you can see the ssh software running the htop command.
You get SSH reguralely if you parchause a VPS server,and trough it you can install all sorts of stuff by giving just commands,it's pretty cool actually :)
Edited by Mystikal on 29-11-2009 11:53,
Ahhh I see!
answered
glad we sorted this out
Wanabo
Wanabo 10
- Senior Member, joined since
- Contributed 598 posts on the community forums.
- Started 94 threads in the forums
Quote
Processor Name Intel(R) Xeon(R) CPU E3110 @ 3.00GHz
Vendor ID GenuineIntel
Processor Speed (MHz) 2991.308
Processor Name Intel(R) Xeon(R) CPU E3110 @ 3.00GHz
Vendor ID GenuineIntel
Processor Speed (MHz) 2991.308
Total Memory 1555380 kB
Free Memory 316340 kB
Total Swap Memory 1048568 kB
Free Swap Memory 1048508 kB
System Uptime 44 Days, 17 Hours and 35 Minutes
Because of the dual core I have added thread_concurrency=4, for every cpu you can multiply this by 2.
In phpMyAdmin go to tab status,
For every value shown in red, try to increase the according setting in my.cnf.
Values shown in green are ok.
Just remember to restart mysql every time you change values
Edit: for benchmarking use this linux command
ab -c 5 -n 20 yourwebsite.com/news.php
Edited by Wanabo on 29-11-2009 17:27,
Category Forum
System Administration - 8Labels
None yet
Statistics
- Views 0 views
- Posts 12 posts
- Votes 0 votes
- Topic users 3 members
3 participants
Wanabo 10
- Senior Member, joined since
- Contributed 598 posts on the community forums.
- Started 94 threads in the forums
smokeman 10
- Veteran Member, joined since
- Contributed 920 posts on the community forums.
- Started 79 threads in the forums
Mystikal 10
- Junior Member, joined since
- Contributed 11 posts on the community forums.
- Started 1 thread in the forums
- Started this discussions
Notifications
Track thread
You are not receiving notifications from this thread.
Related Questions