mariadb 10 compatability and issues?

soaringeagle
@soaringeagle
10 years ago
3,304 posts
ii upgraded from mysql to mariadb 10 last night
now the cpu use was all over the place but always many times higher then usual
router.php seemed to spawn way ore instances using more ram and cpu
everything seemed to use way more cpu it was often off the charts causing instability even ram and swap at 1 point for no explainable reason maxed out

mysql activity shows it doing the same repeating join sequence over and over
| 210888 | greentec_jam | localhost | greentec_jam | Query | 2 | Sending data | SELECT STRAIGHT_JOIN a.`_item_id` AS _item_id FROM jr_jrgallery_item_key a
JOIN jr_jrgallery_item_ke | 0.000 |
| 210956 | greentec_jam | localhost | greentec_jam | Sleep | 0 | | NULL | 0.000 |
| 210961 | greentec_jam | localhost | greentec_jam | Query | 2 | Sending data | SELECT STRAIGHT_JOIN a.`_item_id` AS _item_id FROM jr_jrgallery_item_key a
JOIN jr_jrgallery_item_ke | 0.000 |
| 210974 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |
| 210988 | greentec_jam | localhost | greentec_jam | Query | 0 | Sending data | SELECT STRAIGHT_JOIN a.`_item_id` AS _item_id FROM jr_jrrating_item_key a
JOIN jr_jrrating_item_key | 0.000 |



--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

updated by @soaringeagle: 01/23/16 04:40:02PM
brian
@brian
10 years ago
10,149 posts
I've been running MariaDB 10.1 here for a while on my dev server and there are no issues I am aware of.

Make sure you've set things correct for mariadb in your my.cnf

The queries you have shows are fine - they've been running a max of 2 seconds.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
soaringeagle
@soaringeagle
10 years ago
3,304 posts
what settings do you suggest for 8 coresw 32 gigs ram
lots of tables

[root@server ~]# perl mysqltuner.pl
>> MySQLTuner 1.6.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.0.21-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
[--] Data in MyISAM tables: 1G (Tables: 2350)
[--] Data in InnoDB tables: 1G (Tables: 257)
[!!] Total fragmented tables: 41

-------- Security Recommendations -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[--] There is 605 basic passwords in the list.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3h 24m 46s (2M q [200.930 qps], 185K conn, TX: 12B, RX: 28B)
[--] Reads / Writes: 82% / 18%
[--] Binary logging is disabled
[--] Total buffers: 16.0G global + 60.5M per thread (151 max threads)
[OK] Maximum reached memory usage: 19.7G (63.06% of installed RAM)
[OK] Maximum possible memory usage: 24.9G (79.67% of installed RAM)
[OK] Slow queries: 0% (4/2M)
[OK] Highest usage of available connections: 41% (63/151)
[OK] Aborted connections: 0.03% (49/185353)
[OK] Query cache efficiency: 35.5% (928K cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (232 temp sorts / 127K sorts)
[OK] Temporary tables created on disk: 23% (18K on disk / 79K total)
[OK] Thread cache hit rate: 99% (63 created / 185K connections)
[OK] Table cache hit rate: 100% (3K open / 3K opened)
[OK] Open file limit used: 5% (4K/86K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 20.9% (224M used / 1B cache)
[OK] Key buffer size / total MyISAM indexes: 1.0G/270.5M
[OK] Read Key buffer hit rate: 99.7% (12M cached / 38K reads)
[!!] Write Key buffer hit rate: 29.5% (1M cached / 1M writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 8.9G/1.1G
[OK] InnoDB buffer pool instances: 8
[!!] InnoDB Used buffer: 9.40% (54700 used/ 582136 total)
[OK] InnoDB Read buffer efficiency: 100.00% (4516076531 hits/ 4516112296 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 35801 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
[root@server ~]#



current my.cnf

[mysqld]
query_cache_size        = 2G
tmp_table_size       = 4G
max_heap_table_size  = 4G
sort_buffer_size = 30M
thread_cache_size = 160
table_open_cache = 42997
innodb_buffer_pool_size = 9096M
innodb_buffer_pool_instances =8
read_rnd_buffer_size = 30M
key_buffer =1G





--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
brian
@brian
10 years ago
10,149 posts
This is the my.cnf we use on a 32G JR Hosting server:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1
#
# * Fine Tuning
#
key_buffer              = 1024M
max_allowed_packet      = 32M
table_cache             = 1536
table_definition_cache  = 1536
open_files_limit        = 2048
tmp_table_size          = 512M
max_heap_table_size     = 512M
sort_buffer_size        = 4M
read_buffer_size        = 4M
read_rnd_buffer_size    = 768K
max_connections         = 200
innodb_buffer_pool_size = 16G
innodb_flush_method     = O_DIRECT
ft_min_word_len         = 3
ft_stopword_file        = ""

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit	= 1M
query_cache_size        = 512M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time     = 3

# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 100M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/



--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net

updated by @brian: 10/22/15 11:36:37AM
soaringeagle
@soaringeagle
10 years ago
3,304 posts
and these dont look fine to you do they
1.jpg
1.jpg  •  225KB

2.jpg
2.jpg  •  248KB




--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
brian
@brian
10 years ago
10,149 posts
Also - we have this as innodb_config.cnf in the /etc/mysql/conf.d directory:

[mysqld]
innodb_file_per_table
innodb_log_file_size = 2G



--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
brian
@brian
10 years ago
10,149 posts
soaringeagle:
and these dont look fine to you do they

Yeah you have something wrong with your server - with your mariadb conf you should not be eating up all your RAM like that.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
soaringeagle
@soaringeagle
10 years ago
3,304 posts
ok cpu is reasonable but way higher then at the same load level uning mysql
but its art a point that i can watch it and tune it every 24 hurs till i get it right...i hope
i kinda had to chop yurs to peices to get it to work since u had it set specificly for the jr servers had to remove everal chunks
the thread_concurrency = 10 should be 8 with 8 cores right


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
soaringeagle
@soaringeagle
10 years ago
3,304 posts
its still kinda out of control high not as high but pushing the limits


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
brian
@brian
10 years ago
10,149 posts
soaringeagle:
the thread_concurrency = 10 should be 8 with 8 cores right

no - you're not running Solaris so you can ignore thread_concurrency.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
brian
@brian
10 years ago
10,149 posts
Just to add too that I've seen about a 40% improvement going from MySQL 5.5 to MariaDB 10.1 - we're looking at making it the default SQL server on Jamroom Hosting in the future.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
soaringeagle
@soaringeagle
10 years ago
3,304 posts
ok thnx i have mariadb techs caling me later to try to get it figured out


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
soaringeagle
@soaringeagle
10 years ago
3,304 posts
im using 10.0.12
maybe need to upgrade t 10.1
the cpu is now over 22.0


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
brian
@brian
10 years ago
10,149 posts
What is causing the load? You should be able to do a SHOW PROCESSLIST; and see if it is long running processes in MariaDB. If it's not, you want to look at other servers running on server (i.e. your search stuff, anything else, etc.).


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
soaringeagle
@soaringeagle
10 years ago
3,304 posts
processlist does not show a liong priocess list but top shows mysql causing the massive load 1 sec ill grab ss

ok thats the longest processlist ive seen
but still
1.jpg
1.jpg  •  295KB

2.jpg
2.jpg  •  384KB




--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
brian
@brian
10 years ago
10,149 posts
Looks like Tomcat is causing you some issues - the MySQL queries from JR are not that long, but I do see some long running router.php jobs - but those can be queues.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
brian
@brian
10 years ago
10,149 posts
the top one is form "snagssharkteeth.com" - is that a JR domain?


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
soaringeagle
@soaringeagle
10 years ago
3,304 posts
tomcat before the mariadb upgrade always ran at 0.01 cpu it njever did this till the maruiadb upgrade
its required for solr but since i have not yet completed solr implimentation ill remove it and rebuild apache


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
soaringeagle
@soaringeagle
10 years ago
3,304 posts
brian:
the top one is form "snagssharkteeth.com" - is that a JR domain?

no thats the server name i shared iit with someone for Awhile splitting costs and was just easier to keep the server name
that site no longer exists really, it was a zencart ite selling 40 million year old dinasaur sharks teeth he since joined a cult, or his lemon sized brain tumor he hd for 11 years came back cause now he thinks the earths flat and men kept dinasaurs as pets only a few thousand years ago...

in otherwords he was not the ideal artner to share a server with haha i had to have all is logins to do everything for him cause he couldnt figure out simple things


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
soaringeagle
@soaringeagle
10 years ago
3,304 posts
removed tomcat rebuilt apache still very very high and still wayyyyy more router.php running then i ever see even when im putting a high load on it
i think its gotta be the version


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
brian
@brian
10 years ago
10,149 posts
soaringeagle:
removed tomcat rebuilt apache still very very high and still wayyyyy more router.php running then i ever see even when im putting a high load on it
i think its gotta be the version

I'm not sure what to say - from your posts I've not seen a single JR query that is taking more than 2 seconds.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
brian
@brian
10 years ago
10,149 posts
Just to add we've been running 5.2.40 on Jamroom.net here since yesterday and the load is fine. There WERE performance improvements for ordering in JR 5.2.40 - tested on both MySQL and MariaDB 10.1 - if anything it should be better than it was previously.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
brian
@brian
10 years ago
10,149 posts
One thing I just thought of - are you still running your search engine? Make sure you DISABLE the Core -> Global Config -> Listings => Use Optimized Pager.

You don't want that on your system or it will never get to the "end" of a list.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
soaringeagle
@soaringeagle
10 years ago
3,304 posts
is that why the pagination never ends?


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
brian
@brian
10 years ago
10,149 posts
soaringeagle:
is that why the pagination never ends?

Yep. Turn that off if it is confusing your search engine.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
soaringeagle
@soaringeagle
10 years ago
3,304 posts
no im not using the search engine now but i turned it off there was still a next page button on every page and it goes on forever


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
brian
@brian
10 years ago
10,149 posts
soaringeagle:
no im not using the search engine now but i turned it off there was still a next page button on every page and it goes on forever

Just be aware that it will cause a higher DB load with that off - since it has to run each query twice (once to get the total count of items that match the search conditions + once to get the actual data). With JR's "optimized pager" it can avoid that since it just shows you prev/next page links (it doesn't have to figure out how many pages). When you get the end it just wraps back to the beginning.

So just an FYI.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
soaringeagle
@soaringeagle
10 years ago
3,304 posts
its not wrapping back to the beginning its continuing indefinateky with blank pages


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
brian
@brian
10 years ago
10,149 posts
soaringeagle:
its not wrapping back to the beginning its continuing indefinateky with blank pages

Just wanted to add - update to Core 5.2.41 - I noticed a very high load on one of our largest Jamroom hosting customers when they upgraded to 5.2.40 - I found the root cause and released a fix in 5.2.41. Let me know if that helps.

Thanks!


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
soaringeagle
@soaringeagle
10 years ago
3,304 posts
better, though need to let mysql run longer to build cache and see how much better


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

Tags