Struggling with new install

[Mindzipper]

let me preface this by saying, We have many years experience with MySQL, we have little to no experience with Transactional databases (innodb) so I'm a newbie all over again.

I have my forum running, but it is excrutiatingly slow when importing. I mean disgustingly so. I installed a 3rd party theme (digi) and i did it on a sandox install i have on the RDS service at Amazon. At amazon it installed in a few seconds. On my server? 30 minutes minimum. 45 is closer to accurate.

the front end runs smooth and ast, it is ONLY an issue when i import.

I could survive this, but i have to do a vb4 import of a forum that's been running for 7 years, and is 1.1gb with NO files in the db. they're stored in the filesystem. that is pure db text. over a million if you include PMs and threads/posts. it would take a month.

i tried to do the import, and i can't even do it. I can import the users, mods, all the way down to the forums. everything imports fine, but is slow. When I get to the threads/posts? i get the error that MySQL has 'gone away'. my gut feeling is i'm running out fo RAM. this is a dedicated DB server running at Digital Ocean. it's a $20.00 server running CentOS 7, and MariaDB

here is my config. I followed the advice of a mod in the troubleshooting forum, and went aggressive with my settings, then backed them off. nothing helped my speed.

here is my config. has anyone got an aswer? this db server is very empty. it's only running 1 other db. then this one. I'm starting to stress because I started trying to import at 11pm. it is now 6 am, and I am NOWHWHERE.

I ended up wiping my entire install. no sweat I think, i'll just restore the db dump I made prior to importing. Wrong..... duplicate entries, dump fail. so i had to install fresh.... which i've done, but I need to figure this out before trying again.

here is my config

user=mysql
bind-address=0.0.0.0
connect_timeout=10
wait_timeout=15
local-infile=0
log_warnings=0
log-bin=/var/lib/mysql/data/mysqld-bin.log
log-bin-index=/var/lib/mysql/data/mysqld-bin.index

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# DEFAULT CHARACTER SET AND COLLATION
character-set-server=utf8
collation-server=utf8_general_ci

# SSL
ssl-ca=/etc/mysql.d/ca-cert.pem
ssl-cert=/etc/mysql.d/mysql-server.pem
#ssl-key=/etc/mysql.d/server-key.pem

# LOCATIONS
datadir=/var/lib/mysql/data
tmpdir=/tmp/.mysql
socket=/var/lib/mysql/mysql.sock

# PASSWORDS
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=0

# CONNECTIONS & THREADS
max_connections=60
max_delayed_threads=120

anyone got any ideas?

 

[Mike]

I don't see any performance tuning whatsoever in that config. No MyISAM tuning or InnoDB tuning.

The exact tuning you do varies based on what the server is being used for. If it's more than just a DB (in production at least), then you need to leave more memory around for other actions. If you're using MyISAM data, you need to ensure memory is available for those tables. As XF uses InnoDB primary, the most important elements will be those configs. For a tuning primer, see here: http://www.percona.com/blog/2013/09/20/innodb-performance-optimization-basics-updated/ (Note that XF does still use MyISAM for a few things, so it's worth giving some memory to it.)

I've not tried it before, but you can try Percona's wizard to see what it gives you for config recommendations: https://tools.percona.com/wizard

 

[Mindzipper]

you know, I might have pulled the old config. i'll check

 

[Sheratan]

You may check your PC. Your mysql conf is really... Something.

 

[Mindzipper]

yeah lol i copied the default backup me thinks. didn't even look when i pasted. been a very long night and my brain is a bit squishy right now. i'll post it when i get back up

 

[Mindzipper]

Just a follow up here.

I've changed direction on this, and have migrated to a single platform with both web and mysql services on it. So i'll follow up with my progress late tonight.