performance tips

B

bl4d3run

Hi, I'm a new user of FreeBSD. I'm looking for tips, settings etc. for performance, on sysctl etc.
Disk, network, load. I'm using postgresql and mysql, lots of heavy loads, and I'm looking for this. Can anybody tell to me performance tips for sql servers?

Thanks.
 
Easy way:
Fisrt - tune your (sql|web|...)-server itself. This should give more advantage before tuning the OS.
Then look that it loads in work using vmstat -ifs, vmstat -vms, top etc and remove/tune bottlenecks you can find. That's all =)
 
Alt is right, it is imperative that you deal with the sql backends first. Then check your FreeBSD tuning. You may find that a separate filesystem for your database system mounted with specific flags will improve performance too. Google it, you'll find many howtos for each db distro on FreeBSD.
 
Databases can be tuned vertically, horizontally and in every other direction you can think of. If something is slow, analyse your queries, transactions, indexes and so on. Sometimes you have to do trade-offs for better performance. An example is breaking normalization rules by inserting all data into just one table, though this really depends on the situation.

Databases are complex, and you can only learn by trying and failing. Because it's too much to knowledge in this area to know for a human mind and you're probably one of the few people in the world who knows how exactly your database should be working.
 
I've use the MySQL tuning primer script. It's quite helpful when dealing with tuning of MySQL.

Besides tuning things like caches and buffers, you also need to take a look at the slow-query.log. Queries can sometimes benefit immensely just by adding a few indexes. Another thing to watch for is InnoDB vs. MyISAM. The latter locks the entire table with an INSERT, the former only locks a rule. When you have concurrent inserts and selects switching to InnoDB might help too.
 
#######PostgreSQL Performance Tuning
ee postgresql.conf
Code: 
----------------------------------------------------
hba_file = 'pg_hba.conf'        # host-based authentication file
listen_addresses = '*'
----------------------------------------------------
shared_buffers
->Used to hold query results that are in progress.
->5%-10% of system physical memory
->Reasonably 25% [Not really true]

effective_cache_size
->Indicates the query optimizer how much OS filesystem cache expected to have.
->Might be all physical memory or 80% above for solely running servers.
->Set to a value of mem which will always available for PostgreSQL
->Larger = More likely to use index
->50% of total memory would be a normal conservative setting.

work_mem
->Memory sorting, for ALL users, and ALL sorting queries
->Could be really large, careful. Because this is per sort, per connection.
->50MB would be ok
->2%-4% of system physical memory

Lastly, try this -> (http://pgfoundry.org/projects/pgtune/)
 
You must log in or register to reply here.
Back
Top