maria-developers team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

Hi Daniel!
2014-11-25 5:54 GMT-02:00 Daniel Black <daniel.black@xxxxxxxxxxxxx>:

>
>
> ----- Original Message -----
> > hi Daniel
> >
> > the problems:
> >
> > 1) i have a application/database design problem, i don't have access to
> > source code, the application is running in a server, and database at
> other,
> > i have only access to database
> > 2) at database the problem is with myisam locking contention when running
> > slow updated and selects, update get a write lock and select wait it, no
> > problems with insert (yet), no deletes in this table (yet)
>
> have you used explain {query} on all of these to verify if there is an
> missing index problem?
>

yes, that's considered and very relevant to optimizer
i check profiling too, i will get a 10.1 version to check analyze too (the
real execution "explain")
i get slow queries mainly with slow log, and i consider creating a index,
and don't create too many index, sometimes i check if histograms could
help, i don't remember others options to 'tune' the optimizer, but that's
what i normally do when i found a slow query or users report about
contention




>
> > i tried to partition this table, but this don't reduce the write lock
> time,
> > the where part of update normally get rows from all partitions
>
> Don't consider partitioning yet. It probably won't help.
>
yeap :/ didn't work, i didn't tested shard with spider yet



>
> > 3) users report long times waiting data (this happen with long updates, i
> > confirm this with slow query log and processlist)
> >
> >
> > what i'm thinking as possible solutions, but i don't know if it's really
> > possible, or how complex it is
> >
> > 0) leave developer solve the problem rewriting code
> > the 'best' one, but i need to contact he, i'm not finding he, trying to
> > call, sending email and nothing...
>
> When you have indexes that need adding feed this back. When you see bad
> queries feed those back with details and a substitute query that does the
> same thing.
>
nice, i'm logging them to send to developer
sorry i don't have many contact with he to solve this easily, many thanks
helping me here


>
>
> > 1) convert myisam to innodb and check what happen
> > i'm creating another server to reproduce the load, this take sometime
> > (>100gb myisam tables)  it's executing from the first email
> > i will execute some logged queries to reproduce load, and check what
> > problems i will have
> > at query log there're queries without "where" part (possible slow queries
> > to innodb) and queries using count/min/max without "where" too.
>
> Don't remove the where part. It changes the query significantly and you
> don't have a comparison point. If your app doesn't do the queries without
> the wheres then why are you?
>
yeap, i think developer use the table as a statistic table, myisam
sometimes do a good job with this design, but innodb sometimes execute full
table scan when it don't have a where part
i was thinking about a way to rewrite the table without changing engine,
something like... if i found a query without where, rewrite to  read from a
statistic table, create some triggers to update this statistic table and
'solve' part of the problem, i don't know if this work, again just an idea


>
> If you get two servers replicating of each other.

yeap, i forgot the option to create replicas... i will check this, maybe
just a good balance solve the main problem


> Have one server with myisam tables, another with innodb and at some point
> switch the application to a different server making sure replication has
> caught up before writes are enabled.
>
> Make sure the innodb server is tuned for innodb with decent buffer pool
> size etc.
>
yeap, what i can do today without problem is get all queries from one
database log and test the same load at the other, ok this don't test the
application, i'm with doubts about BLOB inside innodb table, probably the
application test will get some feeling about this


>
> Use mariadb-10 on both. log_slow_verbosity=query_plan,explain (on both)
> will help a lot.

hum, nice, i only record the query and the time expent, i will record the
plain and explain now


> mysqldumpslow to assist in summarising all similar queries.
>
> Compare the results of the slow query log on a query by query basis.
>
nice, there's some percona tools that help here, i'm using it and raw log


>
> See if the problem is the query, the indexes or indeed the engine.
>
> Work out what's better overall by fixing indexes, suggesting query fixes
> to the developer and if something is really bad and shouldn't be give devs
> here details.
>
nice


>
> > 3) try other non standard mariadb engine?
>
> You could try tokudb the same way.
>
right, considering this too
what about replication with toku? i didn't tested yet, is something
different that i should pay attention?


>
> > 4) use faster storage to reduce write/read time, reducing lock time
> > i think that's not a good solution, but the last one i have, with a high
> > cost
>
> Lock time isn't related to storage. After fixing queries and indexes you
> still might have a slow hardware problem.
>
> Sometimes good solutions aren't free.
>
yes



>
> > do you see any other solution to this problems? any other experience like
> > this?
>
> Get details to identify the cause of the problems.
>
> New Relic can help breakdown web requests and information as to which DB
> queries occur in them. Might still be useful with closed source
> applications.
>
> > sorry i was reading the 'to' part of email, i selected the
> maria-developers
> > instead maria-discuss
>
> yes.
>
>
> --
> --
> Daniel Black, Engineer @ Open Query (http://openquery.com.au)
> Remote expertise & maintenance for MySQL/MariaDB server environments.
>

thanks Daniel



-- 
Roberto Spadim
SPAEmpresarial
Eng. Automação e Controle