RE: How big is too big?
Our database server has 4Gigs of ram on it, and we have a hard time of figuring out the true InnoDB settings on how to use that ram up. I ended up just increasing the ram used until it just wouldn't run anymore, then backed up and used that. We plan on adding another 4Gigs of ram, total of 8GB. Tweaking the InnoDB stuff doesn't seem as easy as the MyISAM side. So as it stands, we have 4GB, and one table that easily exceeds that, almost 8GB in size. Even after we bump the server up to 8GB, that means this table will barely fit. Does that mean we need to start cleaning out that table, or adding new ram? -Original Message- From: Sasha Pachev [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 6:52 PM To: Misao Cc: mysql@lists.mysql.com Subject: Re: How big is too big? > > Does anyone know the point that the MySQL Administrator can't report on > table sizes? Misao: I do not use MySQL Adminstrator, so I do not know at what point it is unable to correctly report the size of a table. But if there exists a table that it cannot correctly report the size of, it is a bug in the MySQL Administrator, and should be reported at http://bugs.mysql.com You can use SHOW TABLE STATUS LIKE 'table_name' to get the info about any given table. > > Also, does anyone know any of the magic numbers where things get difficult > for databases and tables and even the main server when it gets too big? There is no one magic number, but I define a couple of threshholds that could be of some guidance: * when the table does not fit into RAM anymore * when the most frequently accessed part of the table does not fit into RAM anymore However, the actual cut-off values for when the trouble starts is largely application dependent. -- Sasha Pachev AskSasha Linux Consulting http://www.asksasha.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How big is too big?
Our production databases here are really growing and getting to be rather big. The question on our minds is; when is a database or table just too big? Our InnoDB datafile was 116GB last I checked, and I know we've got a few 20GB+ databases on there, but my real concern is over the table sizes. I have 2 or 3 tables that the MySQL Administrator can't even get a size on. It reports it as 0Bytes, but the little picture bar shows that these tables take up almost 1/3 of the database size. I think these tables could be as big as 8GB, but we have quite a few above 1GB. Does anyone know the point that the MySQL Administrator can't report on table sizes? Also, does anyone know any of the magic numbers where things get difficult for databases and tables and even the main server when it gets too big? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Server optimization issue
MySQL is the only process that runs on these boxes. We dedicate the servers to MySQL since the DBs are so large. One of the possible problems is that these servers used to be MyISAM DBs, but we upgraded to InnoDB once we moved from 3.23.33 to 4.0.16. It is hard to tell the MyISAM variables from the InnoDB ones, so that I can optimize for InnoDB. I know that those prefixed with InnoDB are for such, but not which others may affect it as well. We only use MyISAM for the mysql system DB. The build is 4.0.16 from a RedHat RPM binary. These settings used to work great on the older MySQL, older hardware, same 4GB RAM, and much higher user access.(It was for the old MP3.com DBs) Here is the whole mysqld section in the my.cnf: [mysqld] user= mysql port= 3306 socket = /home/mysql/mysql.sock tmpdir = /usr2/tmp skip-locking server-id = 13 set-variable= back_log=10 set-variable= max_connections=800 set-variable= key_buffer=256M set-variable= max_allowed_packet=8M max_allowed_packet =8M set-variable= thread_stack=128K set-variable= record_buffer=4M set-variable= sort_buffer=64M #set-variable= wait_timeout=120 skip-slave-start # Start logging log-slow-queries = slow.log #log = query_log log-slave-updates log-bin # This MySQL options file was generated by innobackup. innodb_data_home_dir=/home/mysql innodb_data_file_path=ibdata1:2M:autoextend innodb_log_group_home_dir=/home/mysql/ innodb_log_files_in_group=2 innodb_log_file_size=512M innodb_buffer_pool_size = 1512M innodb_additional_mem_pool_size = 20M And a note: /home is actually on a large filesystem separate from /, so there is not OS/DB access issues. -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Friday, June 11, 2004 7:45 AM To: [EMAIL PROTECTED] Subject: Re: Server optimization issue "Misao" <[EMAIL PROTECTED]> wrote: Take a deep look at http://dev.mysql.com/doc/mysql/en/Server_system_variables.html But ensure that this is MySQL who takes so much memory. Watch other variables like key_buffer. MySQL probably should not take that much memory if these are the only variables specified: > Here are my InnoDB settings in my.cnf: > innodb_log_files_in_group=2 > innodb_log_file_size=512M > innodb_buffer_pool_size = 1512M > innodb_additional_mem_pool_size = 20M What are the OS and MySQL build? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server optimization issue
I have 4 MySQL 4.0.16 servers, all with Dual 3ghz Xeons and 4GB of RAM. They use InnoDB for all tables, and the ibdata file is 70GB. The DBs seem to be a little slow, and the darn thing is always using a huge chunk of swap. I've tried increasing and decreasing what it's allowed to use, but this seems to be the best working settings. Here are my InnoDB settings in my.cnf: innodb_log_files_in_group=2 innodb_log_file_size=512M innodb_buffer_pool_size = 1512M innodb_additional_mem_pool_size = 20M Can any optimizations be done to this, or do I just need to get more RAM for these servers? I believe I can go up to 6GB of RAM, but it requires an OS upgrade to handle anything over 4GB. I know that the biggest DB in that chunk is about 20GB itself, with around 5-10GB in it's biggest table. Memory status: Mem: 3943852K av, 3938184K used,5668K free, 0K shrd, 209456K buff Swap: 2096440K av, 1895456K used, 200984K free 1921172K cached Any help would be appreciated, this seemed easier to tweak when it was a MyISAM server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB, Replication, and Data warehouse: Oil, Water, and little floating plastic men
What follows is a short story, all true and quite stressful. No database servers were harmed in the making of this server, but a couple were threatened with loose rack mount rails. We are trying to move over to InnoDB, but we have a few problems that we just can't figure out: First, for some reason, MySQL claims it can not claim more than .5Gigs of RAM from a system that has 4Gigs of RAM total and not being used for anything but MySQL. Secondly, MySQL replication leaves a problem for our data warehouse and replication. When you use MySQL binary replication, it has been my experience that it is all or nothing. You can't choose just one database to replicate. You can start or stop just one database on the slave from being replicated. If you stop one, they all stop. This is a problem, because what we need to do is stop replication at midnight, and then do a dump of the database. Just one of them. When we stop it now, they all stop so now we have databases that are idle and not getting up to date replication while this one database gets mysqldumped for hours. InnoDB hot backup is a swell thing, but it doesn't dump the database in a useable format for anything except bringing an entire server online. This doesn't help us when we just want a simple mysqldump file of one database to do data warehouse work. Before, what we did with MyISAM was a crude but workable in house replication system that used the text file update logs to replicate to a slave. This allowed us to replicate by database, and in turn only affect that one database for replication and dumping. It also allowed us to attach a data warehouse program to the replication so that it could grab the information it needed. With MySQL binary replication, we can not do either of these activities. So, in a nutshell, this is our problems with InnoDB. I really want to get these problems fixed so I can keep InnoDB around, and use that nice hot backup program. It would be painful to have to stick with MyISAM. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql installation problems (RPM errors)
First off to maybe make this easy on you, you do not need the MySQL-bench rpm if you do not plan on using the benchmarking tools. It is an optional install. Otherwise, I do not think rpm cares about capitalization. I believe this problem is discussed in the MySQL manual, and might have the solution, but then I am sure others on this list can help out. Most important thing to remember, is that you don't need benchmarking to run the MySQL server. In the past, I always used a tarball of the perl DBI and it worked fine. Gale Stafford wrote: Hey Folks Hi there! My first post to the list ... I am installing mysql 4.0.1-2 on a Redhat 8.0 workalike. I've installed the MySQL server software, client software, but when I try to install the MySQL-bench...rpm, I get the failed dependencies error, MySQL-DBI-perl-bin is needed. I did a search and found Mysql-DBI-perl-bin-1.1825-1.i386.rpm, and I installed it. But when I rpm -install the MySQL-bench...rpm package I still get a failed dependencies error. Is this a problem caused by "Mysql-DBI-perl...rpm) not having capitals in all the right places? I dont understand enough about how rpm works, to troubleshoot this! Can anyone help point me to the right MySQL-DBI-perl...rpm file? thanks, Gale -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB can't use system's RAM as specified
I'd have to say that things look fine in that regard. core file size (blocks) 0 data seg size (kbytes) unlimited file size (blocks) unlimited max locked memory (kbytes) unlimited max memory size (kbytes)unlimited open files 1024 pipe size (512 bytes) 8 stack size (kbytes) 8192 cpu time (seconds) unlimited max user processes 15231 virtual memory (kbytes) unlimited Heikki Tuuri wrote: Hi! We have only 2 GB of RAM in our SuSE Linux server, and I can allocate 1400 MB to the buffer pool. Have you checked what ulimit -a says for the user running mysqld? Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: ""Misaochankun"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, August 26, 2003 4:01 AM Subject: InnoDB can't use system's RAM as specified I am coming across an annoying problem with a new InnoDB installation. On a 4Gig RAM box, Mysql doesn't seem to want to grab the RAM I am telling it to take. I would like for it to try and use around 3 Gigs for its innodb_buffer_pool_size, but it refuses to startup with anything greater than 512M. I bugged RedHat about it, and they had me change the shared memory allowance to 3Gigs, but nothing changed. I'd really like to get the pool size up so that the server doesn't have to go to disk so often. I will admit to being an InnoDB newbie, I am reading everything I can find though and have used the example my.cnf files to tweak it pretty decently. This memory issue is all that is really bugging me at the moment. *snip* -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
