Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 0 down vote favorite

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

3 Answers

up vote 1 down vote

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. – syneticon-dj 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
up vote 1 down vote

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. – syneticon-dj Feb 27 '12 at 17:24
up vote 0 down vote

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. – syneticon-dj 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

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.