All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
What's your Average MySQL memory consumption ?
Hi all,
I'm no MySQL DBA or expert, and learning to fine tune it futher on my 5 different VPSs. All of them are running MariaDB... 2 are v10.1 another 3 run v5.5. All running default configurations with no tinkering on the my.conf's or tuning.
I have been monitoring and (strangely enough) the MySQL Average load remains around 700 - 900Mb
Now I'm wondering, :
a 2GB OpenVZ VPS Running 4-5 avg visits+DB wordpress sites (MyISAM only) consumes 700MB
another 4GB KVMVPS with 2 CakePHP + 2 Core PHP sites (mix of inno+MyISAM) runs around 800MB
another 8GB KVM VPS with 2 CakePHP high visit site, with a 6 GB Database (mix of inno+MyISAM) and 100K pageviews also hovers around 850-900MB
I'm trying to detect the similarity behavior here.
Also can you educate me how to read the HTOP VIRT | RES and deduce actual memory consumption? how can I monitor which site/threads are consuming how much memory exactly.
I'm scheduled to do some MySQL tuning and cut down my costs for VPSs. I've seen people successfully running (with Caching) massive WP sites on 512mb VPS only. Just trying to hone my skills to achieve something similar.
Comments
There's a good writeup about how mysql uses memory here (and how to optimize) http://www.narga.net/optimizing-apachephpmysql-low-memory-server/
Generally I decide on a mysql memory goal per server, usually around 50-60% of total available memory, giving the webserver and php the rest, and then adjust the confs over a period of days/weeks using http://mysqltuner.com/ until I'm satisfied.
@stoke,
How often would you run MySQL tuner on your servers?
2 of my servers are 5 & 3 Months old... the other 3 are 2 and 1 Weeks old only.
Which are the first Safe settings to be implimented as suggested by mysqltuner ?
On openvz multiple copies of MySQL on the same host server may share some memory space. I have a server running MySQL and it uses less than 150mb in total.
You can, and should run mysqltuner often, you can change values at any time.
Here's an example from a 2gb VPS that serves some busy wordpress sites.
Current MariaDB memory usage: 1.1G
Maximum possible memory usage: 1.8G
(php-fpm: 360-600mb)
(nginx: ~96mb)
[mysqld]
key_buffer = 150M
key_buffer_size=150M
query_cache_limit=4M
query_cache_size=92M
query_cache_type=1
table_open_cache=1024M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=768K
join_buffer_size=2M
thread_cache_size=80M
connect_timeout=10
max_connect_errors=10
max_allowed_packet=1M
tmp_table_size=92M
max_heap_table_size=92M
thread_concurrency=8 # 2*CPU
default_storage_engine=MyISAM
skip-innodb
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
22530 apache 20 0 363m 26m 4620 S 10.6 0.7 2:51.40 httpd
1617 mysql 20 0 1271m 32m 4360 S 7.0 0.9 724:17.90 mysqld
not bad on a kvm 8GB
Depends on your definition of bad. If you have 8GB of RAM and your MySQL is being heavily used, you definitely don't want the memory to be low like that, it will slow down your system.
Memory is very good for MySQL. You want MySQL to cache results to speed up your queries and lessen the load on your disks.
Try using the MySQL tuner tool. Just run the following and it will tell what you should be done with your MySQL server to make it faster:
Another recommendation would be to switch to MariaDB and use the TokuDB engine (instead of MyISAM or InnoDB)
"What's your Average MySQL memory consumption?"
About 16GB (tuned!) in our MariaDB Galera cluster. It's growing about 1GB monthly.
wooooo! wonder what's the Database Size ??? That's some serious DB transaction going on!
About 27GB in assorted client databases ranging from WordPress hosting to the energy production data for a green energy client.
0GB... SQLite
Yeah MariaDB! mines at about 700mb, but I could probably tweak it a bit again. Add a little more memory.
interesting fact... I'm thinking of putting all my Wordpress (one by one ofcourse) gradually to Sqlite only... any major consequences ? I guess some plugins can have problem, but some of my hosted high traffic WP's use very few, bare minimum plugins all checked to be supporting sqlite properly.
anyone running WP successfully on sqlite ?
I don't use WP so I cannot help you. But I just love SQLite due it's easy backup able and easier to host multiple databases without worrying about permissions.
Can you elaborate on what you mean by "0GB"? And are you implying SQLite3 uses more memory than older versions?
My MySQL uses 0GB/0MB/... RAM because I use SQLite. I use SQLite3. The "<3" is just a heart and not "lesser than three"