[Rejected] MySQL my.ini is still bad / not tuned :-) (1 Viewer)

[disaster123]

Hi!

The default my.ini which is installed with the MP installer isn't really optimized for MP. You can gain a lot more performance if you tweak the values.

I've attached a more tuned / optimized my.ini. I've optimized with the help of mysqltuner (MySQLTuner).

Stefan

 

[chemelli]

rtv, can you comment please ?

Added:

• skip-innodb
• max_heap_table_size=32M

Changed:

• query_cache_size=64M (from 32M)
• table_cache=256 (from 64)
• tmp_table_size=32M (from 18M)

Simone

 

[tourettes]

• query_cache_size=64M (from 32M)
• table_cache=256 (from 64)
• tmp_table_size=32M (from 18M)

I would assume that those are based on the DB content / size. So the optimization results might not be good for everyone.

 

[disaster123]

AW: MySQL my.ini is still bad / not tuned

Shure the db size matters. But I'm using DVB-C with only about 200 channels and my DB size is really small. So for most of the users here (DVB-S) the value are still too low. But the default values might be nearly for everyon too low.
Some comments regarding the values:
* skip-innodb
As MP does not use innodb it does not make sense to load the innodb engine. So this skip is definitely useful.

* max_heap_table_size=32M
max_heap_table_size should be always equal to tmp_table_size you can read this in every mysql performance documentation. At the moment this value is missing at all and is not set equal to tmp_table_size.

* query_cache_size=64M (from 32M)
The query_cache is with 32M absolutely too low. I think it should be raised to 96MB or so. And even for the users who don't need such a big cache it doesn't matter as MySQL is then simply not using as much memory. And it is pruning the cache itself regulary.

* table_cache=256 (from 64)
As every MP user has the same tables - this value should be good for everybody.

* tmp_table_size=32M (from 18M)
For me there were some queries made by MP which were filesorting comes into play - this is really bad. Higher this value tells MySQL to not use disk for sorting until this value is reached. So it is harmful for everybody since this amount of memory is not generelly in use - only when mysql need it for sorting.

 

[rtv]

AW: MySQL my.ini is still bad / not tuned

Someone with full IRC logs should do a grep.
When I tweaked the defaults last time I posted links from MySQL devs who explained why increasing some cache parameters will SLOW down the performance.

In addition my tweaks where done based on some assumptions - like optimization should focus on slow scenarios where every ms is annoying. The automatic optimizers however will optimize "generally" for the best average and overall load. It doesn't know that e.g. querying the channel status is done by background threads invisible to the users and might even be half a second slower. Likely it doesn't understand that even just 100ms more on the mini-epg are very bad for the user experience.

Finally my values where the results of using MySQL for 3 years with DVB-S and Clickfinder. So I have around 1200 channels of which nearly 80 important channels have extended EPG worth 50-70 MB of raw data in Program table. While this sounds massive I consider myself among the biggest part of MP's user base (North of Europe + DVB-S).

Do you have exact measurements which queries benefit how much from your changes? I'd like to compare the most important ones then.

 

[disaster123]

AW: MySQL my.ini is still bad / not tuned

No sorry don't have such queries but at least my epg feels a lot faster - but i've no problem if this one is rejected i can use my own my.ini for me It was just an idea to post here the values.

 

[chemelli]

for sharing, but we dediced to stay with what we have.

Simone

 

[disaster123]

AW: MySQL my.ini is still bad / not tuned

no problem