← Back to team overview

maria-discuss team mailing list archive

Re: Performace issue with insert

 

Hi Justin and wh,

Please provide us more information about your observations 
We need at least some profiling information about what can cause such big difference . 

Report it to Jira MariaDB, you will get  help on how to proceed. 
Deeper profiling may be required  to understand the difference and improve. 

As Justin state, the partition code have been created with NDB cluster code in mind. 
I would suggest using a transactional engine ,  TokuDB is a good storage to compare with MyISAM    
  
/Stephane


Stéphane Varoqui, Senior Consultant
Phone: +33 695-926-401, skype: svaroqui
http://www.mariadb.com

Le Jan 30, 2016 à 12:29 AM, Justin Swanhart a écrit :

> Hi,
> 
> Queries are always going to pile up with MyISAM because write locks have higher priority than read locks, though you could try LOW_PRIORITY_UPDATES which will invert the lock priority, but could starve writes.
> 
> MySQL 5.6 changes the way locking works for partitions, and write locks are held only for one, not all partitions.  I'm not sure if MariaDB has ported these changes or not, but if not, a write lock will write-lock ALL partitions even when inserting into one.  
> 
> I would suggest that you switch to InnoDB, and investigate using smaller partitions (weekly instead of monthly) and also determine if 5.6 has better insertion performance after these changes, if so, consider using Oracle MySQL not MariaDB.
> 
> --Justin
> 
> On Fri, Jan 29, 2016 at 3:35 AM, walter harms <wharms@xxxxxx> wrote:
> 
> 
> Am 27.01.2016 20:05, schrieb Tom Worster:
> > Hi wh,
> >
> > If a table has any indexes then inset time increases with the number of
> > records in the table. Even with only one index, the difference between
> > inserting when the table is nearly empty and when it has many millions of
> > records can be dramatic.
> >
> > If you have some understanding of how conventional indexes work then this
> > effect can become fairly easy to imagine.
> >
> 
> 
> It is not that easy, since tables in my test 10.1.10 and mysql 5.1.53
> have the same setup. I would not be surprised if the difference had been
> 10% or so. But the actual difference is much more, causing jobs to pile up
> at peak times.
> 
> Unfortunately i have correct my self at one small point the engine is MyISAM.
> 
> re,
>  wh
> 
> > Tom
> >
> > On 1/27/16, 5:27 AM, "Maria-discuss on behalf of walter harms"
> > <maria-discuss-bounces+fsb=thefsb.org@xxxxxxxxxxxxxxxxxxx on behalf of
> > wharms@xxxxxx> wrote:
> >
> >> Hello list,
> >> i have a strange problem with inserts.
> >> The tables is large (time series data) and has a
> >> lot of partitions, engine is IMMODB.
> >> We have notice that the write performance decreases with
> >> time, start of the month good, end of the month bad.
> >>
> >> The same behavier is the lasted version of mariadb.
> >> But when i replace mariadb with mysql the problems vanishes
> >> (so its not a hardware issue). I suspect that it is a caching
> >> problem but a comparison of the configs did not give a hint.
> >> Anyone an idea what may cause the effect ?
> >>
> >>
> >> re,
> >> wh
> >>
> >> _______________________________________________
> >> Mailing list: https://launchpad.net/~maria-discuss
> >> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> >> Unsubscribe : https://launchpad.net/~maria-discuss
> >> More help   : https://help.launchpad.net/ListHelp
> >
> >
> 
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
> 
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp


Follow ups

References