Re: Perl arrays into MySQL
On 1 Jun 2004, at 2:55 am, Nik Belajcic wrote: This may be a silly question, but I am wondering if there is something opposite to: @row = $sth-fetchrow_array In other words, instead of fetching rows from MySQL and loading them into an array that can be accessed from Perl, I want to do the opposite - take a Perl (associative) array and load it into MySQL table. I know I could dump it into a text file and read it from there, but assuming I do not want to go through this intermediary step, how could I do it? Although it's not elegant, dumping to a text file and reading it in from there is the fastest way to load the data if there's a lot of it. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump problem
On 24 May 2004, at 6:05 pm, Gilbert Wu wrote: Hi All, The dump file is a 4Gytes text file. Could find an editor able to look at the content of the file. Hence, I had to write a little Perl program (something I haven't done for 5 years). I discovered the line that caused a syntax error whenever I tried to restore from the dump file. It looks something like: INSERT INTO TABLE2 VALUES (5,579,265,16,20020312,-438,191.789,191.789,5.5767e-006,5.5767e -006,0,0,0,-12086.6),(5,579,265,16,20020313, -438,157.242,157.242,4.5723e-006,4.5723e-006,0,0,0, -11892.7),(5,579,265,16,20020314,-438,-177.829,-177.829,-5.1788e-006, -5.1788e-006,0,0,0,-11829.3),(5,579,265,16,20020315,-438,-131.495, -131.495,-3.8387e-006,-3.8387e-006,0,0,0, -12012.6),(5,579,265,16,20020318,1,-133.587,-173.91,-4.7218e-006, -6.147e-006,47.0828,1.6642e-006,12290.1,-12135.1),(5UNLOCK TABLES; It seems like mysqldump gave up during the file generation and inserted UNLOCK TABLES; in the middle of the insertion block. Hence, the syntax error during restoration. Is this a mysqldump bug? Regards, Gilbert -Original Message- From: Gilbert Wu Sent: 24 May 2004 17:10 To: Brian Reichert Cc: [EMAIL PROTECTED] Subject: RE: mysqldump problem Sorry, I am running MySQL 4.0.17-nt on Windows 2000 Server. Consider the OS and filesystem as well (wheatever uyou _are_ using; you didn't tell us). Quotas? Limits by FS? getrlimit()? Would this by any chance be dumping to a disk formatted with the FAT32 filesystem? FAT32 files are limited to 2^32-1 bytes - i.e. 1 less than 4GB. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Log partition filled up, binary logging stopped. How to recover?
MySQL 4.0.18, Tru64 5.1B The log partition filled up on one of our instances. RESET MASTER fails with: ERROR 1186: Binlog closed, cannot RESET MASTER I deleted some older logs by hand, but RESET MASTER still fails. FLUSH LOGS appears to succeed, but no binary logs are being written, and RESET MASTER still fails. I have one comment and one question about this: Question: Is there any way out of this rather than restarting the server? Comment: Data-modifying queries are still continuing successfully. This worries me.Without binary logs, and with data updates continuing, we don't have a way to restore the instance should the hardware fail. And if there were a replication slave running against this instance, it presumably would by now be far out of date, with no way of updating it without a complete resynchronisation from scratch. Surely if the binary update log can't be written, inserts and updates should either block, or fail completely? Or am I missing something? Many thanks, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL limits.
On 18 May 2004, at 2:28 pm, RV Tec wrote: Is MySQL able to handle such load with no problems/turbulences at all? If so, what would be the best hardware/OS configuration? What is the largest DB known to MySQL community? We regularly run databases with around 200 GB of data per instance, and up to 1000 simultaneous clients. Admittedly on slightly beefier machines than yours - usually 4-way AlphaServers running Tru64. You didn't say what version of MySQL you were using? Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connections repeatedly dropped
Hmm, well, I seem to have resolved the problem, and it looks like it was not MySQL's fault. I updated the OS to the current testing release of Debian, which included a C library update. Following a reboot, the code worked perfectly. Looks like this was an Itanium2 C library bug, most likely. No wonder you were all so quiet! Having said that, MySQL runs *vastly* better on these Itanium2 machines than on the Alphas that we currently use in production, even though there's very little difference in clock rate and memory between the two systems. I can't wait to try the new memory cluster code on a group of these Itanium2 boxes. :-) Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest filesystem for MySQL
On 14 May 2004, at 4:37 am, Roy Butler wrote: Jacob, I'd go with Reiser on SuSE. What about Reiser on Debian? I'd choose SuSE since Reiser is their default filesystem and they have been an early implementor of Reiser-related patches. If you use Linux kernel 2.4.24 (or later) and the latest 3.6 series of ReiserFS+tools, the Linux distribution you choose shouldn't technically matter. I'm under the impression that Debian isn't bleeding-edge in many respects, perhaps due to its support of so many architectures, so you might have to build all of this yourself (or find someone who has) if you go that route. Debian is reasonably current if you follow the testing tree, rather than its stable releases. Debian stable is on ReiserFS 3.6.25 for 2.4 kernels, so it's not too out of date. The testing tree has support for 2.6 kernels too. Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB filesystem
On 14 May 2004, at 1:14 am, Dathan Vance Pattishall wrote: -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Thursday, May 13, 2004 4:03 PM To: Dathan Vance Pattishall Cc: 'Tim Cutts'; 'MySQL List' Subject: Re: InnoDB filesystem On Thu, May 13, 2004 at 04:51:27PM -0700, Dathan Vance Pattishall wrote: I think that the problem is that it's *not* a 64 bit OS. It's just an Intel 32bit box with 4GB of memory. And sine MySQL doesn't do PAE, it'll never see that extra memory. Intel box with 4GB? It is possible with a patch like hugemem in Linux but 4GB should only be used 2^32 = 4GB. - The hugemem patch for instance allows you to use all 16 GB but at a performance penalty. I have 168 32-bit machines (IBM HS20 blades) which can take 8GB RAM each, although we don't have them configured with that much. So there are plenty of these machines available. I tend to agree though, that for MySQL, if you want lots of memory its better to go for a 64-bit platform. We've been using Alphas and Tru64 for MySQL databases for years. Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB filesystem
On 13 May 2004, at 3:34 pm, Dan Nelson wrote: Pros: performance and bypassing the filesystem cache. I believe most OSes support direct file access which either bypasses or minimizes cache effects, and InnoDB will enable it if possible. Solaris direct file I/O performance on UFS is within a couple percent of raw partitions, for example. This is all very well, but on some systems, surely the OS filesystem cache is a bonus, not a hindrance. Take for example a four-way X86 system with 16 GB RAM. I've seen people talk about such machines on this list. MySQL can't use all that memory itself, so it makes sense to allow the OS to cache as much disk space as possible in the memory that MySQL can't use directly? Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest filesystem for MySQL
On 13 May 2004, at 4:02 pm, Jacob Friis Larsen wrote: I'd go with Reiser on SuSE. What about Reiser on Debian? It shouldn't matter too much. This functionality is in the kernel, so if the kernel version on SuSE and Debian is the same, the filesystem code will be the same, with the possible caveat that SuSE may have applied some other patches. The same isn't so true of Red Hat, who patch their kernels up to the eyeballs with whatever they feel like, until it bears scant resemblance to the version it actually says it is. Debian kernels are pretty much vanilla kernel.org kernels. Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connections repeatedly dropped
Dear MySQL experts! I'm at something of a loss here. I'm testing MySQL on a new hardware platform. Previously, we had it running on Tru64 Alpha boxes. We're now moving it onto Itanium2 boxes running Debian Linux. Each machine has 4 CPUs and 16 GB RAM. Kernel version is 2.6.5. We couldn't get the MySQL binary distribution to run at all; it dumped core immediately with SEGV. We compiled it ourselves using the Intel compiler, and got the same result. I then compiled it with gcc, and we have a binary that does at least run without crashing instantly, and appears to work correctly. The instance is replicated from one Itanium2 machine to a second identical machine. The clients to these databases are computational jobs running on a cluster of approximately 1000 X86 Linux boxes. The jobs query the database for the data on which they are to work, and upload results to it once they are finished. They also update a status table in the database as they work so that a master control script can periodically poll the database and resubmit jobs which fail and so on. This setup works fine with MySQL 4.0.18 running on AlphaServer ES45 machines. But on the Itanium2 Linux machines, the vast majority of clients are seeing aborted connections: ia64c show status like 'Aborted_%'; +--+---+ | Variable_name| Value | +--+---+ | Aborted_clients | 2177 | | Aborted_connects | 0 | +--+---+ 2 rows in set (0.00 sec) Looking at: http://dev.mysql.com/doc/mysql/en/Communication_errors.html for possible reasons, I see the usual suspects of timeout variables, but those are fine on this instance: ia64c show global variables like '%_timeout'; +--+-+ | Variable_name| Value | +--+-+ | connect_timeout | 5 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50 | | interactive_timeout | 2678200 | | net_read_timeout | 30 | | net_write_timeout| 60 | | slave_net_timeout| 3600| | wait_timeout | 2678200 | +--+-+ 8 rows in set (0.00 sec) These are the same settings we use on the Alphas, where they work fine. The other possibility is max_allowed_packet, but we've got that set quite large (certainly large enough for these queries): ia64c show global variables like '%_packet'; ++--+ | Variable_name | Value| ++--+ | max_allowed_packet | 16776192 | ++--+ 1 row in set (0.00 sec) So I don't think it's any of these settings. As to the Linux problems which are mentioned: 1) We don't think it's ethernet duplex - these are gigabit ethernet. 2) TCP/IP seems to be correctly configured in all other respects. 3) The switches are all fine, as far as we know The only base I can't cover is the statement: Some problem with the thread library that causes interrupts on reads. Are there known problems of this sort on certain Linux versions? Is there any code around to test whether this machine has this problem? Many thanks for any help you gurus can offer... Regards, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SLOW 22million rows, 5 hour query?
On 23 Apr 2004, at 9:48 pm, [EMAIL PROTECTED] wrote: Relevant `explain` details: Full table scan: 22,371,273 rows, Using temporary; Using filesort The filesort is a giveaway. Can you increase the sort buffer size so that the sort can happen in memory rather than having to use a file to sort? Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Cluster
On 14 Apr 2004, at 10:57 pm, Adam Erickson wrote: (This is probably not the best place for this post, but here goes...) The (soon to be released) MySQL cluster software docs use a sample cluster node configured with Dual Xeons and 16GB of ram. MySQL has never been able to use more than 2 gigs of system memory (on 32 bit platforms.) With MySQL Cluster, will MySQL finally start using the memory paging trick Oracle and others have been using for years? Otherwise, what is the point of having 16 gigs of ram for one MySQL server? Disk cache. Tables which MySQL doesn't have in its own buffers but which nevertheless are frequently accessed will already be in RAM, and therefore faster to access. Even so, you'd probably do better with a 64 bit processor with that amount of memory. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Performing Hardware/OS/MySQL?
On 29 Mar 2004, at 23:55, Donny Simonton wrote: SCSI, 15,000 RPM drives and a decent amount of memory 2-16 gigs. Dual procs definitely do help; we have tried it with dual procs with hyperthreading and without and with hyperthreading seems to be much faster. Besides that, you can run it on any OS; we use Fedora, with Linux 2.6.x. But that's our choice. Until recently, we ran it mainly on Tru64 Alpha boxes. We've recently been looking at new machines. Xeon machines running Linux are great if your databases are small enough for 2GB to be enough memory for the MySQL server. Many of ours are not (the human genome is 3 billion base pairs, for example, so the DNA table alone exceeds 2GB, and that pales into insignificance compared to the annotation tables) For those, we are starting to look at Itanium2 machines running Debian. Like Donny, we're using the 2.6 kernel. MySQL is about twice as fast on an Itanium2 running the 2.6 kernel as it is on the same machine running a 2.4 kernel. We've just received a quad-CPU-Opteron machine and will be testing that as soon as we can find an OS for it that actually works... But just say no to IDE drives! SATA RAID devices aren't that bad, you know, and they are a lot cheaper than equivalent amounts of SCSI storage. We've used NexSan ATABoy devices, which are relatively cheap, and get you a lot of storage in very little space (10GB in a 3U box). Having said that, our production MySQL servers disks are 15K RPM FibreChannel disks on HP StorageWorks HSV110 controllers, which is rather more at the upper end of the scale. ;-) Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Performing Hardware/OS/MySQL?
On 30 Mar 2004, at 09:05, Tim Cutts wrote: SATA RAID devices aren't that bad, you know, and they are a lot cheaper than equivalent amounts of SCSI storage. We've used NexSan ATABoy devices, which are relatively cheap, and get you a lot of storage in very little space (10GB in a 3U box). I did of course mean 10 TB. 10 GB in a 3U box might have been impressive ten years ago... :-) Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RESOLVED Re: Mystifying mysqld memory usage explosion
Tim: Can you bring your libc to the latest patch level? Not necessary. I resolved the problem: binlog_cache_size was set to 32MB I didn't realise that this would automatically be allocated to every thread, even if there are no InnoDB or BDB tables in the entire instance. This explains why --skip-innodb fixed the problem; without InnoDB, MySQL knew that there would be no transactions occurring, and so would not need the binlog cache for each thread. The documentation probably needs clarifying that this is another per-thread buffer, and it is always allocated to every connection thread if the server supports transactional table types. As a followup question; what happens to the binlog cache if a thread requires more? Does it automatically increase it as needed (up to an eventual limit of max_binlog_cache_size)? Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
On 25 Mar 2004, at 05:01, Sasha Pachev wrote: Innodb to my knowledge does not allocate very much locally per thread, and should not allocate anything at all if you are not doing any queries. That's what I thought. Based on the test results you have reported, I would put your libc as the primary suspect, I'm using the system libc which comes with Tru64 5.1B and the next one would be bad build/compiler bugs. I'm using the binary build as supplied by MySQL. I'm going to try compiling mysql myself, and see whether that makes any difference. I would suppose that --skip-innodb just changes some memory allocation patters on startup, which possibly avoid triggering the bug. Perhaps... Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
On 25 Mar 2004, at 06:31, Heikki Tuuri wrote: you can use the command SHOW INNODB STATUS; to check how much memory InnoDB has allocated in total. Please report what it says at the time of the memory explosion. Well, that was informative, but in a negative sort of way. SHOW INNODB STATUS produced identical output when run immediately after server startup and when 50 idle connections were in place, and the virtual memory consumption had increased by around 2GB. You report that even 100 IDLE connections cause the memory explosion. I agree with Sasha that this probably is not a MySQL/InnoDB bug. I have not heard of a similar memory problem from anyone else. No, indeed. I'm going to try building mysql myself, on the machine on which it's going to be running, and see whether that still has the issue... Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
On 25 Mar 2004, at 10:10, Tim Cutts wrote: No, indeed. I'm going to try building mysql myself, on the machine on which it's going to be running, and see whether that still has the issue... The version compiled natively on the machine does the same thing (although it uses a little less memory to start with since it's not statically linked). I suppose the next thing to try is the debug version. I've tried compiling the debug version myself without success, so I'll download the debug version from MySQL and try that. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database dump query
On 25 Mar 2004, at 15:23, joe collins wrote: Hi, anyone know what happens if, while I am doing a database dump, someone logs into the database and updates records, what records are trapped in the dump, or can the dump proceed under this circumstance...in other words must I knock all users off the database before the dump is done? mysqldump, if used with the --lock-tables option (which is implied by --opt) obtains read locks, so queries attempting to update the database will block until the dump has finished. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling with icc
It's my understanding that MySQL does not currently compile with the Intel compiler - it's too gcc-specific, and icc is not 100% gcc-compatible. I seem to remember seeing this in the on-line documentation somewhere, but I can't remember where. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling with icc
On 25 Mar 2004, at 01:14, Chris Nolan wrote: An ad in one of the Linux magazines I leaf through on occassion showed some pretty noticable improvements (in the order of 20% in some cases), but I'm betting that these were crafted test cases, as we all know that MySQL's two main storage engines are extremely smart when it comes to using discs. Some of our computational codes are twice the speed when they're compiled with icc compared to gcc. It can make that much difference. It's significant enough that we compile everything with icc when possible. However, there are some caveats. Some of the optimisation options available are extremely aggressive, and can break your code in all sorts of wonderful and subtle ways if you're not careful - pointer aliasing being a prime example, and we have found at least one bug in the optimiser (which Intel are working on, since we have a support contract with them). My rule of thumb is, if the code is largely compute-bound, compile it with icc if possible. Otherwise, stick with gcc. If you're IO-bound or memory-bound, choice of compiler doesn't really matter. I've compared perl scripts on perl built with icc 7.1 and gcc 3.2, and the performance difference was negligible. I wouldn't bust a gut over this, if I were you. I suspect MySQL is not CPU-bound, most of the time, and certainly not floating-point bound (which is where icc really shines) Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
On 22 Mar 2004, at 18:24, Tim Cutts wrote: Some users' code is causing MySQL's memory use to explode. By the time we reach about 200 simultaneous connections, the MySQL server is using 8GB of virtual memory, and then falls over (the machine is an AlphaServer ES45 with 8 GB of physical memory, and 16 GB of swap, although processes are constrained to a total of 8GB of virtual memory in the OS) The versions of MySQL I have found this behaviour on are 4.0.14 and 4.0.17 Well, a deafening silence from the list. :-) I've got some more information now. I wanted to exclude perl, DBI and whatnot from the list of suspects, so I wrote a client in C. All the client does is connect to the database and sleep for 10 minutes. If I run 100 or so of these simultaneously, the MySQL server still explodes in memory use and crashes. So it's nothing to do with either the SQL that's being run, or the use of perl/DBI clients. It's simply a matter of connection count. Now, here's the kicker: The problem goes away completely if I start the server with --skip-innodb So my question is: are there buffers which are allocated per connection to do with accessing InnoDB tables? My reading of the documentation suggests that all the innodb_ variables refer to global buffers, logs and so on. Or have I missed something? Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does the binary log enabling affect the MySQL performances?
On 23 Mar 2004, at 10:38, [EMAIL PROTECTED] wrote: Hello colleagues, I'd like to know IF by enabling the binary logs production I'll affect meaningfully the MySQL DB performances, and HOW MUCH the performances are impacted I just tested a load of a 6GB mysqldump (MyISAM tables), on an unloaded HP AlphaServer ES45 with 8 GB of RAM, and storage on an HSV 110 SAN device. MySQL version was 4.0.17 Without binary logging, elapsed time was: 2480 seconds With binary logging, elapsed time was: 2502 seconds So it's about 1% slower, if that. I'm logging to a different device from the databases. As an additional point, if I add a replication slave to the equation, the time becomes: 2518 seconds Which is scarcely any difference at all. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mystifying mysqld memory usage explosion
Some users' code is causing MySQL's memory use to explode. By the time we reach about 200 simultaneous connections, the MySQL server is using 8GB of virtual memory, and then falls over (the machine is an AlphaServer ES45 with 8 GB of physical memory, and 16 GB of swap, although processes are constrained to a total of 8GB of virtual memory in the OS) The versions of MySQL I have found this behaviour on are 4.0.14 and 4.0.17 I'm presuming that I have some configuration variable wrong somewhere, but I can't work out what it is. Here's our .cnf file for the instance: [mysqld_safe] err-log=/mysql/log_3365/err.log [mysqladmin] socket=/mysql/data_3365/mysql_3365.sock port=3365 [mysqld] binlog_cache_size=32M datadir=/mysql/data_3365/databases interactive_timeout=2678200 key_buffer=1024M #log=/mysql/log_3365/query.log log_bin=/mysql/log_3365/bin.log log_slow_queries=/mysql/log_3365/slow.log log_warnings max_allowed_packet=16M max_binlog_size=2000M max_connections=1024 net_write_timeout=60 pid-file=/mysql/data_3365/mysql_3365.pid port=3365 query_cache_size=32M read_buffer_size=256K socket=/mysql/data_3365/mysql_3365.sock sort_buffer_size=2M table_cache=512 thread_cache_size=16 wait_timeout=2678200 # Replication options server_id=1 I've tried reducing sort_buffer_size to a pathetic 32K, and it makes no difference, so it's not that. Similarly, I've tried reducing max_allowed_packet, and that makes no difference. The code in question is running a large number of compute jobs on a Linux cluster, and these jobs talk to the MySQL server both to inform a master control process what is going on, and secondly to store their results in it. If I constrain the number of simultaneously running jobs to 20, then MySQL only grows to about 3.5 GB, 2.5 GB of which were allocated as soon as it started, so it looks like each connection is allocating around 50 MB inside MySQL, but I don't know where this is coming from. Surely each thread within the OS doesn't take 50 MB before it's allocated anything else? Any ideas, including ways I can get MySQL to tell me more about what it's doing, would be most helpful. The query log, even with log-warnings on, does not tell us much. Many thanks in advance... Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: restoring a database from tape
On 16 Mar 2004, at 09:06, Victor Pendleton wrote: If the directory structure is the same and the snapshot is consistent then the answer is yes. If you do not want to purchase a commercial utility, one method is to schedule mysqldump, compress the file and then backup that file up. What commercial utilities exist for backups of MySQL instances? For complicated reasons I don't want to go into, most of the standard methods for backing up MySQL instances don't work well for us, or at least have significant drawbacks. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: just the list please!
On 16 Mar 2004, at 21:31, Alan Williamson wrote: Can people please just email the list and not the person *AND* the list!!! i get duplicate emails and its very annoying to what is a great list so far. kinda puts me off from answering peoples questions! This is something the list admins might be able to sort out. It's common for mailing lists to set a reply-to header, so that replies automatically go to the list, and not to the originating poster (all the mailing lists that I run do so) but this list does not. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: just the list please!
On 16 Mar 2004, at 22:30, Gabriel Guzman wrote: On Tuesday 16 March 2004 02:19 pm, Tim Cutts wrote: This is something the list admins might be able to sort out. It's common for mailing lists to set a reply-to header... snip here we go again Oops - is that a bad button to push? :-) If it's the policy of the list managers not to set the reply-to header, then so be it, and the OP will just have to put up with it! Some lists I subscribe to have the opposite extreme policy - no-one must reply to the list at all; they reply to the original poster, and then some time later the original poster posts a summary of the replies. Works quite well, and keeps the traffic down, but does take some discipline. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access denied from a single machine...
Server version: MySQL 4.0.18, running on Red Hat Linux 8.0 Symptoms: All our machines except for one can connect to the database. That one machine gets 'access denied'. All users are affected, even root. SHOW GRANTS for an example user: Grants for [EMAIL PROTECTED]: GRANT ALL PRIVILEGES ON *.* TO 'ensadmin'@'%' IDENTIFIED BY PASSWORD 'x' And yet any attempt to connect to that database from one particular machine results in failure. For example, here are the logs for two successive connections, one from machine bc-1-1-03 and one from bc-1-1-02: Time Id CommandArgument 040315 10:44:00 1 Connect [EMAIL PROTECTED] on 1 Query select USER() 040315 10:44:09 1 Quit 040315 10:44:21 2 Connect Access denied for user: '[EMAIL PROTECTED]' (Us ing password: YES) I've tried re-starting the database, to no effect (fortunately this is just a test instance). Am I missing something ridiculously simple, or is this a bug? Thanks in advance... Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Log production option enabling
On 15 Mar 2004, at 12:34, Victoria Reznichenko wrote: [EMAIL PROTECTED] wrote: Hello colleagues: I would like to know if it's possible to start the log file production at run time, i.e. without stopping and restarting the database ... No, you should restart MySQL server. Which is really irritating if you're debugging a problem with a busy production server. You don't want the general query log on all the time, but you don't want to have to have database downtime (even if it's only momentary) to switch the logging on and then off again. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied from a single machine...
On 15 Mar 2004, at 15:35, Victoria Reznichenko wrote: Probably you specified wrong password in the option file on 'bc-1-1-02' box. Check the output of mysql --print-defaults. Nice idea, but no - I typed the password in manually both times, and I have repeated the experiment several times (and so has another user). The software in use on both client machines is identical, and the user accounts are on shared home directories, and so would have been reading the same ~/.my.cnf Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
