-5

We have a dedicated database server (MariaDB 5.5.31) hosting our database used by our java application (accessing the db via hibernate from another server). While our database itself is using about 12gb of RAM, the operating system (CentOS 6.4) is caching around 25gb additionally.

Can you give me any advice, how we can influence this behaviour?

While most of our 100 tables have less than 1000 entries, we have some tables with above 10 million. These tables experience lots of reads and writes.

These are our db settings:

[server]

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
character-set-server = utf8
collation-server=utf8_general_ci
skip-external-locking
back_log = 50
max_connections = 100
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 16M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 128M
query_cache_limit = 2M
thread_stack = 240K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
binlog_cache_size = 1M
log-bin=mysql-bin
binlog_format=mixed
expire_logs_days = 2

#*** MyISAM Specific options
key_buffer_size = 384M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

# *** INNODB Specific options ***
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 10G
innodb_data_home_dir = /var/lib/mysql
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

[embedded]
[mysqld-5.5]
[mariadb]
[mariadb-5.5]
tflu
  • 1
  • 1
  • Could you please share information on the tables that store more than million records? Are those Innodb or ISAM? What are time related field and what kind of data goes in there? Are there any index there? Are table stats done? – Ashutosh Nigam Mar 13 '15 at 14:29

1 Answers1

0

If the tables with millions of records are transactional tables then following are the foremost things to be done:

  1. List the child tables for those tables.
  2. Discuss with client and have a backup plan, i.e., only 1/1.5/2 years of data to be kept in the table.

  3. If there is lots of child table's data that can be backed up along with then have 2 options:

    a) Have one denormalized table for the backup data

    b) Have one table for each can be named original_table_name_bk

There should be a backup process planned periodically say every 3 months, when preferrably during lean period the process will run and move data from transactional table to backup table.

Now you have both historical and current data in your database without impacting performance of transactional operations.

Ashutosh Nigam
  • 868
  • 8
  • 27
  • All tables are innodb tables and yes the data is time related and we have indexes. Unfortunately most of our data is inside one year and we plan to implement backup strategies for older data. I'm baffled the operating system is caching about double the database's size. Am I missing some crucial settings which would cause the OS to cache less? – tflu Mar 13 '15 at 14:59
  • @tflu That sounds good... As isam tables contain more data, you can check the possibility of having backup columns as ISAM. In either case mysql is not designed to Multi billion records, so you may need to remove data from backup tables also. That time you can plan to have this data either in some datawarehouse or document repository and use elasticsearch to retrieve it. – Ashutosh Nigam Mar 13 '15 at 15:02
  • 10 million record for transactional table is too much in mysql database. Ask DBA to run stats and see the impact. – Ashutosh Nigam Mar 13 '15 at 15:04
  • Remember those _bk tables are still in same database, so you can have a separate view for older transaction. Like you see in Banks, for more than 6 months you need to go to a different link. As the data is coming from different table. – Ashutosh Nigam Mar 13 '15 at 15:43