Re: high-availability loadbalanced mysql server farm
If you're looking to load-balance the write requests... sorry, MySQL replication won't help much (if at all). Think about it... every insert/update/delete simply *has* to happen on every server. You only *send* it to one of them, sure... but then it replicates from that one to the other(s) and happens there too. In fact, replicated queries are executed in a single-threaded fashion on the slaves (or the other master, in the case of master-master)... it could very well be *slower*. If you need faster write speeds, you need to: 1) invest better hardware, or 2) somehow design your setup such that the MySQL servers don't all contain the full set of data* *If 1/2 your tables are on server A and the other 1/2 are on server B, then you've effectively split the read *and* write load between them. How to do this without modifying the frontend is an exercise left to the reader. :) Replication will help with load-balancing SELECT statements, but any modification statement still eventually has to happen everywhere, so the overall speed can't be faster than the slowest server. You might look into some of the more esoteric MySQL engines... I have virtually no experience beyond MyISAM and InnoDB, but perhaps some of the more complicated ones would make it easy to spread the data out over multiple servers and balance the load that way. Good luck, Jake On Wed, Jan 14, 2009 at 7:13 PM, xufeng xuf...@yuanjie.net wrote: Hi all, One website is based on LAMP(Linux+Apache+MySQL+PHP)(that is our case).We donot have very big tables or complicated database design.We only have one database. Because the php code is a third-party product we donot want to make much modification on the code. But when it comes to the underlying MySQL deployment,it is a problem. To loadbalance to write requests(insert,update...) from web program,we have some options to follow. One is master-master replication with a loadbalancer in front of the two MySQL master servers,and the loadbalancer could be LVS(it has been put into our production for years with stability and performance) or mysql-proxy(I am not sure of its stability in production). The other one is MySQL Cluster which is composed by some data nodes and mysql nodes and one management node. Our consideration is that the underlying MySQL server farm is transparent from the web program. Any suggestions will be welcomed. Thank you in advance. Yours Xu Feng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jakem...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: high-availability loadbalanced mysql server farm
Hi, Jake Maul wrote: *If 1/2 your tables are on server A and the other 1/2 are on server B, then you've effectively split the read *and* write load between them. How to do this without modifying the frontend is an exercise left to the reader. :) From what I've read in the past about MySQL Proxy, you can set it up so it examines the query being executed and directs it to a different server depending on your logic. So, in Jake's example, you could configure MySQL Proxy and if it contains tables that are on server A, send the query to server A, otherwise send it to server B. This could also help in replication situations - connect an application to the MySQL Proxy which talks to all master and slave servers. If the query is an UPDATE, INSERT or DELETE, direct it at the master only. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is deleting the .FRM, .MYD and .MYI files the same as dropping table?
Daevid Vincent dae...@daevid.com wrote on 01/15/2009 09:57:19 PM:
you misunderstand me. I have three servers (dev, test, prod) that all
have maybe 3 databases EACH that have all these eventum* tables in them.
don't ask. a simple trickle won't do. I'm writing a script to loop
through them all.
The script below will create the Drop Table command for all tables like
'eventum%'.
select CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
from information_schema.TABLES
where TABLE_NAME like 'eventum%' and TABLE_TYPE 'VIEW'
Donna D.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is deleting the .FRM, .MYD and .MYI files the same as dropping table?
I think you may be over-panicing. :-) If you do a DROP on the master and that replicates through, just like the create did, then you're all set. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
help refactoring query
I'm having some difficulty getting my head around a particular query. I'd like to make this a view once I get something working. However, all I've been able to come up with uses a sub-query. So, no view on the horizon. I have 3 tables: users id, (etc. the usual) disciplines id, name (ie. film, photography, writing, etc.) disciplines_users discipline_id, user_id Each user may have one or more discipline. The view I'm looking for shows the total number of users who have a particular discipline. NOTE: the sum of the totals is greater than the total number of users, which is by design. SELECT name, COUNT(discipline.u_id) AS total FROM ( SELECT du.discipline_id, du.user_id as u_id, d.name FROM disciplines_users AS du LEFT JOIN disciplines AS d ON d.id = du.discipline_id ) AS discipline GROUP BY discipline.name ORDER BY discipline.name; +-+---+ | name| total | +-+---+ | Dance | 176 | | Film and Television | 376 | etc. I've a feeling that this could be done without that sub-query and using another join. If not, I might make the sub-query its own view and see what the performance is like. I'd appreciate any suggestions, especially any pointers on refactoring sub-queries into joins, in general. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
