Moshe1010
Well-known member
That means I need more RAM
How did you minimize your mysql server memory consumption?
- Thread starter rdn
- Start date
Moshe1010
Well-known member
I see that you have much more stuff than me, but I use Percona, so I'm not sure if there is a difference in terms of my.cnf. That's mine:
Code:
[mysqld]
open_files_limit=82000
default-storage-engine=InnoDB
wait_timeout=1200
interactive_timeout=1200
skip-federated
local-infile=0
user=mysql
# INNODB #
innodb_additional_mem_pool_size=16M
innodb_buffer_pool_size=1GB
innodb_file_per_table=1
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=0
innodb_flush_method=O_DIRECT
# CACHES AND LIMITS #
tmp_table_size=128M
max_heap_table_size=128M
query_cache_type=0
query_cache_size=0
max_connections=80
thread_cache_size=16
table_definition_cache=4096
table_open_cache=4096
# MyISAM #
myisam_sort_buffer_size=512M
key_buffer_size=512M
# SAFETY #
max_allowed_packet=16M
read_buffer_size=1M
thread_concurrency=16
sort_buffer_size=1M
join_buffer_size=1M
read_rnd_buffer_size=1M
[myisamchk]
read_buffer=2M
key_buffer=256M
sort_buffer_size=256M
write_buffer=2M
[mysql]
no-auto-rehash
[isamchk]
read_buffer=2M
key_buffer=256M
sort_buffer_size=256M
write_buffer=2M
[mysqlhotcopy]
interactive-timeout4 Cores
5GB RAM
SSD
xCache
Percona
CentOS
How much is your current RAM?That means I need more RAM
I think your forum is much larger than mine.
I'm using 4GB for InnoDB Buffer Pool Size.
8GB total of RAM on my server.
Moshe1010
Well-known member
I just posted my.cnf on my previous post. I'm about 600k posts and 30-40 concurrent connections.
I have 5GB RAM. It's not that cheap on my current server. I'm paying $10/1GB/Month. My server is managed though, and it's in Netherlands. US/Canadian servers are much cheaper, but I can't move due to latency "problems".
Here's my latest my.conf:
Just focus on optimizing all the Innodb variables.
Code:
[mysqld]
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#bind-address=127.0.0.1
tmpdir=/home/mysqltmp
skip-federated
#skip-pbxt
#skip-pbxt_statistics
skip-archive
#skip-name-resolve
#old_passwords
back_log = 75
max_connections = 100
key_buffer_size = 64M
myisam_sort_buffer_size = 32M
myisam_max_sort_file_size = 2048M
join_buffer_size = 256K
read_buffer_size = 256K
sort_buffer_size = 512K
table_definition_cache = 8192
table_open_cache = 8192
thread_cache_size = 64
wait_timeout = 120
connect_timeout = 10
tmp_table_size = 512M
max_heap_table_size = 512M
max_allowed_packet = 32M
max_seeks_for_key = 1000
group_concat_max_len = 102400
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 256K
bulk_insert_buffer_size = 8M
query_cache_limit = 8MB
query_cache_size = 256M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB
log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
log-error=/var/log/mysqld.log
# innodb settings
#innodb_extra_rsegments = 4
innodb_purge_threads=1
innodb_doublewrite = 1
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 32M
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1
# 200 * # DISKS
innodb_io_capacity = 100
innodb_read_io_threads = 2
innodb_write_io_threads = 2
# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1
userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 32M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 8M
aria_sort_buffer_size = 8M
[mariadb-5.5]
#ignore_db_dirs=
query_cache_strip_comments=0
#innodb_lazy_drop_table=1
innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192
[mysqldump]
quick
max_allowed_packet = 32M
[myisamchk]
key_buffer = 32M
sort_buffer = 16M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeoutJust focus on optimizing all the Innodb variables.
This Blog really helps me: http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
Moshe1010
Well-known member
I see these differnces betwee us.
You have:
[myisamchk]
I have:
And I don't have these lines:
You have:
[myisamchk]
Code:
write_buffer = 16M
sort_buffer = 16M
key_buffer = 32MI have:
Code:
write_buffer=2M
sort_buffer_size=256M
key_buffer=256MAnd I don't have these lines:
Code:
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1
# 200 * # DISKS
innodb_io_capacity = 100
innodb_read_io_threads = 2
innodb_write_io_threads = 2Moshe1010
Well-known member
InnoDB Buffer Pool: 1.55G/2.50G (61.91%)
Yeah, it was pretty bad. I guess it would increase even more with the server's uptime. I'm sure it's because my testing area is on the same server. I hope I could config different Buffer Pool to different accounts/databases on the same server.
Yeah, it was pretty bad. I guess it would increase even more with the server's uptime. I'm sure it's because my testing area is on the same server. I hope I could config different Buffer Pool to different accounts/databases on the same server.
MattW
Well-known member
That is why I always have my test set up on a different VPS. I don't care about tuning it, so I just stick it on a cheap $5 VPS.
Moshe1010
Well-known member
I was thinking about it, but then I have 2 problems:
1. No easy restore from backup (like R1Soft on my current server, which takes snapshots every hour)
2. I have Litespeed, which I wouldn't be able to setup on another server without a license. Sure, Litespeed (vs. apache) errors are pretty rare, but who knows?
But the RAM thing is getting out of proportion for my board's size, so I guess I would need to get another VPS for testing area.
Floren
Well-known member
You can't, memory and fast disks are vital for MySQL server.
Have you looked at xtrabackup?
Moshe1010
Well-known member
No' what's so special about it?You can't, memory and fast disks are vital for MySQL server.
Have you looked at xtrabackup?
Deebs
Well-known member
Online backups, incremental backups, etc etc, infact it is an essential tool for the DBA imo.
Oh, did I mention that it is free? I use this to backup my servers and then rsync the data offsite every 30 minutes.
Moshe1010
Well-known member
Isn't it just for databases? What about files?
Deebs
Well-known member
This thread is about MySQL, I commented on one of the best backup suites for MySQL. For files you can easily add an rsync command to the end of the your backup script.
Floren
Well-known member
I use mydumper and xtrabackup on a regular basis.
Code:
# yum --enablerepo=axivo info mydumper
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.agmn.ca
* extras: mirror.agmn.ca
* updates: centos.mirror.iweb.ca
Installed Packages
Name : mydumper
Arch : x86_64
Version : 0.6.0
Release : 1.el6
Size : 99 k
Repo : installed
From repo : axivo
Summary : High-performance MySQL backup tool
URL : http://launchpad.net/mydumper
License : GPLv3+
Description : Mydumper (MySQL Data Dumper) is a high-performance multi-threaded backup and
: restore toolset for MySQL and Drizzle. The main developers originally worked
: as Support Engineers at MySQL and this is how they would envisage mysqldump
: based on years of user feedback.
# yum --enablerepo=axivo info xtrabackup
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.agmn.ca
* extras: mirror.agmn.ca
* updates: centos.mirror.iweb.ca
Installed Packages
Name : xtrabackup
Arch : x86_64
Version : 2.1.6
Release : 1.el6
Size : 2.9 M
Repo : installed
From repo : axivo
Summary : Online backup for MariaDB
URL : http://www.percona.com/software/percona-xtrabackup/
License : GPLv2
Description : Percona XtraBackup is an online (non-blocking) backup solution for Percona
: XtraDB storage engines. It features uninterrupted transaction processing during
: backups for InnoDB, but can also backup MyISAM tables. Percona XtraDB is an
: enhanced version of InnoDB storage engine, designed to better scale on modern
: hardware and is the default InnoDB implementation in MariaDB.
Last edited:
Moshe1010
Well-known member
InnoDB Buffer Pool: 2.50G/2.50G (100.00%)
I'm wondering how much more RAM should I allocate to this thing.....
I guess I have no choice but to separate my testing area from this VPS. My database is 1.3GB (each, but the testing area is pretty dead, so I wouldn't expect to take much. I guess I'm wrong).
HittingSmoke
Active member
Run two MySQL instances. When you're not using the test instance shut it down.
https://dev.mysql.com/doc/refman/5.5/en/multiple-servers.html
Here's my latest stats: http://pastebin.com/B9qbrcnn
If anyone have spare time to review it, Thanks !
Similar threads
- Question
