Re: Multiple Cores.
MySQL processes each request in a separate thread... hence, it is *always* multi-core capable. Check the thread_concurrency setting in my.cnf. Also, although not exactly related, check the size of thread_cache. Are you by chance running 1 long-running query at a time? A single query runs in a single thread, hence can never use more than one core. So if your workload is dominated by 1 long-running query after another, it may seem like it's using only one core. Periodically check the output of "mysqladmin status"... if the thread counter is > 1, you're using multiple cores... assuming, of course, that your OS actually *knows* about them (ie: you're running an SMP-aware OS kernel). Jake On Mon, Dec 8, 2008 at 4:52 AM, Kunal Jain <[EMAIL PROTECTED]> wrote: > How we can configure Mysql in such a way so that i start using all the cores > of CPU. I Have a QuadCore server but somehow mysql use only single core > whose usage percentage goes upto 99% while other three cores remains idle. > > Any Idea or Multiple Core/CPU is wastage. > > -- > Kunal Jain, burrp! > http://burrp.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: On fighting with master-slave replication lag
There's a couple ways to go about this. The simplest thing (and what we use in most of our simple monitoring cases) is a shell script that checks the output of "show slave status\G" on the slave periodically. If it reports that either thread isn't running, or there's something in "Last error", or the time behind master is too long, it throws an error and alerts us in our monitoring software. We do something similar for non-replication checking... we parse the output of "mysqladmin status". Slightly more complicated (and also probably more accurate- the time reported by show slave status is known to be unreliable in some cases) would be a script that inserts a row into a table, then check the slave over and over till it arrives. Or even better, insert 2 values... a timestamp that *you* provide (in a shell script, something like $(date) would work) and a timestamp generated by MySQL assuming the times are syncronized on the master, slave, and the box you're inserting from, when the insert hits the slave it'll generate it's own timestamp, which you can then subtract *your* timestamp from. There's also a tool in maatkit which does replication tracking, although I've not yet used it. Judging by the other tools in that package though, it's probably pretty decent :). Jake On Mon, Dec 22, 2008 at 8:26 PM, xufeng wrote: > Hello everyone, > In my production system, I set up MySQL 5.0.67 master/slave replication, and > recently I met with master/slave replication lag problem. > Is there a good monitoring tool or some other tools to detect and discover > this latency on slave? > Any suggestion is 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: Performance tips
I few random things come to mind... 1) Try the query with IGNORE INDEX calldate_idx ... I can't see how this could possibly be faster, but I always like to check anyway. In your case this should result in a full table scan, given the information you've given us. 2) If the performance problem comes from the date_format() function, there might not be much you can do about it, except to call it less often. Maybe you could add one or more WHERE clauses to restrict the range of rows you're looking at? (WHERE calldate > "2000-01-01 01:01:01" or something). 3) Obviously it'd probably be faster if you weren't using SQL_NO_CACHE... guessing you just did that to show us what it's like that way? 4) I'd check the values of sort_buffer_size and read_rnd_buffer_size. Perhaps you could benefit from raising them slightly. As for general performance tips, Here's a couple scripts I like to use that give some decent tuning advice: http://www.day32.com/MySQL/ http://wiki.mysqltuner.com/MySQLTuner Neither of these will be specific to this particular query of course. There is a query profiler tool in maatkit (http://www.maatkit.org/), however... might be worth a shot. Good luck, Jake On Sat, Dec 27, 2008 at 10:15 AM, Chris Picton wrote: > Hi > > I am trying to get to grips with understanding mysql performance. > > I have the following query: > > select sql_no_cache count(*), date_format(calldate, '%y-%m-%d') as m from > cdr_warehouse group by m; > > This gives me: > 115 rows in set (59.52 sec) > > > mysql> explain select count(*), date_format(calldate, '%y-%m-%d') as m from > cdr_warehouse group by m\G > *** 1. row *** > id: 1 > select_type: SIMPLE >table: cdr_warehouse > type: index > possible_keys: NULL > key: calldate_idx > key_len: 8 > ref: NULL > rows: 43708571 >Extra: Using index; Using temporary; Using filesort > 1 row in set (0.00 sec) > > > mysql> show keys from cdr_warehouse \G; > *** 1. row *** > Table: cdr_warehouse > Non_unique: 1 >Key_name: uploaded_idx > Seq_in_index: 1 > Column_name: uploaded > Collation: A > Cardinality: 66 >Sub_part: NULL > Packed: NULL >Null: > Index_type: BTREE > Comment: > *** 2. row *** > Table: cdr_warehouse > Non_unique: 1 >Key_name: calldate_idx > Seq_in_index: 1 > Column_name: calldate > Collation: A > Cardinality: 5526774 >Sub_part: NULL > Packed: NULL >Null: > Index_type: BTREE > Comment: > > > How can I improve the running speed of this query? I am running 5.1.30, but > don't (yet) want to partition the table (horizontally or vertically). > Nothing else on the server is touching this table at the moment. The exact > date_format is not important, as I may want to group by 5 second intervals, > or full months. > > Any tips/ideas for me? > > Chris > > > -- > 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: Setup a replication slave without stopping master
If you're making backups of the DB, it might be possible to use the backup data as a replication snapshot for kickstarting the slave. You would need to be recording the master log file and position at the time the backups are made as well, however (usually easy to hack in if you're not already recording this). You are making backups, right? :) Here's another possibility, although not one I would really recommend: 1) Import all schema's into the slave 2) Set up replication (don't bother starting slave since it'll probably fail right away, unless you only do INSERT, never UPDATE/DELETE) 3) Get maatkit 4) Use mk-table-checksum --replicate on the master... this will lock your tables one at a time. 5) Use mk-table-sync --synctomaster on the slave to fix all the problems (that is, everything). The time to do this might not be much shorter than the 'correct' method of mysqldump --master-data or tar/mysqlhotcopy, but at least your tables would be only locked one at a time and not all at once (I think). Other than that, there's not a lot you can do. If you don't stop the db entirely, you'll at least still be able to read from it while you do your mysqldump / mysqlhotcopy / mk-table-checksum. Jake On Tue, Jan 6, 2009 at 4:04 AM, Claudio Nanni wrote: > All, Happy New Year, and let's hope somebody tries to stop the killing in > gaza. > > I need to setup a replication slave off a master that is currently > production and not stoppable or lockable(possibly). > > Do you have any idea on how to setup the slave with minimum or no impact on > the master? > > The database is about 80GB. > > Thanks > > C. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Setup a replication slave without stopping master
Sounds like you already know the score. Yeah, we use slaves as 'backup-capable' servers too... sometimes to the point of having a separate slave who's sole purpose in life is to be taken down and backed up. :) As it happens, I worked with mk-table-checksum and mk-table-sync some today. On a ~11GB database (size of the mysql dump files, that is), it took approximately 15 minutes to checksum everything, locking the tables one-by-one. HOWEVER, attempting to fix the errors I had on the slaves seemed to cause a GLOBAL lock on the master, and was not very fast at all... had to kill it after ~10 minutes with comparatively minimal progress (it wasn't giving very much output even with --verbose). Seems like the kind of thing that'd be useful on small databases, but if I were you I'd test it somehow before doing it for real. Maybe you could dump the schema somewhere else and fill in some random data, then see how fast mk-table-sync was able to sync it all to the slave. The --single-transaction option is not bad, but know that it is (obviously) ineffective with MyISAM tables... they just ignore it and dump normally. Don't know what happens if you dump some of each in the same dump (which you would need to do for replication). When I find myself in this type of situation, I generally try and give the stakeholders a few very simple choices. In my (agreeably, somewhat limited) experience, if you lay out the pros/cons, they feel like they can make an informed decision on the best way to proceed and won't haggle nearly as much over the nigh-impossible. Basically, I lay out the consequences for each path and let them choose which to walk down. In your case, I think I would go for 1) standard mysqldump (downtime, reliable, reusable elsewhere, lowest incident of human error) 2) LVM snapshotting (little or no downtime, "we've never done this before", not usable everywhere else, presumably harder and thus more operator-error-prone) 3) Do nothing (no downtime at all, but also still no backups... but maybe you have RAID-5 and good ACLs) Who knows, they may decide that the downtime of a normal dump is worth the tradeoff once the alternatives are actually spelled out. Or they might decide the uptime is still king and it's not worth the hassle at all. Of course, go ahead and mention what you'd *like* to do, as you're supposedly the expert on the situation Jake On Tue, Jan 6, 2009 at 1:09 PM, Claudio Nanni wrote: > First, Thank You all guys, I really appreciate your great answers. > Second in my experience this is one of the most challenging and frequent > things with mysql on production servers, > once you have the slave practically you have online backups > > I will try to answer one by one. > > Jake Maul wrote: >> >> If you're making backups of the DB, it might be possible to use the >> backup data as a replication snapshot for kickstarting the slave. You >> > > If I only had a kickstart backup! : >> >> You are making backups, right? :) >> > > ehmthe problems is exactly that. On production server you cannot stop or > lock the server so I need > the replication slave mainly for backups (actually MySQL replication is > simply great for this) >> >> Here's another possibility, although not one I would really recommend: >> > > I will give it a try, I am really interested in seeing what happens locking > one table per time. > > NOTE: > I have tried Innodb Hotbackup Tool today but it was locking the production > server! > and the strange thing is that it was locking while doing a 'cp' of a .MYI > file, pretty weird, > I would definitely not copy indexes but rather rebuild them offline, easily > on the slave. > from the InnoDB Hot Backup site: > > * Online backup of InnoDB tables — the backup takes place entirely > online, without preventing queries or updates. > * Online backup of MyISAM tables — during the backup of InnoDB > tables, read and write access is permitted to MyISAM tables. While > the MyISAM tables are being copied, updates (but not reads) to the > MyISAM tables are precluded. > > >> Jake >> > > Thank you man > > === > > Baron Schwart > > We get asked to do this a lot :) A: (I know!!!) > > There's a bunch of different cases. > What storage engines are you using? A: MyISAM and InnoDB > > Do you have LVM with free space > on the volume group, or another way to take snapshots such as a SAN? > A: Let's dont take it as an option since I could have it on this one but not > on other servers(I have about 60 servers in 10 replication clusters) > > What I am looking for is a stan
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 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: Data Inconsistent
What error is shown by 'show slave status\G' on server C after you issue that query? There's all sorts of things that could break replication... On Tue, Jan 20, 2009 at 7:21 AM, Krishna Chandra Prajapati wrote: > Hi Baron, > > In production we have three servers. > > A> B -C > > A is replicating to B. B is replicating to C > > A mysql-5.0.32 (Write) > B mysql-5.0.32 (Read) > C mysql-5.1.30 (Report Server) Complex and big queries scanning all > data. > > *ISSUE*: If any query like 'update set col1='val', col2=null where > userid=12345;' gets executed by webserver on A. (col2 is not null column) > Then query gets executed and data gets changed on A and B. But it gives > replication error on C. If i skip that error then data will be inconsistent. > Server C will have different data than A and C. > > How to solve this issue. > > Krishna Chandra Prajapati > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How much memory can mysql 5.1 take advantage of?
Didn't want this to go unanswered, although I don't have any great info for you. As long as you're running a 64-bit OS and a 64-bit version of MySQL, there's no technical reason it would be limited to less than the addressable space (that I know of). The main gain would be the ability to set larger buffers and handle more connections simultaneously. Of course, this is assuming your queries and schema are good and you don't suffer from excessive locking problems. That is to say... yes, it'll work, and yes, as far as I know MySQL will be able to allocate as much RAM as you can stuff in the box. Whether it can use it *effectively* is something I don't have any experience with beyond ~8GB. I suspect it would work just fine, though. Jake On Tue, Jan 20, 2009 at 12:08 PM, wrote: > While specing out a new server, I was wondering if there is any limit to how > much memory can be allocated to mysql 5.1. If a server has 16GB of ram, can > mysql take advantage of that much ram (minus a reserved amount for the OS > obviously)? Is there any limit such as those imposed by 32-bit processors? > > Thanks! > > > http://www.retailretreat.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: Bin Files.
Deleting the files from the command line is not considered the 'correct' way: http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html There should be a 'log-bin' line in your my.cnf just comment it if you don't want binary logs. Yes, they're mostly just used for master/slave replication, and point-in-time recovery. Instead of disabling it completely, I generally prefer to set expire_logs_days to some value, so MySQL automatically removes them after so many days. As for tuning values, that's not something we can just tell you... it depends entirely on your workload. If there were certain values everyone should set, they'd be the defaults :). Here's some good places to start though: http://www.day32.com/MySQL/ http://hackmysql.com/mysqlreport http://www.mysqlperformanceblog.com/ The tuning script on the first one will guide you through the most common tweaks you might need. mysqlreport is more in-depth, but less hand-holding... good for after the tuning primer one. The blog is just lots of general info... Good luck, Jake On Sat, Oct 25, 2008 at 5:50 AM, Grant Peel <[EMAIL PROTECTED]> wrote: > Hi all, > > I had a server pretty much locked up this morning due to the mysql bin logs > filling up the /var filesystem. > > I had been investigating the my.cnf settings file a white back, got > sidetracked, and never finished it. > > The bin logs are named: > > /var/db/mysql/myserver-bin.01 > /var/db/mysql/myserver-bin.02 > /var/db/mysql/myserver-bin.03 > /var/db/mysql/myserver-bin.04 > > I assume a new one is created each time the mysql server is started. I had to > shut down all services on the server, delete all these files and restart > everything. > > Anywho, the two questions are: > > 1. Are these files used when 'fixing' tables, or, are they only used for the > master/salve replication? > > 2. Is it safe to, and how do I, turn off the bin logs altogether (there are > no slave servers), > > 3. What my.cnf settings would you all reccomend for: > > Mysql4, running on, FreeBSD 6, 1 GB Memory, var fs is 5 GB, 250 virtual > domains on the server, of which 50 may be using mysql/php (for bulliten > boards etc). > (I am reading through the /usr/local/share/mysql *.cnf files ...) > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: "lost connection to mysql server during query" errors
I've never had a lot of luck tracking down this sort of problem. One thing I've found to be a good first step is to add each server involved to the other server's /etc/hosts file (and restart MySQL so it notices). Don't have much more to offer other than the usual suspects: recent versions, persistent vs. non-persistent connections, etc. A long shot would be to make sure your always talking to the same database server- if you're doing, say, DNS round-robin or load balancing or something, maybe you're getting shunted to a different db server and it's killing the connection... don't know what your setup is. Another long shot in a multi-db-server config would be to make sure they all have different server ID's. Good luck... hopefully someone else has better advice :) Jake On Wed, Oct 29, 2008 at 2:47 AM, Waynn Lue <[EMAIL PROTECTED]> wrote: > We've started seeing mysql errors in the logs, and when i look at the output > of mysql_error() (in php), i get "lost connection to mysql server during > query". Here's an example stack trace: > > 'Can't connect to database [Lost connection to MySQL server during > query]' > > Similarly, we're seeing stack traces here as well: > > 'Can't connect to database []' > > I usually only see this mesasge when I don't use a connection for awhile and > it timeouts, but in this case, the connection is only opened for the > duration of a script, which can't be running for more than a second. The > mysql error logs don't show anything, and wait_timeout is set to 28800. > > At first, I thought it was because I was calling mysql_select_db too much, > so I ended up using two mysql connections per page load, but that didn't > seem to change anything. How can we prevent this error from happening, what > else can I do to diagnose this further? Google brings up some more > discussions about it, but nothing seems related to this, like packetsize. > This is happening when we select two ids from a database. And SHOW > PROCESSLIST shows that the number of connections aren't even coming close to > max connections. > > Thanks for any advice, > Waynn > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don' t know difference between myisam_sort_buffer_size and myisam_max_sort_file_size
>From dev.mysql.com: myisam_max_sort_file_size : The maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes. The default value is 2GB. If MyISAM index files exceed this size and disk space is available, increasing the value may help performance. myisam_sort_buffer_size: The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE. The maximum allowable setting for myisam_sort_buffer_size is 4GB. (Default is 8MB) Simply: one's a buffer, one's a limit on the temp file size used for sorting. Which you might need to increase depends entirely on which limitation you're running into... if the current index files for the relevant tables on disk exceed 2GB, you might want to increase myisam_max_sort_file_size. Otherwise, myisam_sort_buffer_size might be better. Jake On Fri, Oct 31, 2008 at 12:20 AM, Moon's Father <[EMAIL PROTECTED]> wrote: > Because alter table and repair table are both affected by > myisam_sort_buffer_size or myisam_max_sort_file_size, I'm in confusion then. > > Anybody can tell me which to be adjusted when I want to improve the > performance of my index operation. > Thanks. > > -- > I'm a MySQL DBA in china. > More about me just visit here: > http://yueliangdao0608.cublog.cn > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a MySQL desktop database
I have only 3 things worth mentioning: You might want to lower max_connections... wouldn't want someone to actually start *using* 100 connections on a desktop box that only has 256MB RAM to begin with... it'll be in swap instantly (if it isn't already, before you've even started MySQL). Why InnoDB? If you're looking at low-concurrency (5 user max, you said) and low memory usage, you might want to leave it at the default of MyISAM, and then use 'skip-innodb' for the memory savings... unless you've got a good reason to change it. It's not a lot, but with only that small amount to work with every little bit will help. That'll save you $innodb_buffer_pool_size amount of memory at least (plus the actual InnoDB code itself). Depending on what you're trying to do, you might want to consider something like SQLite or HSQLDB instead. Both should be more lightweight than MySQL. As for other tweaks to make to MySQL itself, I don't have much to offer. Most tweaks depend almost entirely on the workload in question... like another respondent said, if there were tweaks that everyone used, they'd be the defaults by now :). You might try out these two scripts... they've been very helpful diagnosing MySQL performance issues for me on Linux servers. Good luck getting them going on Windows though... Cygwin might be necessary :) http://www.day32.com/MySQL/ http://wiki.mysqltuner.com/MySQLTuner Jake 2008/11/11 RP Khare <[EMAIL PROTECTED]>: > Thanks for the answer. > > Maximum five users will work. The machine on which I am testing is the > minimum configuration my client has. Attached is the "My.ini" file. > > . > Rohit. > >> Date: Tue, 11 Nov 2008 16:21:07 +0100 >> From: [EMAIL PROTECTED] >> Subject: Re: Tuning a MySQL desktop database >> To: [EMAIL PROTECTED] >> CC: mysql@lists.mysql.com >> >> Rohit, >> >> >> RP Khare wrote: >> > We are testing MySQL in production environment with real data. When the >> > application is ready and all tests have been conducted well, we will >> > finally >> > migrate to MySQL Enterprise. >> > >> > At present our application is running on a desktop machine with MySQL >> > 5.1 Community Edition installed on it. MySQL 5.1 is using default settings. >> > >> > Machine is: Pentium 4 with 256 MB RAM. >> > >> > What settings I need to change to tune MySQL? >> >> I really do not want to offend you, but the question is silly. >> >> If there were some way to definitely improve MySQL's performance without >> causing any drawbacks or problems, it would be used in the default >> settings. >> >> Default settings are meant to be usable for many installations, but >> cannot be optimum for all. Users can leave them as provided and start >> running their application, watch them, and check whether there are some >> bottlenecks. >> Once they see these, they can change the settings so as to avoid or at >> least reduce these bottlenecks (as much as the hardware permits). >> >> We readers here will never know what your bottlenecks are unless you >> tell us. It might be cache sizes, number of concurrent users, select >> strategies, ... >> >> The only general remark I dare make: 256 MB may be very little RAM for >> most database servers, will be sufficient only if you have few users, >> not much data, or can tolerate slow response times. >> >> >> Jörg >> >> -- >> Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] >> Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten >> Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer >> Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 >> > > > What's on the ramp today could be on the streets tomorrow. Keep up with > trends on MSN Lifestyle Try it! > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Setup Question
I see that you've already decided on 64-bit anyway, but here's a more explicit reason to do so: in 32-bit (PAE-mode), no single process can allocate more than 4GB of memory- that's all the address space there is. The *system* can see it all, but each process can only work with a single 4GB chunk of it. Therefore MySQL, being single-process (multi-threaded), can only use 4GB of memory on a 32-bit platform. So unless you'd be doing something else with that other 12-24GB of RAM, you'd effectively be flushing money down the drain to put in more than 4-5GB. Even then, there's lots of overhead involved with PAE mode, so if you actually have more than 4GB of memory 64-bit will almost always be faster (as far as the hardware is concerned). Jake On Wed, Nov 12, 2008 at 10:35 AM, Shain Miley <[EMAIL PROTECTED]> wrote: > Hello all, > I was wondering if anyone had any good insight into running the 32 bit and > 64 bit versions of MySQL? We are going to be using a replication setup > within my organization very shortly. We intend to a have at least one > master (writable) DB and several (let's say 3 for this excersise ) read-only > DB's. > One suggestion that I got was to use 64 bit version of MySQL so that we can > make better use of our servers memory as we are using servers that have 16 - > 32 GB of RAM. > > Does anyone have any info on whether or not using the 64 Bit version is a > good idea given the setup described above? > > Would I need to run the 64 bit version on all the servers or just the > master, etc? Any help would be great. > > Thanks in advance, > > Shain > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Setup Question
I don't have any links, but in general we don't do very much as far as tuning is concerned. Here's a few things I can think of off the top of my head that we sometimes do if we're worried about performance: blockdev --setra 2048 /dev/sda (substitute an appropriate readahead amount and device name - 'man blockdev') mount the MySQL data partition with 'noatime' consider playing with different schedulers ... the stock one works fairly well, but some people prefer 'deadline' Hmm... can't think of anything else right now. We spent more effort tuning MySQL itself than the underlying system. Linux has lots of knobs if you go looking, but (at least for us) you generally don't need to mess with them. We run CentOS mostly, for the record... Jake On Wed, Nov 12, 2008 at 12:20 PM, Shain Miley <[EMAIL PROTECTED]> wrote: > Thanks a lot for all the infolooks like we will put the 64 bit plan into > place. > > Does anyone know of any good links to information on tuning Linux before a > MySQL install? I see that there are lot of MySQL tuning guides...but I > assume there are things I can do to Linux that will help with performance as > well... > > Thanks, > > Shain > > > Daniel P. Brown wrote: >> >> On Wed, Nov 12, 2008 at 12:35 PM, Shain Miley <[EMAIL PROTECTED]> wrote: >> >>> >>> Hello all, >>> I was wondering if anyone had any good insight into running the 32 bit >>> and >>> 64 bit versions of MySQL? We are going to be using a replication setup >>> within my organization very shortly. We intend to a have at least one >>> master (writable) DB and several (let's say 3 for this excersise ) >>> read-only >>> DB's. >>> >> >> [snip!] >> >>> >>> Would I need to run the 64 bit version on all the servers or just the >>> master, etc? Any help would be great. >>> >> >>I would highly recommend running the 64-bit version on all systems >> if it's feasible from an infrastructure standpoint. One of my >> customers has a cluster that I manage where the RAM ranges from >> 16-24GB per machine, and I have 64-bit setups on each. They use >> replication as well, for the record. >> >>There may be some issues with read/write/seek times on a 32-bit >> machine as opposed to a 64-bit. The 32-bit may seem to lag, which can >> cause issues with replication under heavy loads. And, of course, >> filesizes and memory barriers do exist (and are being rather >> easily-reached now). However, if you're strictly asking about how it >> interfaces from one MySQL server to the next, it's no problem. MySQL >> couldn't care less if it's compiled for i586 or x86_64; that's only in >> how it relates to the OS on which it's installed, not how it interacts >> with sibling systems. >> >> > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
