[Maria-discuss] Performace issue with insert
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
"We have notice that the write performance decreases with time, start of the month good, end of the month bad."
Do you happen to partition BY MONTH? if so it looks like the more the latest partition grows the slower it becomes.I am no expert in this but I think you should post the CREATE TABLE for this table. You should also tell the exact MariaDB and MySQL versions you have tried ("SELECT version();").
-- Peter -- not a MariaDB person
On Wed, Jan 27, 2016 at 11:27 AM, walter harms wharms@bfs.de 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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.
Tom
On 1/27/16, 5:27 AM, "Maria-discuss on behalf of walter harms" <maria-discuss-bounces+fsb=thefsb.org@lists.launchpad.net on behalf of wharms@bfs.de> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
what mariadb and mysql version?
2016-01-27 17:05 GMT-02:00 Tom Worster fsb@thefsb.org:
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.
Tom
On 1/27/16, 5:27 AM, "Maria-discuss on behalf of walter harms" <maria-discuss-bounces+fsb=thefsb.org@lists.launchpad.net on behalf of wharms@bfs.de> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Am 27.01.2016 um 20:08 schrieb Roberto Spadim:
what mariadb and mysql version?
he already answered that
the mariadb version we tried were 5.1.62 and 10.1.10 compared with mysql 5.1.53
2016-01-27 17:05 GMT-02:00 Tom Worster <fsb@thefsb.org mailto:fsb@thefsb.org>:
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.
Tom
On 1/27/16, 5:27 AM, "Maria-discuss on behalf of walter harms" <maria-discuss-bounces+fsb=thefsb.org@lists.launchpad.net mailto:thefsb.org@lists.launchpad.net on behalf of wharms@bfs.de mailto:wharms@bfs.de> 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 ?
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@lists.launchpad.net on behalf of wharms@bfs.de> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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@bfs.de 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@lists.launchpad.net on behalf of wharms@bfs.de> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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@bfs.de 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@lists.launchpad.net on behalf of wharms@bfs.de> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Am 30.01.2016 03:07, schrieb Stephane VAROQUI:
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
Aktualy I do now some profiling now we want to see the differences when switching 31-1. We used myISAM since the biggest problem is speed and immoDB showed to be crash sensitive. We store long time series data so the system is writing data all the time.
FYI: we consider for now both options reducing partitions sizes (downside that would explode the number on fp), and switching back to mysql. But it seems that tokuDB is good at inserts, maybe that is a point.
re, wh
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@bfs.de 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@lists.launchpad.net on behalf of wharms@bfs.de> 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
Am 30.01.2016 um 21:07 schrieb walter harms:
Aktualy I do now some profiling now we want to see the differences when switching 31-1. We used myISAM since the biggest problem is speed and immoDB showed to be crash sensitive. We store long time series data so the system is writing data all the time.
for "writing data all the time" MyISAM is for sure a completly wrong decision because the performance strength of MyISAM was always on most-read workloads
MyISAM *always* does a *complete table lock* for writes and don't allow concurrent writes without locking - that don't scale when you write all day long and there are table locks all day long
what do you mean with "immoDB showed to be crash sensitive" (besides i assume you mean InnoDB) - MyISAM is crash sensitive and needs repairs everytime something crashs - InnoDB is crash safe (as for as something can be crash safe)
Hi, Reindl!
On Jan 30, Reindl Harald wrote:
Am 30.01.2016 um 21:07 schrieb walter harms:
Aktualy I do now some profiling now we want to see the differences when switching 31-1. We used myISAM since the biggest problem is speed and immoDB showed to be crash sensitive. We store long time series data so the system is writing data all the time.
for "writing data all the time" MyISAM is for sure a completly wrong decision because the performance strength of MyISAM was always on most-read workloads
MyISAM *always* does a *complete table lock* for writes and don't allow concurrent writes without locking - that don't scale when you write all day long and there are table locks all day long
MyISAM should perform very good if inserts are *append only* (no updates or deletes). In this case MyISAM will not use an exclusive table lock and concurrent reads will be allowed.
It is typical for some kind of logging - one threads inserts *all the time* other threads are reading the data concurrently.
In fact, this is one of the use cases MyISAM was written for.
Regards, Sergei Chief Architect MariaDB and security@mariadb.org
Am 31.01.2016 um 09:09 schrieb Sergei Golubchik:
Hi, Reindl!
On Jan 30, Reindl Harald wrote:
Am 30.01.2016 um 21:07 schrieb walter harms:
Aktualy I do now some profiling now we want to see the differences when switching 31-1. We used myISAM since the biggest problem is speed and immoDB showed to be crash sensitive. We store long time series data so the system is writing data all the time.
for "writing data all the time" MyISAM is for sure a completly wrong decision because the performance strength of MyISAM was always on most-read workloads
MyISAM *always* does a *complete table lock* for writes and don't allow concurrent writes without locking - that don't scale when you write all day long and there are table locks all day long
MyISAM should perform very good if inserts are *append only* (no updates or deletes). In this case MyISAM will not use an exclusive table lock and concurrent reads will be allowed
but only if you do *nothing else*
i had servers going down by 50% append and 50% update a key-field after mail-confirmation and so 50% insert / 50 % updates + locked reads for display the page
solution was to create a second table, append and update there and every 15 minutes via cron batch-insert into the main table
in that case InnoDB was no option because the only table out of 6000 on that machine which woul dhave needed it and i hate this stupid global tablespace which exists even with files_per_table
if mariadb has not updated partitioning like 5.6 did, then any writing locks all partitions for write, and the append-only functionality won't work as expected because all partitions are locked, and reads will queue up because writes to different partitions will block all reads and writes.
On Sun, Jan 31, 2016 at 3:09 AM, Sergei Golubchik serg@mariadb.org wrote:
Hi, Reindl!
On Jan 30, Reindl Harald wrote:
Am 30.01.2016 um 21:07 schrieb walter harms:
Aktualy I do now some profiling now we want to see the differences when switching 31-1. We used myISAM since the biggest problem is speed and immoDB showed to be crash sensitive. We store long time series data so the system is writing data all the time.
for "writing data all the time" MyISAM is for sure a completly wrong decision because the performance strength of MyISAM was always on most-read workloads
MyISAM *always* does a *complete table lock* for writes and don't allow concurrent writes without locking - that don't scale when you write all day long and there are table locks all day long
MyISAM should perform very good if inserts are *append only* (no updates or deletes). In this case MyISAM will not use an exclusive table lock and concurrent reads will be allowed.
It is typical for some kind of logging - one threads inserts *all the time* other threads are reading the data concurrently.
In fact, this is one of the use cases MyISAM was written for.
Regards, Sergei Chief Architect MariaDB and security@mariadb.org
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Am 31.01.2016 09:09, schrieb Sergei Golubchik:
Hi, Reindl!
On Jan 30, Reindl Harald wrote:
Am 30.01.2016 um 21:07 schrieb walter harms:
Aktualy I do now some profiling now we want to see the differences when switching 31-1. We used myISAM since the biggest problem is speed and immoDB showed to be crash sensitive. We store long time series data so the system is writing data all the time.
for "writing data all the time" MyISAM is for sure a completly wrong decision because the performance strength of MyISAM was always on most-read workloads
MyISAM *always* does a *complete table lock* for writes and don't allow concurrent writes without locking - that don't scale when you write all day long and there are table locks all day long
MyISAM should perform very good if inserts are *append only* (no updates or deletes). In this case MyISAM will not use an exclusive table lock and concurrent reads will be allowed.
It is typical for some kind of logging - one threads inserts *all the time* other threads are reading the data concurrently.
In fact, this is one of the use cases MyISAM was written for.
This is was we actualy do with, never delete, very few updates and read is no performance problem.
"crash sensitive" translates into: We had 3-4 occassions where an full backup was needed, and all had all to do with immoDB tables broken beyond repair.
re, wh
The only time an innodb table is broken is if the computer lost writes to the system or the media is corrupted. InnoDB is fully transactional. If you had similar problems and MyISAM "works" it will likely have bad data in it, because myisam has no checksums. MyISAM is the kiss of death, it is old, not really supported, it has concurrency problems and it is unreliable after a crash without a lengthy repair.
--Justin
On Sun, Jan 31, 2016 at 2:44 PM, walter harms wharms@bfs.de wrote:
Am 31.01.2016 09:09, schrieb Sergei Golubchik:
Hi, Reindl!
On Jan 30, Reindl Harald wrote:
Am 30.01.2016 um 21:07 schrieb walter harms:
Aktualy I do now some profiling now we want to see the differences when switching 31-1. We used myISAM since the biggest problem is speed and immoDB showed to be crash sensitive. We store long time series data so the system is writing data all the time.
for "writing data all the time" MyISAM is for sure a completly wrong decision because the performance strength of MyISAM was always on most-read workloads
MyISAM *always* does a *complete table lock* for writes and don't allow concurrent writes without locking - that don't scale when you write all day long and there are table locks all day long
MyISAM should perform very good if inserts are *append only* (no updates or deletes). In this case MyISAM will not use an exclusive table lock and concurrent reads will be allowed.
It is typical for some kind of logging - one threads inserts *all the time* other threads are reading the data concurrently.
In fact, this is one of the use cases MyISAM was written for.
This is was we actualy do with, never delete, very few updates and read is no performance problem.
"crash sensitive" translates into: We had 3-4 occassions where an full backup was needed, and all had all to do with immoDB tables broken beyond repair.
re, wh
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Am 31.01.2016 um 20:44 schrieb walter harms:
"crash sensitive" translates into: We had 3-4 occassions where an full backup was needed, and all had all to do with immoDB tables broken beyond repair
InnoDB is transaction safe and much more relieable in case of crashes when your hardware ist not broken at all because it has checksums
MyISAM needs often a manual repair after crashes and is not really reliable in such cases - just because it starts don't say anything about how relieable your data are
*please* spell InnoDB correct it hurts!
Am 31.01.2016 22:01, schrieb Reindl Harald:
Am 31.01.2016 um 20:44 schrieb walter harms:
"crash sensitive" translates into: We had 3-4 occassions where an full backup was needed, and all had all to do with immoDB tables broken beyond repair
InnoDB is transaction safe and much more relieable in case of crashes when your hardware ist not broken at all because it has checksums
MyISAM needs often a manual repair after crashes and is not really reliable in such cases - just because it starts don't say anything about how relieable your data are
*please* spell InnoDB correct it hurts!
I will try to stay with InnoDB spelling :)
Here are the test results: Both boxes are the same concerning hard/soft-ware except mariadb/mysql of cause.
methode used: time -f „%E %w“ insert.programm
Box mariadb 10.1: Total: 89274 Distribution max: [cnt] - [s]
22633 00.05 23448 00.06 12000 00.07
There is a second smaller peak: 1019 00.47 1109 00.48 1014 00.49
There is a huge tailing until 2.6 (usually 1 entry)
Box mysql 5.1 Total: 68638 Distribution max: [cnt] - [s]
22766 00.02 11785 00.03
tailing until 0.56
I did no further analysis.
re, wh
participants (8)
-
Justin Swanhart -
Peter Laursen -
Reindl Harald -
Roberto Spadim -
Sergei Golubchik -
Stephane VAROQUI -
Tom Worster -
walter harms