Re: Improuve my DB performances on server
> I've 64Go of RAM
> installed on Windows Server 2012
These fooled me into believing that you were running 64-bit everything.
Please verify that the Windows Server 2012 is a 64-bit version !
> | version_compile_machine | x86 |
> | version_compile_os | Win32 |
Yes, you are running 32-bit version. You should plan on upgrading MySQL at some point.
(32-bit installations are so rare these days, that I forget to inquire.)
> so i can't set the innodb_buffer_pool_size over 4G.
Quite true. In fact, you should not set it anywhere near 4G (on a 32-bit version). 1500M should be safe. (Or leave it alone; see below.)
> | innodb_log_file_size | 5242880 |
Don't change that without further discussion; a change could prevent restarting. Note: even commenting out the value _may_ lead to changing the value (to whatever the builtin default is).
> i had a dangerous problem
I can think of three things that would be "dangerous"; I discussed them above.
* Configuring more than 4GB to a 32-bit mysql. (This is true for any 32-bit program.)
* Allocating so much RAM that mysql is swapping. (If you have a 64-bit OS, and 64G of RAM, etc, there won't be any swapping.)
* It takes multiple steps to change the log size. (mysqld won't start until you go back to the original size, or go through the steps.)
OK, back to the sluggish performance...
> `date` date NOT NULL,
> `heure` time DEFAULT NULL,
> KEY `heure` (`heure`),
> KEY `date` (`date`),
It is _usually_ much better to combine into a single field of type DATETIME (or TIMESTAMP). WHERE and ORDER BY clauses using date&heure will become much simpler and faster.
> PRIMARY KEY (`id_rapport`),
> KEY `id_rapport` (`id_rapport`),
A PRIMARY KEY is a UNIQUE KEY; the latter index is redundant and may be DROPped. (This is a minor improvement, mostly helping INSERTs.)
I see no "compound indexes". Let's see some of the SELECTs.
You provided the EXPLAIN without the SELECT. If it was simply
SELECT * FROM rapport;
then I question whether you really need to fetch all 10K rows at once. (This would be modestly slow.)
> Here's the biggest table
I see one potentially big field (adresse). Assuming it averages only a couple hundred bytes, then the whole table is only a few megabytes. With innodb_buffer_pool_size = 134217728, all the tables would easily fit?? This will show the table sizes:
SHOW TABLE STATUS;
OK, all that says is that you are not likely to have an I/O problem. So, I am guessing it is a CPU problem. Again, this goes back to finding the "slow" queries and studying their EXPLAINs and INDEXes.
> | log_slow_queries | OFF | --> ON
> | long_query_time | 10.000000 | --> 1
then (after a day of collecting slow queries) use pt_query_digest to summarize the slowlog. Present the first two to us, together with the SHOW TABLE STATUS and SELECT and EXPLAIN SELECT.
As a crude alternative to the slowlog (and avoiding changing my.ini), do this as root whenever things are "slow":
SHOW FULL PROCESSLIST;
That may catch some of the slow queries.