LinuxQuestions.org
Latest LQ Deal: Complete CCNA, CCNP & Red Hat Certification Training Bundle
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices


Reply
  Search this Thread
Old 02-22-2016, 02:39 PM   #1
imadsani
Member
 
Registered: Aug 2013
Distribution: CentOS 6.5
Posts: 63

Rep: Reputation: Disabled
Mariadb eating disk - help!


I've reworked my setup to eliminate nginx and work solely with apache. This time I decided to use MariaDB 10.1 (innodb db), boy was this a mistake.

The application is a news website, apart from the standard news insertions to the db, news article read analytics are also recorded to the db. That means one sql entry for each page view (30-50 visits/second). Before all this the db was set to myisam, we were experiencing issues with table crashes so we decided to switch to innodb.

The problem: Since the move, mariadb has started to eat up all the space in /tmp. I don't have /tmp partitioned to a separate partition so it ends up eating all the disk. I've tried looking for something regarding this online but haven't been able to find anything that would help.

CPU consumption stands around 40-50% and overall server ram usage stands firmly at 5GB, hasn't gone above that the whole day today.

Server specs:
Xeon(R) CPU E5-1650 v3
64GB DDR4 ECC RAM
2 x 240GB SSDs (Software Raid 1)
CentOS 7.2

server.conf
Code:
[server]

[mysqld]

skip_name_resolve
default_storage_engine = InnoDB
tmp_table_size = 512M
max_heap_table_size = 512M
max_connect_errors = 10000
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size = 15G
innodb_buffer_pool_instances = 15
innodb_log_buffer_size = 128M
innodb_write_io_threads = 8
innodb_read_io_threads = 8

# LOGGING #

log_error = /var/log/mysql/mysqld.log
slow_query_log = 0
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
#log_queries_not_using_indexes = 1

[galera]

[embedded]

[mariadb]

[mariadb-10.1]

[mysqld_safe]
Here's a screenshot from newrelic for the past 3 hours

http://i1196.photobucket.com/albums/...psyoecot64.jpg


What am I doing wrong? Please help!
 
Old 02-22-2016, 04:29 PM   #2
themrrobert
Member
 
Registered: Feb 2007
Distribution: Debian Sid
Posts: 52

Rep: Reputation: 16
Are you using a lot of temporary tables or doing big table ALTER's?

Can you show us
$ ls -lh /tmp

Maybe see if there's anything in the mysql-slow.log
 
Old 02-22-2016, 11:28 PM   #3
imadsani
Member
 
Registered: Aug 2013
Distribution: CentOS 6.5
Posts: 63

Original Poster
Rep: Reputation: Disabled
I'm not sure about the first question, will ask the developers and get back on that.

Here's the result of ls -lh /tmp

Code:
~]# ls -lh /tmp
total 219M
-rw-rw---- 1 mysql mysql 219M Feb 23 09:22 #sql_57bb_0.MAD
-rw-rw---- 1 mysql mysql 8.0K Feb 23 09:22 #sql_57bb_0.MAI
I've been keeping an eye on the /tmp folder for the past couple of days and it usually goes up to 20GB and then comes down, always filling it up with files like the ones above.
 
Old 02-23-2016, 01:04 AM   #4
pan64
LQ Guru
 
Registered: Mar 2012
Location: Hungary
Distribution: debian/ubuntu/suse ...
Posts: 9,914

Rep: Reputation: 2921Reputation: 2921Reputation: 2921Reputation: 2921Reputation: 2921Reputation: 2921Reputation: 2921Reputation: 2921Reputation: 2921Reputation: 2921Reputation: 2921
I would say that is a very huge database or just badly designed. Probably you need to modify the internal structures, index tables.
http://www.mysqlperformancetuning.co...y-optimization
https://seravo.fi/2013/improve-mysql...ed-up-websites
 
Old 02-23-2016, 02:45 AM   #5
imadsani
Member
 
Registered: Aug 2013
Distribution: CentOS 6.5
Posts: 63

Original Poster
Rep: Reputation: Disabled
db size stands at 3.2GB
 
Old 02-23-2016, 03:12 AM   #6
pan64
LQ Guru
 
Registered: Mar 2012
Location: Hungary
Distribution: debian/ubuntu/suse ...
Posts: 9,914

Rep: Reputation: 2921Reputation: 2921Reputation: 2921Reputation: 2921Reputation: 2921Reputation: 2921Reputation: 2921Reputation: 2921Reputation: 2921Reputation: 2921Reputation: 2921
so for me it means your queries are really far from optimal. But obviously it is only my own guess.
 
Old 02-23-2016, 03:23 AM   #7
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=14, FreeBSD_10{.0|.1|.2}
Posts: 4,528
Blog Entries: 6

Rep: Reputation: 2437Reputation: 2437Reputation: 2437Reputation: 2437Reputation: 2437Reputation: 2437Reputation: 2437Reputation: 2437Reputation: 2437Reputation: 2437Reputation: 2437
It is not possible to say with any certainty with the information you have provided.

But just looking at the rough conditions, 30-50 visits per second, each with multiple but unknown number of queries, innodb tables which can require "extra" overhead for rollback ability, 3+GB db size... that can be a very busy database.

Then multiply by unknowns... normalized or ad-hoc schema? ... well validated queries? ... scripting language and code quality? ... post-query processing or buffering? ... various database config parameters?

I think you need someone with good depth of MySQL/MariaDB and relational model knowledge, and with access to the platform to get any kind of useful answers.
 
Old 02-26-2016, 08:50 AM   #8
Ramurd
Member
 
Registered: Mar 2009
Location: Rotterdam, the Netherlands
Distribution: Slackwarelinux
Posts: 678

Rep: Reputation: 105Reputation: 105
The filename style gives me the idea these are temporary storage files... like temp tables...
or... queries that do not auto-commit, and are waiting for the commit, thus never altering your database and the changes waiting for the actual commit.

Then again, the location of /tmp confuses me; I'd say these files would be in the same directory as the actual table(s)...

Another suggestion, as we encountered some nasty locking issues with MySQL (on an even larger database) and other performance metrics, we decided to switch to PostgreSQL; We actually saw performance increases along the lines of twice as fast or faster; both reads and updates...

As you can see in another thread, there's some other database we need to migrate, switching encoding is not always very easy; the rest was smooth as butter.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
ubuntu trusty 14.04 eating disk space davidrjones1066 Linux - Software 7 09-09-2014 04:20 PM
/var/lib/apt/list/partial eating all of the disk space. replica9000 Debian 1 06-16-2014 04:47 PM
Something's eating up my disk space TomConnolly Linux - Newbie 5 03-15-2013 02:44 AM
disk space eating...? WiseDraco Linux - Desktop 10 08-16-2011 04:13 AM
cron and sendmail are eating my hard disk longtex Linux - Software 3 10-05-2005 02:34 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 04:14 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration