Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am writing an indexer, using python, which indexes documents and insert them into Database, Before it was single process but now i made it to multiprocessing with 4 parallel processes running.After every text extraction , it insert into database and does a commit.

Now it hitting IO problem , the main IO Problem is not my process but EXT4's jdb2 , journeling system. It is at 99.99% and casuing CPU to wait for IO at every MySQL Commit.

I saw many having that problem on the internet and their solution is to mount using barrier = 0 . Would that disable Journaling totally ? My Servers have UPS and tempting to do it , should i ?

share|improve this question
    
Is all your data InnoDB ??? –  RolandoMySQLDBA Jul 9 '13 at 17:45

5 Answers 5

Put the database on a non-journaling file system. At least larger servers (oracle, sql server) have their own journal function (transaction log) and optimize their IO accordingly. You have log and database on separate file systems and discs and rely on database internal functionality for handling bad IO. There are normally no (larger setup) file system changes except write date anyway because files do not expand - they would be generated with their "final" size (ok, admins can change that), and changes are as I said tracked by the database level transaction log.

You may also want to tell us what your hardware layer is. Most people underestimate that IOPS is the limiting factor for a database and think a small disc set is a proper environment for a large database. While some of us work on databases using a larger number of discs, thus potentially supporting a higher number of IOPS.

share|improve this answer
    
I would modify this to using a filesystem not using the journal for data but only metadata. Ext4 can be configured this way as well. –  the-wabbit Feb 24 '12 at 13:55
    
Yes. At the end the jouirnal doubles the IO - and the database log will do the same again, so you wend up with a lot more IOPS than you have to. And redundancy that basically is not needed. The system jouirnalling is NICE to protect the file.... but useless when the application does so already, which databases do. –  TomTom Feb 24 '12 at 14:35
    
Which offers best performance at non-journaling? Thanks! –  V3ss0n Feb 27 '12 at 15:47

It is quite likely that your I/O backend is not coping with the load all that well. You should make sure your filesystem is not journaling data. I would suggest using the data=writeback,relatime,nobarrier parameters to mount for your database's data partition as the first quick&dirty optimization.

Also, deducing from your symptoms, you are apparently not using write caching with your controller. You should make sure you are using a battery-backed or flash-backed write cache on your controller and enable it - this should give you a significant performance boost without vastly increasing the risk of data loss or corruption. Note that using write cache without a battery or flash backup does increase the risk of data loss or corruption significantly - so only do this for testing purposes and/or if you can take the loss.

share|improve this answer
    
so how about : data=writeback,relatime,nobarrier and then totally disable mysql Logging? I think this would speed things up a lot? –  V3ss0n Feb 27 '12 at 15:49
    
hdpram -i shows that i am using write caching. so hmm ?? –  V3ss0n Feb 27 '12 at 16:02
    
@V3ss0n you can't disable logging for a transactional engine - it is the very heart of it. You might choose to move the transaction log to a different set of disks as it has a totally different access pattern (mostly linear writes) than your main database data (random read/writes) - this is a commonly recommended configuration. As for your storage setup: you are not using a RAID controller but simply individual disks with write cache on? This would not help any of your synchronous writes as they come with explicit cache flush requests. –  the-wabbit Feb 27 '12 at 17:24
    
Is nobarrier the same as barrier=0 ? –  Nicholas Tolley Cottrell Mar 4 at 20:48

There is always going to be a trade off between resiliency and performance.

With MySQL on ext4 the barriers=1 default does indeed cause a slow down, however the first action should not be to disable journaling or to turn on data=writeback.

First, if resiliency is of high importance, a battery backed RAID is certainly well worth it.

The mount options I have chosen, especially on non-battery backed RAID are:

/dev/mapper/vg-mysql--data  /var/lib/mysql/data ext4  defaults,noatime,nodiratime,barrier=1,data=ordered  0 0

This is intentionally not using data=writeback because I do not want to risk filesystem corruption resulting in "old data to appear in files after a crash and journal recovery" (quote is from man mount).

The ideal configuration in my.cnf for full resiliency around I/O related settings are:

[mysqld]
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

I have opted for the following sequence of trade-offs to increase performance:

  1. sync_binlog = 0: this is the first MySQL config that I change away from the full resiliency. The reason for this is that it gives a significant performance improvement, especially where binlog_format=row (unfortunately required for Jira). I am using enough MySQL replicas in the cluster that if the binlog were to become corrupted by a power loss scenario I would do a binary copy from another replica.
  2. innodb_flush_log_at_trx_commit = 2: While a value of 1 is required for full ACID compliance, with a value of 2 "the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues." (quote from MySQL docs)
  3. Update the mount options to use data=writeback. Note that if this is your root file system you will also need to pass a kernel command line option. I put together a few steps on that at coderwall.
  4. Test various values of innodb_flush_method. O_DIRECT is shown to improve performance in some workloads, but it's not a given that this will work in your environment.
  5. Upgrade to SSDs, in which case you'll also want to increase innodb_io_capacity, and tune settings such as innodb_adaptive_flushing, innodb_read_io_threads, innodb_write_io_threads, innodb_purge_threads, and other possible settings.
share|improve this answer

What database engine are you using to insert this data into?

If it's MyISAM: that must lock the entire table during a write, so running concurrent insert threads will kill ANY system, no matter how powerful.

Make sure you're using InnoDB for these tables.

share|improve this answer
    
Since he is committing transactions, the engine would not be MyISAM since MyISAM does not support transactions. –  the-wabbit Feb 24 '12 at 13:54
    
Arr, brainfart. –  adaptr Feb 24 '12 at 15:01
    
I am using innodb , mysql5.5 defaults to innodb. –  V3ss0n Feb 27 '12 at 15:45

Also, not directly related to mysql, but some HD have problems with ext4 due to aggressive power management... when that happens, the machine load increases without any apparent activity.

Try to disable it. first check whatever value you have (if you need to put it back without rebooting) and then disable it.

Check the current value:

    hdparm -B /dev/sda

Disable it

   hdparm -B 255 /dev/sda

(or whatever is your HD) and test. Probably will not help for most issues, but it might help some users out there. Rebooting will reset the value, or manually replace the 255 for the previous value.

If it helps, check the /etc/default/hdparm or /etc/hdparm.conf for a more permanent config, by setting it on boot.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.