maria-developers team mailing list archive

[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?

> 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.

> 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.


> 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?

If you get two servers replicating of each other. 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.

Use mariadb-10 on both. log_slow_verbosity=query_plan,explain (on both) will help a lot. mysqldumpslow to assist in summarising all similar queries.

Compare the results of the slow query log on a query by query basis.

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.

> 3) try other non standard mariadb engine?

You could try tokudb the same way.
 
> 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.

> 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.