MySQL port settings for the best performance

kenorb

kenorb

I really need the fastest solution, which following settings are the best?
Code: 
You may use the following build options:
	WITH_CHARSET=charset	Define the primary built-in charset (latin1).
	WITH_XCHARSET=list	Define other built-in charsets (may be 'all').
	WITH_COLLATION=collate	Define default collation (latin1_swedish_ci).
	WITH_OPENSSL=yes	Enable secure connections
				(define WITHOUT_YASSL for backward compatibility).
	WITH_LINUXTHREADS=yes	Use the linuxthreads pthread library.
	WITH_PROC_SCOPE_PTH=yes	Use process scope threads
				(try it if you use libpthread).
	WITH_FAST_MUTEXES=yes	Replace mutexes with spinlocks.
	BUILD_OPTIMIZED=yes	Enable compiler optimizations
				(use it if you need speed).
	BUILD_STATIC=yes	Build a static version of mysqld.
				(use it if you need even more speed).
	WITH_NDB=yes		Enable support for NDB Cluster.
BUILD_OPTIMIZED=yes for sure.

My make.conf:
Code: 
# specific options/etc for ports
.if ${.CURDIR:M*/databases/mysql*-server}
BUILD_OPTIMIZED=yes
.endif

Do WITH_LINUXTHREADS/WITH_PROC_SCOPE_PTH or BUILD_STATIC will boost the performance on Intel Xeon Core2?
 
Your database organization and choice of database engine will have a much higher impact with regards to it's performance.

Database performance is mainly dictated by I/O (how fast can you read/write data) not CPU.
 
SirDice said:
Your database organization and choice of database engine will have a much higher impact with regards to it's performance.

Database performance is mainly dictated by I/O (how fast can you read/write data) not CPU.
Click to expand...

You saying that BUILD_OPTIMIZED=yes doesn't change anything with apache+php+mysql performance and page loading, even on complex queries on 0,5G-1,5G databases?
I've got around 500 mysql queries per one page load.
It's not always I/O, databases should be cached in memory, then it's matter of CPU.
Why doesn't say then: "Enable compiler optimizations (use it if you need speed)." ?
 
kenorb said:
You saying that BUILD_OPTIMIZED=yes doesn't change anything with apache+php+mysql performance and page loading, even on complex queries on 0,5G-1,5G databases?
Click to expand...
Yes. We have a lot more databases exceeding that. I think the biggest we have is around 7GB.

I've got around 500 mysql queries per one page load.
Click to expand...
Then there's something seriously wrong with that page or your queries.

It's not always I/O, databases should be cached in memory, then it's matter of CPU.
Click to expand...
This is done with DMA, which doesn't use the CPU.

The only time our CPUs are hit (hard) is when the database backup runs. But that CPU hit is mostly on gzip. Another reason the CPU would get hit is when you haven't optimized mysql's configuration and it's using more memory then the machine has. Correctly tuning mysql and the queries is the key to getting the best performance.
 
I don't use Drupal but all I can suggest is to have a seriously long and hard look at how the pages are build up. Perhaps some tables benefit from InnoDB instead of MyISAM. Perhaps tables need extra indexes. Perhaps you can benefit from a bigger query cache, key buffers, thread cache, join buffers, tmp table sizes, etc.

Tuning databases is almost an art form in and of itself.
 
Even on JOIN, DISTINCT, etc?
Code: 
# Time: 101104 17:28:58
# User@Host: root[root] @ localhost []
# Query_time: 2.233344  Lock_time: 0.000178 Rows_sent: 11  Rows_examined: 440396
SET timestamp=1288891738;
SELECT DISTINCT(n.nid) FROM node n LEFT JOIN node_revisions ns ON (n.nid = ns.nid) INNER JOIN viddies nx ON (n.vid = nx.vid) WHERE (ns.nid LIKE '%46537%');

# Time: 101029 17:17:06
# User@Host: root[root] @ localhost []
# Query_time: 2.746130  Lock_time: 0.000000 Rows_sent: 1027877  Rows_examined: 1027877
SET timestamp=1288369026;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `search_index`;
For sure if you are sorting 1m rows which are already loaded and joining them, it uses some CPU.
 
kenorb said:
Even on JOIN, DISTINCT, etc?

{..snip..}

For sure if you are sorting 1m rows which are already loaded and joining them, it uses some CPU.
Click to expand...

Use EXPLAIN and see if it properly uses indexes. If it has to resort to file sorts it's going to be a lot slower.

When I first started where I work now our MySQL database performed like crap. Just by tuning it's parameters and optimizing a lot of queries we managed to increase the overall performance 2-3 times. And that's running on the same MySQL, same box, just some tuning.

Edit: Another thing you should seriously consider is putting MySQL on it's own box. Separate Apache/PHP from it. Both have different tuning requirements and both benefit from different things. It'll be hard trying to find a common improvement and it'll be a lot easier to tune when separated.
 
I already using some of the huge config found in /usr/local/share/mysql
Code: 
/usr/local/share/mysql/my-innodb-heavy-4G.cnf
on host with 8GB memory.
But not much difference.
 
kenorb said:
I already using some of the huge config found in /usr/local/share/mysql
Code: 
/usr/local/share/mysql/my-innodb-heavy-4G.cnf
on host with 8GB memory.
But not much difference.
Click to expand...

Use that tuning script I posted above. Every database use is different and those configs are just examples.
 
I am not sure why it is called BUILD_OPTIMIZED because when I use it mysql uses more cpu and the number of slow queries sky rockets.
 
You must log in or register to reply here.
Back
Top