MySQL, my.ini

Knowledge base
1

Hello Everyone,

I was wondering if anyone would be willing to share with me their my.ini setting for a large scale website. One of sites are getting about 7461735 hits in a year and we have hundreds of sites to track. Please let me know how you all have your mysql server set up. Does anyone use a dedicated mysql server?

Thank you for your time,

2

Does anyone have any suggestions?

3

If you need help feel free to contact support at: We offer support subscriptions for businesses worldwide to make the most out of Piwik analytics. - Analytics Platform - Matomo

See also this discussion: 301 Moved Permanently

4

Disclaimer: I’m not a MySQL specialist !

MySQL Tuning Primer Script in Launchpad was a great tool to help my get my settings ok.

We have 2.3 M pageviews in August so far (6 websites) and using 2 different servers only used for Piwik, nothing else (Apache-PHP + MySQL)

Here’s our my.cnf :

[mysqld]
log-error=/var/log/mysql/error.log
log-slow-queries = /var/log/mysql/slow.log
long_query_time = 10

MySQL UTF8

character-set-server=utf8
collation-server=utf8_general_ci

###########################################

Parametert Piwik InnoDB 2014

###########################################

Was 151

max_connections = 100

Was 5 MB defaut, now almost 3 GB

innodb_buffer_pool_size = 2700M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 4
innodb_flush_method=O_DIRECT

thread_cache_size = 128
query_cache_size = 64M
query_cache_limit = 16M
query_cache_type = 1

Changed from default after a “General error: 2006 MySQL server has gone away”

wait_timeout = 720
max_tmp_tables = 200
max_allowed_packet = 256M
table_open_cache = 500

MyISAM minimal not used by Piwik

key_buffer_size = 16M
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 1M
join_buffer_size = 1M

Hope it helps you.

Dali

1 Like
5

Thank you very much for you help. This does help out a lot.