# performance tips



## bl4d3run (Apr 25, 2011)

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.


----------



## Alt (Apr 25, 2011)

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 =)


----------



## mamalos (Apr 25, 2011)

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.


----------



## da1 (Apr 25, 2011)

I'm 100% sure you already checked google, right ?


----------



## pbd (Apr 25, 2011)

tuning(7)


----------



## da1 (Apr 25, 2011)

pbd said:
			
		

> tuning(7)



Hmm, didn't know about this one.

Diky vole


----------



## olav (Apr 26, 2011)

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.


----------



## SirDice (Apr 26, 2011)

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.


----------



## vivek (Apr 26, 2011)

Apart from mysql query cache and tuning you need to use databases/memcached


----------



## UNIXgod (Apr 26, 2011)

pbd said:
			
		

> tuning(7)



Beat me to it!


----------



## bestwc (May 3, 2011)

#######PostgreSQL Performance Tuning
ee postgresql.conf

```
----------------------------------------------------
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/)


----------

