Re: my.cnf authencication
- Original Message - > From: "Harrie Robins" <har...@eyequestion.nl> > Subject: my.cnf authencication > > mysqldump --defaults-file dbase > c:\sql\dbase.sql 2>> c:\log.tct Might just be a typo in your mail, but you'll need to actually pass the defaults-file, too: --defaults-file=c:\sql\dump.cnf . I think there may be another typo somewhere, too, as it seems to think that lts-file is the user you're passing. I'm wondering if you haven't accidentally put only a single - in front of defaults-file. > > > My log shows: > > mysqldump: Got error: 1045: Access denied for user > 'lts-file=c:\sql\dump.cnf'@'localhost' (using password: NO) when trying to > connect > > It looks like credentials are not filled in!? > > Regards, > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: my.cnf authencication
Thanks for the reply - that was indeed a typo. I resolved this buy making these changes: mysqldump --defaults-extra-file="c:\sql\dump.cnf" dname > c:\loc with my cnf containing: [client] user = user password = pass Looks like I misplaced "" and or [client] / [mysqldump] in the cnf Thanks -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: vrijdag 29 januari 2016 15:06 To: Harrie Robins <har...@eyequestion.nl> Cc: MySql <mysql@lists.mysql.com> Subject: Re: my.cnf authencication - Original Message - > From: "Harrie Robins" <har...@eyequestion.nl> > Subject: my.cnf authencication > > mysqldump --defaults-file dbase > c:\sql\dbase.sql 2>> c:\log.tct Might just be a typo in your mail, but you'll need to actually pass the defaults-file, too: --defaults-file=c:\sql\dump.cnf . I think there may be another typo somewhere, too, as it seems to think that lts-file is the user you're passing. I'm wondering if you haven't accidentally put only a single - in front of defaults-file. > > > My log shows: > > mysqldump: Got error: 1045: Access denied for user > 'lts-file=c:\sql\dump.cnf'@'localhost' (using password: NO) when > trying to connect > > It looks like credentials are not filled in!? > > Regards, > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
my.cnf authencication
I'm running a mysqldump in windows and I'm mailing 2>> output, right now I get this annoying 'insecure' error that pollutes my log. So I figured I use --defaults-file and set: In c:\sql\dump.cnf [mysqldump] user = myuser password = pass my line looks like this mysqldump --defaults-file dbase > c:\sql\dbase.sql 2>> c:\log.tct My log shows: mysqldump: Got error: 1045: Access denied for user 'lts-file=c:\sql\dump.cnf'@'localhost' (using password: NO) when trying to connect It looks like credentials are not filled in!? Regards, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
DBA: please review my.cnf [for Java Hibernate application] and give suggestions
Hi We will use a Java application which uses Hibernate for DB calls. The vendor didn't made recommendations howto configure MySQL. The application is not yet in production. MySQL is new to me, I previously used Oracle DB. The vendor provided a guide howto configure Oracle. This is our my.conf $ cat /etc/my.cnf [mysqld] datadir=/opt/pprd/mysql socket=/var/lib/mysql/mysql.sock user=mysql max_allowed_packet=10M query_cache_size = 8388608 table_open_cache=256 tmp_table_size=67108864 log_bin = /opt/pprd/log/mysql-bin.log log_bin_index = /opt/pprd/log/mysql-bin.index expire_logs_days= 5 max_binlog_size = 100M binlog_format = row [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid $ rpm -qa mysql mysql-5.1.66-1.el6_3.x86_64 $ free -m total used free sharedbuffers cached Mem: 32241 31610630 0240 27209 -/+ buffers/cache: 4161 28080 Swap: 2047 74 1973 $ cat /etc/redhat-release Red Hat Enterprise Linux Server release 6.1 (Santiago) did we miss an inportant option? What config options do you use? the DDL is provided by vendor and we are not allowed to change it (for example indexes). I personly like if the MySQL behaves like a out-of-tbe-box Oracle 11g db.
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
Hi Lukas, What is your default engine? In MySQL there are a lot of parameters that configure the engine behaviour. Depends on the engine, I suggest you to add some parameters or others. Also it's important to know the size of your data. Your configuration is minimal and by default is not optimal. Regards, Antonio.
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
Is it a standalone DB server or Application is also hosted on top of it. You can give 50-70% of RAM to memory parameters like Innodb_buffer_pool_size ( Innodb ) and key_cache ( Myisam ) for mysql tables. Below link : http://mysql.rjweb.org/doc.php/memory will give you a brief idea. Thanks
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
Hi Antonio all tables use InnoDB. The size is 27 GB (not yet in prod). I guess in prod it will be fast 80GB. thanks On Wed, Feb 12, 2014 at 10:28 AM, Antonio Fernández Pérez antoniofernan...@fabergames.com wrote: Hi Lukas, What is your default engine? In MySQL there are a lot of parameters that configure the engine behaviour. Depends on the engine, I suggest you to add some parameters or others. Also it's important to know the size of your data. Your configuration is minimal and by default is not optimal. Regards, Antonio.
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
Hi it's also a Tomcat application server. Not dedicated MySQL instance. On Wed, Feb 12, 2014 at 11:28 AM, Adarsh Sharma eddy.ada...@gmail.comwrote: Is it a standalone DB server or Application is also hosted on top of it. You can give 50-70% of RAM to memory parameters like Innodb_buffer_pool_size ( Innodb ) and key_cache ( Myisam ) for mysql tables. Below link : http://mysql.rjweb.org/doc.php/memory will give you a brief idea. Thanks
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
Hi Lukas,
In that case, such as Adarsh has said, you can configure until 70% of your
RAM for innodb_buffer_pool_size.
In your case, with 3GB RAM, I suggest you to configure until 2GB for MySQL:
Minimal for MyISAM (Maybe 32MB), and the rest for InnoDB. Your problem will
be loading data. Maybe your application will work slowly loading data
because there are more data than RAM memory.
Executing the following script, you can see your optimal buffer size for
InnoDB with your data.
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf10240,0,IF(PowerOf10243,0,PowerOf1024)))+0.4),
SUBSTR(' KMG',IF(PowerOf10240,0,
IF(PowerOf10243,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM
information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 3 PowerOf1024) B;
SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
FROM
(
SELECT SUM(data_length+index_length)*1.1*growth RIBPS
FROM information_schema.tables AAA,
(SELECT 1 growth) BBB
WHERE ENGINE='InnoDB'
) AA
) A;
Good luck!
Regards,
Antonio.
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
2014-02-12 12:32 GMT+01:00 Lukas Lehner webleh...@gmail.com: Hi Antonio all tables use InnoDB. The size is 27 GB (not yet in prod). I guess in prod it will be fast 80GB. Depending on how your application is going to use MySQL resources you will need to tweak some things (and not only MySQL). If it is going to be CPU bound, IO bound etc...there are different scenarios Anyways, some general things to take a look at: - Use file per table if possible. This won't give you extra performance, but it will be good if you run into disk spaces issues or for future table migrations. - Make sure you have trx_commit and sync_binlog disabled (make sure you understand what this means and what problems you could have during an un expected crash) - If you're expecting lot of temporary tables (filesorts), make sure tmpdir runs over a fast disk. - Use NUMA memory handling - Make sure you test different disk schedulers (depending if you have RAID and which kind of it) and see how they perform. - You might want to take a look to smp irq affinity and see how it could impact in your system. Manuel.
RE: Innodb, MySQL 5.5.28 - Would an incorrect setting in my.cnf cause mysqld to randomly crash on high load?
Nothing looks bad. 96G for the buffer_pool is bigger than I have experienced, but I know of no reason for it to fail (given that you have 128GB of RAM). -Original Message- From: Tom [mailto:livefortheda...@gmail.com] Sent: Wednesday, November 21, 2012 5:17 PM To: mysql@lists.mysql.com Subject: Innodb, MySQL 5.5.28 - Would an incorrect setting in my.cnf cause mysqld to randomly crash on high load? We have a high-end server, 128GB RAM, 32 Core , Xeon, SSD RAID 10 - running Ubuntu 12.04 with MySQL 5.5.28 . Doing random imports to large InnoDB tables, over 50+ gigs, randomly after a few hours of heavy load, mysql does a Signal 11 and crashes. We have tried to move hardware. Doing a full dump (but not a restore yet) gives no issues. Usually on corrupted tables, a dump would fail no? Below is the crash log and my.cnf . --- 12:45:4 UTC - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=536870912 read_buffer_size=131072 max_used_connections=324 max_threads=200 thread_count=308 connection_count=308 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 965187 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x7fc7eb1b5040 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7fadf6abfe60 thread_stack 0x3 /usr/sbin/mysqld(my_print_stacktrace+0x29)[0x7fc758522759] /usr/sbin/mysqld(handle_fatal_signal+0x483)[0x7fc7583e9ae3] /lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7fc75713bcb0] /usr/sbin/mysqld(+0x6671b0)[0x7fc75863a1b0] /usr/sbin/mysqld(+0x61d6b9)[0x7fc7585f06b9] /usr/sbin/mysqld(+0x630d12)[0x7fc758603d12] /usr/sbin/mysqld(+0x6319c2)[0x7fc7586049c2] /usr/sbin/mysqld(+0x631d85)[0x7fc758604d85] /usr/sbin/mysqld(+0x626e7d)[0x7fc7585f9e7d] /usr/sbin/mysqld(+0x633cea)[0x7fc758606cea] /usr/sbin/mysqld(+0x6347e2)[0x7fc7586077e2] /usr/sbin/mysqld(+0x624426)[0x7fc7585f7426] /usr/sbin/mysqld(+0x610871)[0x7fc7585e3871] /usr/sbin/mysqld(+0x5d4cb0)[0x7fc7585a7cb0] /usr/sbin/mysqld(+0x5b7c9c)[0x7fc75858ac9c] /usr/sbin/mysqld(_ZN7handler21read_multi_range_nextEPP18st_key_multi_ra nge+0x24)[0x7fc7583e9fe4] /usr/sbin/mysqld(_ZN18QUICK_RANGE_SELECT8get_nextEv+0x3c)[0x7fc7584a3c8 c] /usr/sbin/mysqld(+0x4e9195)[0x7fc7584bc195] /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x71)[0x7fc7582f 1741] /usr/sbin/mysqld(+0x32f025)[0x7fc758302025] /usr/sbin/mysqld(_ZN4JOIN4execEv+0x4a5)[0x7fc758311155] /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_E S2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_sele ct_lex+0x130)[0x7fc75830d000] /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x17c)[0x 7fc758312f5c] /usr/sbin/mysqld(+0x2f66b4)[0x7fc7582c96b4] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x16d8)[0x7fc7582d1118] /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x10f)[0x7fc758 2d5daf] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x13 80)[0x7fc7582d7200] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1bd)[0x7fc75837b7a d] /usr/sbin/mysqld(handle_one_connection+0x50)[0x7fc75837b810] /lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a)[0x7fc757133e9a] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fc756864cbd] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7faa4c18e440): is an invalid pointer Connection ID (thread ID): 2286 Status: NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 121120 12:48:48 [Note] Plugin 'FEDERATED' is disabled. 121120 12:48:48 InnoDB: The InnoDB memory heap is disabled 121120 12:48:48 InnoDB: Mutexes and rw_locks use GCC atomic builtins 121120 12:48:48 InnoDB: Compressed tables use zlib 1.2.3.4 121120 12:48:48 InnoDB: Initializing buffer pool, size = 96.0G 121120 12:48:56 InnoDB: Completed initialization of buffer pool 121120 12:48:57 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 1341738337497 121120 12:48:58 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log
Re: Innodb, MySQL 5.5.28 - Would an incorrect setting in my.cnf cause mysqld to randomly crash on high load?
Hi Tom, I am assuming nothing relevant shows up in dmesg, right? I have experienced random crashes like that and most of them turned to be HW issues - hard disk and memory banks related. Is it a HW RAID? Have you tried looking at the controller logs? (Megacli). And yes, corrupted tables would fail when restoring them (or even when backuping them). Good luck! Manuel 2012/11/26, Rick James rja...@yahoo-inc.com: Nothing looks bad. 96G for the buffer_pool is bigger than I have experienced, but I know of no reason for it to fail (given that you have 128GB of RAM). -Original Message- From: Tom [mailto:livefortheda...@gmail.com] Sent: Wednesday, November 21, 2012 5:17 PM To: mysql@lists.mysql.com Subject: Innodb, MySQL 5.5.28 - Would an incorrect setting in my.cnf cause mysqld to randomly crash on high load? We have a high-end server, 128GB RAM, 32 Core , Xeon, SSD RAID 10 - running Ubuntu 12.04 with MySQL 5.5.28 . Doing random imports to large InnoDB tables, over 50+ gigs, randomly after a few hours of heavy load, mysql does a Signal 11 and crashes. We have tried to move hardware. Doing a full dump (but not a restore yet) gives no issues. Usually on corrupted tables, a dump would fail no? Below is the crash log and my.cnf . --- 12:45:4 UTC - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=536870912 read_buffer_size=131072 max_used_connections=324 max_threads=200 thread_count=308 connection_count=308 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 965187 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x7fc7eb1b5040 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7fadf6abfe60 thread_stack 0x3 /usr/sbin/mysqld(my_print_stacktrace+0x29)[0x7fc758522759] /usr/sbin/mysqld(handle_fatal_signal+0x483)[0x7fc7583e9ae3] /lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7fc75713bcb0] /usr/sbin/mysqld(+0x6671b0)[0x7fc75863a1b0] /usr/sbin/mysqld(+0x61d6b9)[0x7fc7585f06b9] /usr/sbin/mysqld(+0x630d12)[0x7fc758603d12] /usr/sbin/mysqld(+0x6319c2)[0x7fc7586049c2] /usr/sbin/mysqld(+0x631d85)[0x7fc758604d85] /usr/sbin/mysqld(+0x626e7d)[0x7fc7585f9e7d] /usr/sbin/mysqld(+0x633cea)[0x7fc758606cea] /usr/sbin/mysqld(+0x6347e2)[0x7fc7586077e2] /usr/sbin/mysqld(+0x624426)[0x7fc7585f7426] /usr/sbin/mysqld(+0x610871)[0x7fc7585e3871] /usr/sbin/mysqld(+0x5d4cb0)[0x7fc7585a7cb0] /usr/sbin/mysqld(+0x5b7c9c)[0x7fc75858ac9c] /usr/sbin/mysqld(_ZN7handler21read_multi_range_nextEPP18st_key_multi_ra nge+0x24)[0x7fc7583e9fe4] /usr/sbin/mysqld(_ZN18QUICK_RANGE_SELECT8get_nextEv+0x3c)[0x7fc7584a3c8 c] /usr/sbin/mysqld(+0x4e9195)[0x7fc7584bc195] /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x71)[0x7fc7582f 1741] /usr/sbin/mysqld(+0x32f025)[0x7fc758302025] /usr/sbin/mysqld(_ZN4JOIN4execEv+0x4a5)[0x7fc758311155] /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_E S2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_sele ct_lex+0x130)[0x7fc75830d000] /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x17c)[0x 7fc758312f5c] /usr/sbin/mysqld(+0x2f66b4)[0x7fc7582c96b4] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x16d8)[0x7fc7582d1118] /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x10f)[0x7fc758 2d5daf] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x13 80)[0x7fc7582d7200] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1bd)[0x7fc75837b7a d] /usr/sbin/mysqld(handle_one_connection+0x50)[0x7fc75837b810] /lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a)[0x7fc757133e9a] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fc756864cbd] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7faa4c18e440): is an invalid pointer Connection ID (thread ID): 2286 Status: NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 121120 12:48:48 [Note] Plugin 'FEDERATED' is disabled. 121120 12:48:48 InnoDB: The InnoDB memory heap is disabled 121120 12:48:48 InnoDB: Mutexes and rw_locks use GCC atomic builtins 121120 12:48:48 InnoDB: Compressed tables use zlib 1.2.3.4 121120 12:48:48 InnoDB: Initializing buffer pool, size = 96.0G 121120 12:48:56 InnoDB: Completed initialization of buffer pool 121120 12:48:57
table cache value error in my.cnf file
i am getting this wierd error in the mysql log: 120604 8:31:32 [Warning] option 'table_cache': unsigned value 536870912 adjusted to 524288 I have 28G of ram in my server, can anyone tell me what this value should be set to ? what is the syntax - i have tried different syntax -- get the same error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: table cache value error in my.cnf file
Am 04.06.2012 14:39, schrieb Joey L: i am getting this wierd error in the mysql log: 120604 8:31:32 [Warning] option 'table_cache': unsigned value 536870912 adjusted to 524288 I have 28G of ram in my server, can anyone tell me what this value should be set to ? what is the syntax - i have tried different syntax -- get the same error table count * expected connections signature.asc Description: OpenPGP digital signature
Re: table cache value error in my.cnf file
Can you explain this further ? Sorry a little slow ? table count * expected connections -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: table cache value error in my.cnf file
Am 04.06.2012 14:45, schrieb Joey L: Can you explain this further ? Sorry a little slow ? table count * expected connections http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_table_cache signature.asc Description: OpenPGP digital signature
Re: table cache value error in my.cnf file
Joey, you've over allocated the cache. MySQL is telling you that it has corrected the allocation. Check out the docs for the meaning behind the numbers. On Mon, Jun 4, 2012 at 2:01 PM, Reindl Harald h.rei...@thelounge.netwrote: Am 04.06.2012 14:45, schrieb Joey L: Can you explain this further ? Sorry a little slow ? table count * expected connections http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_table_cache
Re: RFE: Allow to use version-specific my.cnf files
On 04/27/2012 03:26 PM, Shawn Green wrote: I frequently need to have multiple versions ready to operate on my machine at any time. I solved the configuration file problems by only setting them up in the basedir of the installed version. For those special occasions when I need to configure multiple copies of the same version, I create a separate set of --datadir folders and craft separate configuration files for both. I start mysqld using the --defaults-file option and point that at the special file for each instance. As a matter of convenience, if you need to constantly run with multiple instances on the same host and if any one of those may need to be using a different version than the others, then the utility mysqld_multi may be what you need to be looking at. Each instance you manage by the script can have their own separate set of settings all stored in the same, common, configuration file. Check it out: http://dev.mysql.com/doc/refman/5.1/en/mysqld-multi.html Hi Shawn, thanks for pointing to that. So we have a possibility to define different options for different server instances using mysqld_multi. My proposal allows to have something similar for clients as well (without need to specify any command-line argument), or in cases when we want to define the same options to more servers, according to their version. It's probably nothing we cannot do now. But it could be a nice option, much more cleaner in some environments. Cheers, Honza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: RFE: Allow to use version-specific my.cnf files
I think you need deal with only these: * Diff my.cnf (set as a parameter to mysqld) * Diff tree for all the data (and reflect this in my.cnf) * Diff port (3306 can't be shared between the instances) -Original Message- From: Andrés Tello [mailto:mr.crip...@gmail.com] Sent: Wednesday, April 25, 2012 8:52 AM To: Honza Horak Cc: mysql@lists.mysql.com Subject: Re: RFE: Allow to use version-specific my.cnf files Reads interesting, but... Why would you need that? I mean... If I run several databases in the same hardware, I use completely diferent paths for evertying, so I can have atomic, clean and specific files for each instance/version of the database I think is much more easy to migrato to another hardware that way, just copy the instance and you are set... On Wed, Apr 25, 2012 at 9:23 AM, Honza Horak hho...@redhat.com wrote: Hi, PostgreSQL allows to use version-specific configuration files, which allows to change some settings only for particular version of DB. I think a similar enhancement would be nice and usable for administrators of MySQL as well. Please, consider the attached patch as a simple proposal. Any comments are welcome. Cheers, Honza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: RFE: Allow to use version-specific my.cnf files
On 04/25/2012 05:52 PM, Andrés Tello wrote:
Reads interesting, but...
Why would you need that?
I mean... If I run several databases in the same hardware, I use completely
diferent paths for evertying, so I can have atomic, clean and specific
files for each instance/version of the database
Thanks for your opinion.
You're right, it doesn't make too much sense regarding system-wide
configuration files, such as /etc/my.cnf. A real use case I see is when
we speak about users' config files, like ~/.my.cnf.
Let's say we have two different MySQL versions on one hardware, then
it's possible we'll need a bit different options for each instance.
MySQL unfortunately doesn't distinguish between user-specific (usually
called rc files) and system-wide config files. Trying to have the patch
simple, I applied the feature to all config files (which was not necessary).
The attached patch now is a bit more complicated, but restricts the
feature only for config files in user's home directory. I believe this
makes more sense, than the original one.
Any comments welcome again.
Cheers,
Honza
diff -up mysql-5.5.22/mysys/default.c.versionedcnf mysql-5.5.22/mysys/default.c
--- mysql-5.5.22/mysys/default.c.versionedcnf 2012-03-02 20:44:47.0 +0100
+++ mysql-5.5.22/mysys/default.c 2012-04-27 09:44:01.136938181 +0200
@@ -37,6 +37,7 @@
#include m_string.h
#include m_ctype.h
#include my_dir.h
+#include mysql_version.h
#ifdef __WIN__
#include winbase.h
#endif
@@ -660,18 +661,24 @@ static int search_default_file(Process_o
const char *config_file)
{
char **ext;
+ char **version_ext;
const char *empty_list[]= { , 0 };
+ const char *versioned_list[]= { , - MYSQL_SERVER_VERSION, 0 };
my_bool have_ext= fn_ext(config_file)[0] != 0;
const char **exts_to_use= have_ext ? empty_list : f_extensions;
+ const char **versioned_exts_to_use= (dir[0] == FN_HOMELIB) ? versioned_list : empty_list;
for (ext= (char**) exts_to_use; *ext; ext++)
- {
-int error;
-if ((error= search_default_file_with_ext(opt_handler, handler_ctx,
- dir, *ext,
- config_file, 0)) 0)
- return error;
- }
+for (version_ext= (char**) versioned_exts_to_use; *version_ext; version_ext++)
+{
+ int error;
+ char full_ext[FN_REFLEN + sizeof(MYSQL_SERVER_VERSION) + 2];
+ strxmov(full_ext,*ext,*version_ext,NullS);
+ if ((error= search_default_file_with_ext(opt_handler, handler_ctx,
+ dir, full_ext,
+ config_file, 0)) 0)
+return error;
+}
return 0;
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: RFE: Allow to use version-specific my.cnf files
Hello Honza, On 4/27/2012 4:35 AM, Honza Horak wrote: On 04/25/2012 05:52 PM, Andrés Tello wrote: Reads interesting, but... Why would you need that? I mean... If I run several databases in the same hardware, I use completely diferent paths for evertying, so I can have atomic, clean and specific files for each instance/version of the database Thanks for your opinion. You're right, it doesn't make too much sense regarding system-wide configuration files, such as /etc/my.cnf. A real use case I see is when we speak about users' config files, like ~/.my.cnf. Let's say we have two different MySQL versions on one hardware, then it's possible we'll need a bit different options for each instance. MySQL unfortunately doesn't distinguish between user-specific (usually called rc files) and system-wide config files. Trying to have the patch simple, I applied the feature to all config files (which was not necessary). The attached patch now is a bit more complicated, but restricts the feature only for config files in user's home directory. I believe this makes more sense, than the original one. Any comments welcome again. Cheers, I frequently need to have multiple versions ready to operate on my machine at any time. I solved the configuration file problems by only setting them up in the basedir of the installed version. For those special occasions when I need to configure multiple copies of the same version, I create a separate set of --datadir folders and craft separate configuration files for both. I start mysqld using the --defaults-file option and point that at the special file for each instance. As a matter of convenience, if you need to constantly run with multiple instances on the same host and if any one of those may need to be using a different version than the others, then the utility mysqld_multi may be what you need to be looking at. Each instance you manage by the script can have their own separate set of settings all stored in the same, common, configuration file. Check it out: http://dev.mysql.com/doc/refman/5.1/en/mysqld-multi.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RFE: Allow to use version-specific my.cnf files
Hi,
PostgreSQL allows to use version-specific configuration files, which
allows to change some settings only for particular version of DB.
I think a similar enhancement would be nice and usable for
administrators of MySQL as well.
Please, consider the attached patch as a simple proposal. Any comments
are welcome.
Cheers,
Honza
diff -up mysql-5.5.22/mysys/default.c.versionedcnf mysql-5.5.22/mysys/default.c
--- mysql-5.5.22/mysys/default.c.versionedcnf 2012-03-02 20:44:47.0 +0100
+++ mysql-5.5.22/mysys/default.c 2012-04-25 14:51:32.824181063 +0200
@@ -37,6 +37,7 @@
#include m_string.h
#include m_ctype.h
#include my_dir.h
+#include mysql_version.h
#ifdef __WIN__
#include winbase.h
#endif
@@ -94,10 +95,10 @@ static my_bool defaults_already_read= FA
static const char **default_directories = NULL;
#ifdef __WIN__
-static const char *f_extensions[]= { .ini, .cnf, 0 };
+static const char *f_extensions[]= { .ini, .cnf, .ini- MYSQL_SERVER_VERSION, .cnf- MYSQL_SERVER_VERSION, 0 };
#define NEWLINE \r\n
#else
-static const char *f_extensions[]= { .cnf, 0 };
+static const char *f_extensions[]= { .cnf, .cnf- MYSQL_SERVER_VERSION, 0 };
#define NEWLINE \n
#endif
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: RFE: Allow to use version-specific my.cnf files
Reads interesting, but... Why would you need that? I mean... If I run several databases in the same hardware, I use completely diferent paths for evertying, so I can have atomic, clean and specific files for each instance/version of the database I think is much more easy to migrato to another hardware that way, just copy the instance and you are set... On Wed, Apr 25, 2012 at 9:23 AM, Honza Horak hho...@redhat.com wrote: Hi, PostgreSQL allows to use version-specific configuration files, which allows to change some settings only for particular version of DB. I think a similar enhancement would be nice and usable for administrators of MySQL as well. Please, consider the attached patch as a simple proposal. Any comments are welcome. Cheers, Honza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: can anyone tell me the best my.cnf Configuration for dedicated MySQL DB server
Am 20.02.2012 06:57, schrieb Nayan Darekar: hi members, I want install, configure my 8GB Ram 4 core CPU hardware server for dedicated MySQL DB Server with stable version of MySQL. So which version i should use and can anyone help me for best my.cnf Configuration. there does no best exist and will never be! * how much memory? * what other processes are running on the machine? * how much RAM will they cosume? this is the base to calculate how much memory is available for mysqld so and now you need a application profile * myisam / innodv / both * many parallel connections * how many different and how large results do you expect __ this is one of TEN different production configs here but be careful - with low memory it will blow away your machine and myisam_use_mmap will cause instability before MySQL 5.5.20 [--] Data in MyISAM tables: 635M (Tables: 4376) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 314 Security Recommendations --- [OK] All database users have passwords assigned Performance Metrics - [--] Up for: 7d 12h 1m 2s (20M q [31.984 qps], 954K conn, TX: 37B, RX: 4B) [--] Reads / Writes: 67% / 33% [--] Total buffers: 2.4G global + 3.2M per thread (200 max threads) [OK] Maximum possible memory usage: 3.0G (37% of installed RAM) [OK] Slow queries: 0% (5/20M) [OK] Highest usage of available connections: 13% (27/200) [OK] Key buffer size / total MyISAM indexes: 256.0M/130.8M [OK] Key buffer hit rate: 99.8% (169M cached / 268K reads) [OK] Query cache efficiency: 88.8% (14M cached / 16M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (1K temp sorts / 175K sorts) [!!] Joins performed without indexes: 3777 [OK] Temporary tables created on disk: 25% (61K on disk / 245K total) [OK] Thread cache hit rate: 99% (27 created / 954K connections) [OK] Table cache hit rate: 25% (4K open / 19K opened) [OK] Open file limit used: 30% (9K/30K) [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks) socket = /var/lib/mysql/mysql.sock character-set-server= latin1 collation-server= latin1_german1_ci default-time-zone = Europe/Vienna default-storage-engine = myisam lower_case_table_names = 1 port= 3306 old_passwords = 0 local-infile= 0 thread_concurrency = 16 delay-key-write = ALL concurrent_insert = 2 open-files-limit= 2 myisam-recover = FORCE myisam_use_mmap = 1 wait_timeout= 300 interactive_timeout = 300 max_allowed_packet = 200M max_connections = 200 max_tmp_tables = 150 max_connect_errors = 250 max_delayed_threads = 32 flush_time = 0 query_cache_limit = 220K query_cache_min_res_unit= 2K query_cache_size= 1536M query_cache_type= 1 table_cache = 15000 thread_cache= 200 table_definition_cache = 768 tmp_table_size = 640M max_heap_table_size = 640M key_buffer_size = 256M sort_buffer_size= 512K myisam_sort_buffer_size = 5M join_buffer_size= 2M preload_buffer_size = 256K read_buffer_size= 256K read_rnd_buffer_size= 256K large-pages low-priority-updates safe-user-create skip-federated skip-innodb skip-name-resolve skip-partition skip-archive skip-blackhole skip-symbolic-links slave_compressed_protocol __ signature.asc Description: OpenPGP digital signature
Not finding my.cnf file
Dear all, I install mysql in CentOS -5.4 through 2 commands : yum install mysql-server yum install mysql-client And I can see directories created in /var/lib/mysql directory. But now i want to change it to my /hdd1-1 diretcory and alse set logging directories. So , I search my.cnf file as: [root@ws-test ~]# find / -name my.cnf [root@ws-test ~]# Now how could I solve this issue from where mysql picks its configuration or it go for its default. Thanks best Regards, Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Not finding my.cnf file
You need to provide a my.cnf for your server. There are some sample files included with the server binaries that you can start with. These won't be tailored for your application/server so will need to be changed as per your requirements. Note that several of the options are static values so will require a MySQL restart before they will work. Best of luck Andrew On Wed, Jun 1, 2011 at 10:24 AM, Adarsh Sharma adarsh.sha...@orkash.comwrote: Dear all, I install mysql in CentOS -5.4 through 2 commands : yum install mysql-server yum install mysql-client And I can see directories created in /var/lib/mysql directory. But now i want to change it to my /hdd1-1 diretcory and alse set logging directories. So , I search my.cnf file as: [root@ws-test ~]# find / -name my.cnf [root@ws-test ~]# Now how could I solve this issue from where mysql picks its configuration or it go for its default. Thanks best Regards, Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com
Re: Not finding my.cnf file
I think the default location on Centos is /etc/my.cnf Regards John On 1 June 2011 10:24, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I install mysql in CentOS -5.4 through 2 commands : yum install mysql-server yum install mysql-client And I can see directories created in /var/lib/mysql directory. But now i want to change it to my /hdd1-1 diretcory and alse set logging directories. So , I search my.cnf file as: [root@ws-test ~]# find / -name my.cnf [root@ws-test ~]# Now how could I solve this issue from where mysql picks its configuration or it go for its default. Thanks best Regards, Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: Not finding my.cnf file
I got the error after setting my.cnf file in /etc directory. 110601 15:23:02 [Note] Plugin 'FEDERATED' is disabled. /usr/sbin/mysqld: Table 'mysql.plugin' doesn't exist After some research i found the cause of this error : the new my.cnf is very old and mysql_upgrade is needed So , Can someone Please give my a standard my.cnf file that contains all the parameters. Is my.cnf file is different for different versions. My mysql version is 5.1.4. Please check my attached my.cnf file Thanks John Daisley wrote: I think the default location on Centos is /etc/my.cnf Regards John On 1 June 2011 10:24, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I install mysql in CentOS -5.4 through 2 commands : yum install mysql-server yum install mysql-client And I can see directories created in /var/lib/mysql directory. But now i want to change it to my /hdd1-1 diretcory and alse set logging directories. So , I search my.cnf file as: [root@ws-test ~]# find / -name my.cnf [root@ws-test ~]# Now how could I solve this issue from where mysql picks its configuration or it go for its default. Thanks best Regards, Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk #This is for a large system with memory = 512M where the system runs mainly # MySQL. # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is @localstatedir@) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock #skip-locking # Caches and Buffer Sizes key_buffer = 256M max_allowed_packet=16M table_cache = 256 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 4M #record_buffer = 1M myisam_sort_buffer_size = 128M thread_cache = 128 query_cache_limit = 2M query_cache_type = 1 query_cache_size = 32M key_buffer = 16M join_buffer = 2M table_cache = 1024 datadir = /hdd2-1/myisam_data log-bin=mysql-bin #Time Outs interactive_timeout = 100 wait_timeout = 100 connect_timeout = 10 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 2 # Maximum connections allowed max_connections = 100 max_user_connections = 50 max_connect_errors = 10 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master-password and the slave fails to #connect), the slave will create a master.info file, and any later #change in this file to the variables' values below will be ignored and #overridden by the content of the master.info file, unless you shutdown #the slave server, delete master.info and restart the slaver server. #For that reason, you may want to leave the lines below untouched #(commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host= hostname # # The username the slave will use for authentication when connecting
Re: Not finding my.cnf file
Hi, You probably didn't run mysql_install_db. Peter Boros On Wed, 2011-06-01 at 15:52 +0530, Adarsh Sharma wrote: I got the error after setting my.cnf file in /etc directory. 110601 15:23:02 [Note] Plugin 'FEDERATED' is disabled. /usr/sbin/mysqld: Table 'mysql.plugin' doesn't exist After some research i found the cause of this error : the new my.cnf is very old and mysql_upgrade is needed So , Can someone Please give my a standard my.cnf file that contains all the parameters. Is my.cnf file is different for different versions. My mysql version is 5.1.4. Please check my attached my.cnf file Thanks John Daisley wrote: I think the default location on Centos is /etc/my.cnf Regards John On 1 June 2011 10:24, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I install mysql in CentOS -5.4 through 2 commands : yum install mysql-server yum install mysql-client And I can see directories created in /var/lib/mysql directory. But now i want to change it to my /hdd1-1 diretcory and alse set logging directories. So , I search my.cnf file as: [root@ws-test ~]# find / -name my.cnf [root@ws-test ~]# Now how could I solve this issue from where mysql picks its configuration or it go for its default. Thanks best Regards, Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk plain text document attachment (my.cnf) #This is for a large system with memory = 512M where the system runs mainly # MySQL. # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is @localstatedir@) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock #skip-locking # Caches and Buffer Sizes key_buffer = 256M max_allowed_packet=16M table_cache = 256 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 4M #record_buffer = 1M myisam_sort_buffer_size = 128M thread_cache = 128 query_cache_limit = 2M query_cache_type = 1 query_cache_size = 32M key_buffer = 16M join_buffer = 2M table_cache = 1024 datadir = /hdd2-1/myisam_data log-bin=mysql-bin #Time Outs interactive_timeout = 100 wait_timeout = 100 connect_timeout = 10 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 2 # Maximum connections allowed max_connections = 100 max_user_connections = 50 max_connect_errors = 10 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master-password and the slave fails to #connect), the slave will create a master.info file, and any later #change in this file to the variables' values below will be ignored and #overridden by the content of the master.info file, unless you shutdown #the slave server, delete master.info and restart the slaver server. #For that reason, you may want to leave the lines below untouched #(commented
Re: Not finding my.cnf file
Thanks, It works a new error occurs as : 110601 16:16:16 mysqld_safe Starting mysqld daemon with databases from /hdd2-1/myisam_data /usr/sbin/mysqld: File '/hdd2-1/myisam_data/mysql-bin.index' not found (Errcode: 13) 110601 16:16:16 [ERROR] Aborting 110601 16:16:16 [Note] /usr/sbin/mysqld: Shutdown complete I checked /hdd2-1/myisam_data/mysql-bin.index file is there , how to comes Thanks Peter Boros wrote: Hi, You probably didn't run mysql_install_db. Peter Boros On Wed, 2011-06-01 at 15:52 +0530, Adarsh Sharma wrote: I got the error after setting my.cnf file in /etc directory. 110601 15:23:02 [Note] Plugin 'FEDERATED' is disabled. /usr/sbin/mysqld: Table 'mysql.plugin' doesn't exist After some research i found the cause of this error : the new my.cnf is very old and mysql_upgrade is needed So , Can someone Please give my a standard my.cnf file that contains all the parameters. Is my.cnf file is different for different versions. My mysql version is 5.1.4. Please check my attached my.cnf file Thanks John Daisley wrote: I think the default location on Centos is /etc/my.cnf Regards John On 1 June 2011 10:24, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I install mysql in CentOS -5.4 through 2 commands : yum install mysql-server yum install mysql-client And I can see directories created in /var/lib/mysql directory. But now i want to change it to my /hdd1-1 diretcory and alse set logging directories. So , I search my.cnf file as: [root@ws-test ~]# find / -name my.cnf [root@ws-test ~]# Now how could I solve this issue from where mysql picks its configuration or it go for its default. Thanks best Regards, Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk plain text document attachment (my.cnf) #This is for a large system with memory = 512M where the system runs mainly # MySQL. # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is @localstatedir@) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock #skip-locking # Caches and Buffer Sizes key_buffer = 256M max_allowed_packet=16M table_cache = 256 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 4M #record_buffer = 1M myisam_sort_buffer_size = 128M thread_cache = 128 query_cache_limit = 2M query_cache_type = 1 query_cache_size = 32M key_buffer = 16M join_buffer = 2M table_cache = 1024 datadir = /hdd2-1/myisam_data log-bin=mysql-bin #Time Outs interactive_timeout = 100 wait_timeout = 100 connect_timeout = 10 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 2 # Maximum connections allowed max_connections = 100 max_user_connections = 50 max_connect_errors = 10 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master-password and the slave fails to #connect), the slave will create a master.info file, and any later #change in this file to the variables' values below will be ignored and #overridden
Re: Not finding my.cnf file
Check the permissions in the datadir. On Wed, Jun 1, 2011 at 11:50 AM, Adarsh Sharma adarsh.sha...@orkash.comwrote: Thanks, It works a new error occurs as : 110601 16:16:16 mysqld_safe Starting mysqld daemon with databases from /hdd2-1/myisam_data /usr/sbin/mysqld: File '/hdd2-1/myisam_data/mysql-bin.index' not found (Errcode: 13) 110601 16:16:16 [ERROR] Aborting 110601 16:16:16 [Note] /usr/sbin/mysqld: Shutdown complete I checked /hdd2-1/myisam_data/mysql-bin.index file is there , how to comes Thanks Peter Boros wrote: Hi, You probably didn't run mysql_install_db. Peter Boros On Wed, 2011-06-01 at 15:52 +0530, Adarsh Sharma wrote: I got the error after setting my.cnf file in /etc directory. 110601 15:23:02 [Note] Plugin 'FEDERATED' is disabled. /usr/sbin/mysqld: Table 'mysql.plugin' doesn't exist After some research i found the cause of this error : the new my.cnf is very old and mysql_upgrade is needed So , Can someone Please give my a standard my.cnf file that contains all the parameters. Is my.cnf file is different for different versions. My mysql version is 5.1.4. Please check my attached my.cnf file Thanks John Daisley wrote: I think the default location on Centos is /etc/my.cnf Regards John On 1 June 2011 10:24, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I install mysql in CentOS -5.4 through 2 commands : yum install mysql-server yum install mysql-client And I can see directories created in /var/lib/mysql directory. But now i want to change it to my /hdd1-1 diretcory and alse set logging directories. So , I search my.cnf file as: [root@ws-test ~]# find / -name my.cnf [root@ws-test ~]# Now how could I solve this issue from where mysql picks its configuration or it go for its default. Thanks best Regards, Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk plain text document attachment (my.cnf) #This is for a large system with memory = 512M where the system runs mainly # MySQL. # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is @localstatedir@) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock #skip-locking # Caches and Buffer Sizes key_buffer = 256M max_allowed_packet=16M table_cache = 256 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 4M #record_buffer = 1M myisam_sort_buffer_size = 128M thread_cache = 128 query_cache_limit = 2M query_cache_type = 1 query_cache_size = 32M key_buffer = 16M join_buffer = 2M table_cache = 1024 datadir = /hdd2-1/myisam_data log-bin=mysql-bin #Time Outs interactive_timeout = 100 wait_timeout = 100 connect_timeout = 10 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 2 # Maximum connections allowed max_connections = 100 max_user_connections = 50 max_connect_errors = 10 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped
Re: my.cnf file
Are you show about the non-outage operation with this command? Best regards. -- Wagner Bianchi 2010/12/31 Sharl.Jimh.Tsin amoiz.sh...@gmail.com rpm -qpi mysql*.rpm | grep my.cnf Best regards, Sharl.Jimh.Tsin (From China **Obviously Taiwan INCLUDED**) 2010/12/30 Lydia Rowe ly...@lydiarowe.com: find / -name my.cnf -- Lydia On Thu, 2010-12-30 at 11:09 -0200, Wagner Bianchi wrote: I am seeing you're using an operate system based on Red Hat distro. Well, after install MySQL via yum or via rpm packages, the location of MySQL samples configuration file usually is /usr/share/mysql. After to check the existence of sample configuration files (my-huge.cnf, my-large.cnf, my-medium.cnf ...), use linux command line cp to copy it to /etc or /etc/mysql and restart mysqld. Could you check it? Best regards. -- Wagner Bianchi 2010/12/30 andrew.2.mo...@nokia.com Adam, you should look upon this as an opportunity to write a my.cnf that suits your application and hardware. Understanding the options in this configuration can be paramount to a well tuned server. a few resources to kick it all off... http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html http://dev.mysql.com/tech-resources/articles/mysql_intro.html#SECTION000150 http://ronaldbradford.com/blog/tag/my-cnf/ Andy From: ext Adarsh Sharma [adarsh.sha...@orkash.com] Sent: 30 December 2010 06:37 To: mysql@lists.mysql.com Subject: my.cnf file Dear all, I am able to install Mysql-5.1.4 o a Linux Machine without any error. All is working fine. But I am searching a file my.cnf which is most important and is used in mysql but cannot able to find it. I install mysql by yum install mysql-server and yum install mysql-client commands. I find only a folder in /var/lib/ i.e mysql folder that contains ibdata and database folder plus .sock and .err file. After some research i find default path of my.cnf is /etc/my.cnf, /etc/mysql/my.cnf, /var/lib/mysql/my.cnf. But couldn,t locate it as it is needed for changing data dirs. Please help. Thanks Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=amoiz.sh...@gmail.com
Re: my.cnf file
Please, forget my last note, I answered in a wrong thread! Sorry. Best regards. -- Wagner Bianchi 2010/12/31 Wagner Bianchi wagnerbianch...@gmail.com Are you show about the non-outage operation with this command? Best regards. -- Wagner Bianchi 2010/12/31 Sharl.Jimh.Tsin amoiz.sh...@gmail.com rpm -qpi mysql*.rpm | grep my.cnf Best regards, Sharl.Jimh.Tsin (From China **Obviously Taiwan INCLUDED**) 2010/12/30 Lydia Rowe ly...@lydiarowe.com: find / -name my.cnf -- Lydia On Thu, 2010-12-30 at 11:09 -0200, Wagner Bianchi wrote: I am seeing you're using an operate system based on Red Hat distro. Well, after install MySQL via yum or via rpm packages, the location of MySQL samples configuration file usually is /usr/share/mysql. After to check the existence of sample configuration files (my-huge.cnf, my-large.cnf, my-medium.cnf ...), use linux command line cp to copy it to /etc or /etc/mysql and restart mysqld. Could you check it? Best regards. -- Wagner Bianchi 2010/12/30 andrew.2.mo...@nokia.com Adam, you should look upon this as an opportunity to write a my.cnf that suits your application and hardware. Understanding the options in this configuration can be paramount to a well tuned server. a few resources to kick it all off... http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html http://dev.mysql.com/tech-resources/articles/mysql_intro.html#SECTION000150 http://ronaldbradford.com/blog/tag/my-cnf/ Andy From: ext Adarsh Sharma [adarsh.sha...@orkash.com] Sent: 30 December 2010 06:37 To: mysql@lists.mysql.com Subject: my.cnf file Dear all, I am able to install Mysql-5.1.4 o a Linux Machine without any error. All is working fine. But I am searching a file my.cnf which is most important and is used in mysql but cannot able to find it. I install mysql by yum install mysql-server and yum install mysql-client commands. I find only a folder in /var/lib/ i.e mysql folder that contains ibdata and database folder plus .sock and .err file. After some research i find default path of my.cnf is /etc/my.cnf, /etc/mysql/my.cnf, /var/lib/mysql/my.cnf. But couldn,t locate it as it is needed for changing data dirs. Please help. Thanks Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=amoiz.sh...@gmail.com
RE: my.cnf file
Adam, you should look upon this as an opportunity to write a my.cnf that suits your application and hardware. Understanding the options in this configuration can be paramount to a well tuned server. a few resources to kick it all off... http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html http://dev.mysql.com/tech-resources/articles/mysql_intro.html#SECTION000150 http://ronaldbradford.com/blog/tag/my-cnf/ Andy From: ext Adarsh Sharma [adarsh.sha...@orkash.com] Sent: 30 December 2010 06:37 To: mysql@lists.mysql.com Subject: my.cnf file Dear all, I am able to install Mysql-5.1.4 o a Linux Machine without any error. All is working fine. But I am searching a file my.cnf which is most important and is used in mysql but cannot able to find it. I install mysql by yum install mysql-server and yum install mysql-client commands. I find only a folder in /var/lib/ i.e mysql folder that contains ibdata and database folder plus .sock and .err file. After some research i find default path of my.cnf is /etc/my.cnf, /etc/mysql/my.cnf, /var/lib/mysql/my.cnf. But couldn,t locate it as it is needed for changing data dirs. Please help. Thanks Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.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: my.cnf file
I am seeing you're using an operate system based on Red Hat distro. Well, after install MySQL via yum or via rpm packages, the location of MySQL samples configuration file usually is /usr/share/mysql. After to check the existence of sample configuration files (my-huge.cnf, my-large.cnf, my-medium.cnf ...), use linux command line cp to copy it to /etc or /etc/mysql and restart mysqld. Could you check it? Best regards. -- Wagner Bianchi 2010/12/30 andrew.2.mo...@nokia.com Adam, you should look upon this as an opportunity to write a my.cnf that suits your application and hardware. Understanding the options in this configuration can be paramount to a well tuned server. a few resources to kick it all off... http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html http://dev.mysql.com/tech-resources/articles/mysql_intro.html#SECTION000150 http://ronaldbradford.com/blog/tag/my-cnf/ Andy From: ext Adarsh Sharma [adarsh.sha...@orkash.com] Sent: 30 December 2010 06:37 To: mysql@lists.mysql.com Subject: my.cnf file Dear all, I am able to install Mysql-5.1.4 o a Linux Machine without any error. All is working fine. But I am searching a file my.cnf which is most important and is used in mysql but cannot able to find it. I install mysql by yum install mysql-server and yum install mysql-client commands. I find only a folder in /var/lib/ i.e mysql folder that contains ibdata and database folder plus .sock and .err file. After some research i find default path of my.cnf is /etc/my.cnf, /etc/mysql/my.cnf, /var/lib/mysql/my.cnf. But couldn,t locate it as it is needed for changing data dirs. Please help. Thanks Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Re: my.cnf file
find / -name my.cnf -- Lydia On Thu, 2010-12-30 at 11:09 -0200, Wagner Bianchi wrote: I am seeing you're using an operate system based on Red Hat distro. Well, after install MySQL via yum or via rpm packages, the location of MySQL samples configuration file usually is /usr/share/mysql. After to check the existence of sample configuration files (my-huge.cnf, my-large.cnf, my-medium.cnf ...), use linux command line cp to copy it to /etc or /etc/mysql and restart mysqld. Could you check it? Best regards. -- Wagner Bianchi 2010/12/30 andrew.2.mo...@nokia.com Adam, you should look upon this as an opportunity to write a my.cnf that suits your application and hardware. Understanding the options in this configuration can be paramount to a well tuned server. a few resources to kick it all off... http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html http://dev.mysql.com/tech-resources/articles/mysql_intro.html#SECTION000150 http://ronaldbradford.com/blog/tag/my-cnf/ Andy From: ext Adarsh Sharma [adarsh.sha...@orkash.com] Sent: 30 December 2010 06:37 To: mysql@lists.mysql.com Subject: my.cnf file Dear all, I am able to install Mysql-5.1.4 o a Linux Machine without any error. All is working fine. But I am searching a file my.cnf which is most important and is used in mysql but cannot able to find it. I install mysql by yum install mysql-server and yum install mysql-client commands. I find only a folder in /var/lib/ i.e mysql folder that contains ibdata and database folder plus .sock and .err file. After some research i find default path of my.cnf is /etc/my.cnf, /etc/mysql/my.cnf, /var/lib/mysql/my.cnf. But couldn,t locate it as it is needed for changing data dirs. Please help. Thanks Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@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: my.cnf file
rpm -qpi mysql*.rpm | grep my.cnf Best regards, Sharl.Jimh.Tsin (From China **Obviously Taiwan INCLUDED**) 2010/12/30 Lydia Rowe ly...@lydiarowe.com: find / -name my.cnf -- Lydia On Thu, 2010-12-30 at 11:09 -0200, Wagner Bianchi wrote: I am seeing you're using an operate system based on Red Hat distro. Well, after install MySQL via yum or via rpm packages, the location of MySQL samples configuration file usually is /usr/share/mysql. After to check the existence of sample configuration files (my-huge.cnf, my-large.cnf, my-medium.cnf ...), use linux command line cp to copy it to /etc or /etc/mysql and restart mysqld. Could you check it? Best regards. -- Wagner Bianchi 2010/12/30 andrew.2.mo...@nokia.com Adam, you should look upon this as an opportunity to write a my.cnf that suits your application and hardware. Understanding the options in this configuration can be paramount to a well tuned server. a few resources to kick it all off... http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html http://dev.mysql.com/tech-resources/articles/mysql_intro.html#SECTION000150 http://ronaldbradford.com/blog/tag/my-cnf/ Andy From: ext Adarsh Sharma [adarsh.sha...@orkash.com] Sent: 30 December 2010 06:37 To: mysql@lists.mysql.com Subject: my.cnf file Dear all, I am able to install Mysql-5.1.4 o a Linux Machine without any error. All is working fine. But I am searching a file my.cnf which is most important and is used in mysql but cannot able to find it. I install mysql by yum install mysql-server and yum install mysql-client commands. I find only a folder in /var/lib/ i.e mysql folder that contains ibdata and database folder plus .sock and .err file. After some research i find default path of my.cnf is /etc/my.cnf, /etc/mysql/my.cnf, /var/lib/mysql/my.cnf. But couldn,t locate it as it is needed for changing data dirs. Please help. Thanks Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=amoiz.sh...@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
my.cnf file
Dear all, I am able to install Mysql-5.1.4 o a Linux Machine without any error. All is working fine. But I am searching a file my.cnf which is most important and is used in mysql but cannot able to find it. I install mysql by yum install mysql-server and yum install mysql-client commands. I find only a folder in /var/lib/ i.e mysql folder that contains ibdata and database folder plus .sock and .err file. After some research i find default path of my.cnf is /etc/my.cnf, /etc/mysql/my.cnf, /var/lib/mysql/my.cnf. But couldn,t locate it as it is needed for changing data dirs. Please help. Thanks Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
is changing my.cnf without restart safe?
Hello MySQL Community, Last Friday I changed /etc/mysql/my.cnf file (at server) accidentally. I set the variable innodb_data_file_path to ibdata1:100M Then I realized that I changed the server copy. Then to get the original value I issued the query : SHOW VARIABLES LIKE 'innodb_data_file_path' I get the following: innodb_data_file_path = ibdata1:4000M;ibdata2:4000M;ibdata3:4000M;ibdata4:4000M:autoextend I wrote this value to my.cnf again. MySQL isn't restarted in these whole process. Whole thing took 5-10 minutes. Here is my questions: if i change something in my.cnf, they are not activated until i restart mysql right? is above scenario safe? Do you think i messed up something? Thank you for your help. -- 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 changing my.cnf without restart safe?
No, this is in and of itself safe. I didn't realise you could change the InnoDB datafiles on the fly, though - thanks for that hint :-) MySQL will never write the config file itself, so you're not at risk of conflict there. You are at risk of putting something in the configfile which messes up your MySQL server at the next restart, however; but that's pretty much the case for any other daemon, too. On Mon, Nov 8, 2010 at 11:06 AM, AHMET ARSLAN aarsl...@anadolu.edu.trwrote: Hello MySQL Community, Last Friday I changed /etc/mysql/my.cnf file (at server) accidentally. I set the variable innodb_data_file_path to ibdata1:100M Then I realized that I changed the server copy. Then to get the original value I issued the query : SHOW VARIABLES LIKE 'innodb_data_file_path' I get the following: innodb_data_file_path = ibdata1:4000M;ibdata2:4000M;ibdata3:4000M;ibdata4:4000M:autoextend I wrote this value to my.cnf again. MySQL isn't restarted in these whole process. Whole thing took 5-10 minutes. Here is my questions: if i change something in my.cnf, they are not activated until i restart mysql right? is above scenario safe? Do you think i messed up something? Thank you for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
my.cnf settings
I currently have a dedicated database server with 8 GBs of RAM and 8 1.60 GHz processors. The tables on my databases are almost exclusively InnoDB, except for 2-3 tables that are MyISAM and used for logging purposes (lots of INSERT DELAYED statements). I have the following settings in my my.cnf, and I'm having trouble adjusting the innodb_buffer_pool_size to something logical. I first tried setting it to 6000M, but the server went OOM and eventually crashed. I've subsequently kept bringing it down, and now it's at 4000M but it looks like swap is still being hit. $ free -m total used free sharedbuffers cached Mem: 7982 7943 38 0 8175 -/+ buffers/cache: 7759222 Swap: 1992702 1289 I spent some time looking at various Google links to figure out memory usage, and what I'm confused by is how mysqld is still talking up 8388m of virtual memory (according to top) and has 6.7g of physical memory used. http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/ http://www.mysqlperformanceblog.com/2009/02/12/how-much-memory-can-mysql-use-in-the-worst-case/ What I'm trying to figure out is 1. Are there settings I should turn down for myisam or myisamchk, and is that why I'm hitting 6.7GBs of actual memory? 2. Is 4000M the correct setting for innodb_buffer_pool_size? 3. Even if it is 6.7 GBs of memory, isn't 1.3 GBs of RAM (give or take) more than enough to run the rest of the machine? I don't see anything else coming close to the memory footprint of mysql, and I'm not sure why swap is still getting hit. [mysqld] #datadir=/home/mysql socket=/var/lib/mysql/mysql.sock max_connections = 320 safe-show-database skip-locking key_buffer = 192M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 32M thread_concurrency = 8 wait_timeout = 15 innodb_buffer_pool_size=4000M innodb_log_buffer_size=4M #innodb_log_file_size=128M #innodb_flush_method=O_DIRECT innodb_flush_log_at_trx_commit=2 log-slow-queries=/var/log/mysql/log-slow-queries.log log-error=/var/log/mysql/mysqlerror.log #innodb_file_per_table sql-mode=NO_AUTO_VALUE_ON_ZERO [mysqldump] quick max_allowed_packet = 16M [mysql] #no-auto-rehash max_allowed_packet = 1M [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M
Re: Performance Innodb my.cnf
Hi Ortis, How abt the hits or load i.e ( DML, DDL ) to the server. My initial assessment after looking at you cnf file is 1) Calculate and place an appropriate value for innodb_buffer_pool_size 2) Reduse the innodb_thread_concurrency to 4 or 8. and how about the no. of tables in the database and the table type. On Sun, Jan 17, 2010 at 3:03 AM, Junior Ortis jror...@gmail.com wrote: Hi guys, first thanks for all help, this list is amazing. Well i have a dedicated server on Fedora 11 x64, its have 12GB ram and a SCSI 15k rpm on datadir. I need a improve on my mysql conf to that my software run better, its my.cnf HOW i Can improve this :D Thanks !! HERE: [client] #password = [your_password] port= 3306 socket = /tmp/mysql.sock # *** Application-specific options follow here *** # # The MySQL server # [mysqld] # generic configuration options port= 3306 socket = /tmp/mysql.sock skip-locking skip-external-locking datadir = /disk3/mysql net_buffer_length = 1024K join_buffer_size= 1M sort_buffer_size= 4M read_buffer_size= 4M read_rnd_buffer_size= 4M table_cache = 500 max_allowed_packet = 16M max_connections=30 max_user_connections=200 key_buffer = 1000M key_buffer_size = 1000M #thread_cache = 400 thread_stack= 128K thread_cache_size = 1024 thread_concurrency = 8 #thread_stack = 128K default-character-set = utf8 innodb_flush_method=O_DIRECT innodb_buffer_pool_size= 11000M innodb_additional_mem_pool_size=10M innodb_log_file_size= 256M innodb_log_buffer_size=4M innodb_flush_log_at_trx_commit=0 innodb_thread_concurrency=32 innodb_file_per_table innodb_table_locks=0 query_alloc_block_size = 16k query_cache_limit = 512M query_cache_size= 512M query_cache_type= 1 long_query_time = 3 table_cache = 800 #innodb_force_recovery = 3 table_definition_cache = 800 query_cache_min_res_unit = 5K delay-key-write=OFF innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_support_xa = false innodb_io_capacity = 1 innodb_max_dirty_pages_pct = 90 concurrent_insert = 2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Realistic settings for [myisamchk] in my.cnf
I'm looking to optimize the myisamchk settings for some table rebuilds I need to do. I'm running CentOS 5 and MySQL 5.1 in a VMWare VM with 4 vCPUs and 4GB of memory. All the examples I can find online look like they are several years old, and just copied from someone else's config. I think with 4GB of memory, the settings can be better than this example: [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M Any suggestions? Thanks, -Hank query, mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cannot find my.cnf file
Sydney Puente schrieb: Hello, I want to log all sql queries made against a mysql db. Googled and found I should add a line to my.cnf. However I cannot find a my.cnf file [r...@radium init.d]# ps -ef | grep mysql root 13614 1 0 Sep24 ?00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/object01.pid mysql13669 13614 0 Sep24 ?00:21:40 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/object01.err --pid-file=/var/lib/mysql/object01.pid root 23050 22746 0 19:05 pts/000:00:00 grep mysql [r...@radium init.d]# locate cnf /usr/share/doc/MySQL-server-community-5.1.39/my-huge.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-innodb-heavy-4G.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-large.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-medium.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-small.cnf /usr/share/man/man8/cnfsheadconf.8.gz /usr/share/man/man8/cnfsstat.8.gz /usr/share/ssl/openssl.cnf /usr/share/mysql/my-large.cnf /usr/share/mysql/my-huge.cnf /usr/share/mysql/my-innodb-heavy-4G.cnf /usr/share/mysql/my-medium.cnf /usr/share/mysql/my-small.cnf Any ideas? I might add i did not install mysql and I did not start it and the guy who did is in holiday! Systemwide config files are always in /etc/ (see: man hier). re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: cannot find my.cnf file
Please look in /etc/my.cnf If not found create one. Of cause, MySQL can start and run without a 'my.cnf' file that’s why you couldn't find one. In the absence of the my.cnf file, MySQL will use default for every parameter . Charles, -Original Message- From: walter harms [mailto:wha...@bfs.de] Sent: Friday, November 13, 2009 2:44 AM To: Sydney Puente Cc: mysql@lists.mysql.com Subject: Re: cannot find my.cnf file Sydney Puente schrieb: Hello, I want to log all sql queries made against a mysql db. Googled and found I should add a line to my.cnf. However I cannot find a my.cnf file [r...@radium init.d]# ps -ef | grep mysql root 13614 1 0 Sep24 ?00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/object01.pid mysql13669 13614 0 Sep24 ?00:21:40 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/object01.err --pid-file=/var/lib/mysql/object01.pid root 23050 22746 0 19:05 pts/000:00:00 grep mysql [r...@radium init.d]# locate cnf /usr/share/doc/MySQL-server-community-5.1.39/my-huge.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-innodb-heavy-4G.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-large.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-medium.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-small.cnf /usr/share/man/man8/cnfsheadconf.8.gz /usr/share/man/man8/cnfsstat.8.gz /usr/share/ssl/openssl.cnf /usr/share/mysql/my-large.cnf /usr/share/mysql/my-huge.cnf /usr/share/mysql/my-innodb-heavy-4G.cnf /usr/share/mysql/my-medium.cnf /usr/share/mysql/my-small.cnf Any ideas? I might add i did not install mysql and I did not start it and the guy who did is in holiday! Systemwide config files are always in /etc/ (see: man hier). re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=cbr...@bmi.com This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you.
Fw: cannot find my.cnf file
Guys, That's a great response. Thanks. I have copied /usr/share/doc/MySQL-server-community-5.1.39/my-huge.cnfto /etc/my.cnf and restarted. However I can no longer log on via the command line from the database box, I can still log via a remote client So I presume the problem is related to the host in the mysql.user table. mysql -h localhost -u martin -p Enter password: ERROR 1045 (28000): Access denied for user 'martin'@'localhost' (using password: YES) mysql select host from mysql.user where user = 'martin'; +--+ | host | +--+ | %| +--+ mysql select host from mysql.user where user = 'root'; +---+ | host | +---+ | % | | 127.0.0.1 | | localhost | | object01 | +---+ Sydney Puente schrieb: Hello, I want to log all sql queries made against a mysql db. Googled and found I should add a line to my.cnf. However I cannot find a my.cnf file [r...@radium init.d]# ps -ef | grep mysql root 13614 1 0 Sep24 ?00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/object01.pid mysql13669 13614 0 Sep24 ?00:21:40 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/object01.err --pid-file=/var/lib/mysql/object01.pid root 23050 22746 0 19:05 pts/000:00:00 grep mysql [r...@radium init.d]# locate cnf /usr/share/doc/MySQL-server-community-5.1.39/my-huge.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-innodb-heavy-4G.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-large.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-medium.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-small.cnf /usr/share/man/man8/cnfsheadconf.8.gz /usr/share/man/man8/cnfsstat.8.gz /usr/share/ssl/openssl.cnf /usr/share/mysql/my-large.cnf /usr/share/mysql/my-huge.cnf /usr/share/mysql/my-innodb-heavy-4G.cnf /usr/share/mysql/my-medium.cnf /usr/share/mysql/my-small.cnf Any ideas? I might add i did not install mysql and I did not start it and the guy who did is in holiday! Systemwide config files are always in /etc/ (see: man hier). re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fw: cannot find my.cnf file
H I wouldn't use that my-huge.cnf file unless that machine is a dedicated mysql server with plenty of ram and even then I wouldn't use it without modification. Are you sure you have the password correct? A % under host is a synonym for 'any host'. Regards John Guys, That's a great response. Thanks. I have copied /usr/share/doc/MySQL-server-community-5.1.39/my-huge.cnfto /etc/my.cnf and restarted. However I can no longer log on via the command line from the database box, I can still log via a remote client So I presume the problem is related to the host in the mysql.user table. mysql -h localhost -u martin -p Enter password: ERROR 1045 (28000): Access denied for user 'martin'@'localhost' (using password: YES) mysql select host from mysql.user where user = 'martin'; +--+ | host | +--+ | %| +--+ mysql select host from mysql.user where user = 'root'; +---+ | host | +---+ | % | | 127.0.0.1 | | localhost | | object01 | +---+ Sydney Puente schrieb: Hello, I want to log all sql queries made against a mysql db. Googled and found I should add a line to my.cnf. However I cannot find a my.cnf file [r...@radium init.d]# ps -ef | grep mysql root 13614 1 0 Sep24 ?00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/object01.pid mysql13669 13614 0 Sep24 ?00:21:40 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/object01.err --pid-file=/var/lib/mysql/object01.pid root 23050 22746 0 19:05 pts/000:00:00 grep mysql [r...@radium init.d]# locate cnf /usr/share/doc/MySQL-server-community-5.1.39/my-huge.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-innodb-heavy-4G.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-large.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-medium.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-small.cnf /usr/share/man/man8/cnfsheadconf.8.gz /usr/share/man/man8/cnfsstat.8.gz /usr/share/ssl/openssl.cnf /usr/share/mysql/my-large.cnf /usr/share/mysql/my-huge.cnf /usr/share/mysql/my-innodb-heavy-4G.cnf /usr/share/mysql/my-medium.cnf /usr/share/mysql/my-small.cnf Any ideas? I might add i did not install mysql and I did not start it and the guy who did is in holiday! Systemwide config files are always in /etc/ (see: man hier). re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cannot find my.cnf file
Yes I enter the password manually into the remote mysql client (actually Oracle's SQL developer) when I login. I thought I would restart mysql with the /etc/init.d/mysql script and go back to the original default settings without any my.cnf present. Just to check it was some setting in my.cnf that caused the log in problem. # ./mysql status MySQL is running but PID file could not be found [FAILED] # ./mysql stop MySQL manager or server PID file could not be found! [FAILED] In fact I cannot find a pid file anywhere on the box. # ps -ef | grep mysql root 6517 1 0 10:10 pts/000:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/radium01.pid mysql 6623 6517 0 10:10 pts/000:00:24 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/object01.err --pid-file=/var/lib/mysql/object01.pid --socket=/var/lib/mysql/mysql.sock --port=3306 # locate pid | grep mysql /usr/share/man/man1/mysql_waitpid.1.gz /usr/bin/mysql_waitpid I have no idea why a pid file would be missing! any ideas Syd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: cannot find my.cnf file
Did you remove the my.cnf file and then run /etc/init.d/mysql stop? The my.cnf probably had non-default paths for the pid file, so if you remove the config file, now the startup script is looking in the wrong location. Also for your password issue, please show use the exact command you're using to try to log in, and the exact error message you get. Regards Gavin Towey -Original Message- From: Sydney Puente [mailto:sydneypue...@yahoo.com] Sent: Friday, November 13, 2009 5:31 AM To: mysql@lists.mysql.com Subject: Re: cannot find my.cnf file Yes I enter the password manually into the remote mysql client (actually Oracle's SQL developer) when I login. I thought I would restart mysql with the /etc/init.d/mysql script and go back to the original default settings without any my.cnf present. Just to check it was some setting in my.cnf that caused the log in problem. # ./mysql status MySQL is running but PID file could not be found [FAILED] # ./mysql stop MySQL manager or server PID file could not be found! [FAILED] In fact I cannot find a pid file anywhere on the box. # ps -ef | grep mysql root 6517 1 0 10:10 pts/000:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/radium01.pid mysql 6623 6517 0 10:10 pts/000:00:24 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/object01.err --pid-file=/var/lib/mysql/object01.pid --socket=/var/lib/mysql/mysql.sock --port=3306 # locate pid | grep mysql /usr/share/man/man1/mysql_waitpid.1.gz /usr/bin/mysql_waitpid I have no idea why a pid file would be missing! any ideas Syd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
cannot find my.cnf file
Hello, I want to log all sql queries made against a mysql db. Googled and found I should add a line to my.cnf. However I cannot find a my.cnf file [r...@radium init.d]# ps -ef | grep mysql root 13614 1 0 Sep24 ?00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/object01.pid mysql13669 13614 0 Sep24 ?00:21:40 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/object01.err --pid-file=/var/lib/mysql/object01.pid root 23050 22746 0 19:05 pts/000:00:00 grep mysql [r...@radium init.d]# locate cnf /usr/share/doc/MySQL-server-community-5.1.39/my-huge.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-innodb-heavy-4G.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-large.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-medium.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-small.cnf /usr/share/man/man8/cnfsheadconf.8.gz /usr/share/man/man8/cnfsstat.8.gz /usr/share/ssl/openssl.cnf /usr/share/mysql/my-large.cnf /usr/share/mysql/my-huge.cnf /usr/share/mysql/my-innodb-heavy-4G.cnf /usr/share/mysql/my-medium.cnf /usr/share/mysql/my-small.cnf Any ideas? I might add i did not install mysql and I did not start it and the guy who did is in holiday! TIA Syd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cannot find my.cnf file
should be in /etc/my.cnf or try the following at the command line locate my.cnf That should give you the location On Thu, 2009-11-12 at 18:10 +, Sydney Puente wrote: Hello, I want to log all sql queries made against a mysql db. Googled and found I should add a line to my.cnf. However I cannot find a my.cnf file [r...@radium init.d]# ps -ef | grep mysql root 13614 1 0 Sep24 ?00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/object01.pid mysql13669 13614 0 Sep24 ?00:21:40 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/object01.err --pid-file=/var/lib/mysql/object01.pid root 23050 22746 0 19:05 pts/000:00:00 grep mysql [r...@radium init.d]# locate cnf /usr/share/doc/MySQL-server-community-5.1.39/my-huge.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-innodb-heavy-4G.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-large.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-medium.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-small.cnf /usr/share/man/man8/cnfsheadconf.8.gz /usr/share/man/man8/cnfsstat.8.gz /usr/share/ssl/openssl.cnf /usr/share/mysql/my-large.cnf /usr/share/mysql/my-huge.cnf /usr/share/mysql/my-innodb-heavy-4G.cnf /usr/share/mysql/my-medium.cnf /usr/share/mysql/my-small.cnf Any ideas? I might add i did not install mysql and I did not start it and the guy who did is in holiday! TIA Syd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: cannot find my.cnf file
Also note that mysql doesn't need a my.cnf file and will happily run with default values. It's possible that there is none and you'll have to create it. To see where your mysqld is configured to check for the config file do: mysql --verbose --help | grep -C3 my.cnf This will give you a list of paths it checks in order. Regards, Gavin Towey -Original Message- From: John Daisley [mailto:john.dais...@butterflysystems.co.uk] Sent: Thursday, November 12, 2009 10:30 AM To: Sydney Puente Cc: mysql@lists.mysql.com Subject: Re: cannot find my.cnf file should be in /etc/my.cnf or try the following at the command line locate my.cnf That should give you the location On Thu, 2009-11-12 at 18:10 +, Sydney Puente wrote: Hello, I want to log all sql queries made against a mysql db. Googled and found I should add a line to my.cnf. However I cannot find a my.cnf file [r...@radium init.d]# ps -ef | grep mysql root 13614 1 0 Sep24 ?00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/object01.pid mysql13669 13614 0 Sep24 ?00:21:40 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/object01.err --pid-file=/var/lib/mysql/object01.pid root 23050 22746 0 19:05 pts/000:00:00 grep mysql [r...@radium init.d]# locate cnf /usr/share/doc/MySQL-server-community-5.1.39/my-huge.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-innodb-heavy-4G.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-large.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-medium.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-small.cnf /usr/share/man/man8/cnfsheadconf.8.gz /usr/share/man/man8/cnfsstat.8.gz /usr/share/ssl/openssl.cnf /usr/share/mysql/my-large.cnf /usr/share/mysql/my-huge.cnf /usr/share/mysql/my-innodb-heavy-4G.cnf /usr/share/mysql/my-medium.cnf /usr/share/mysql/my-small.cnf Any ideas? I might add i did not install mysql and I did not start it and the guy who did is in holiday! TIA Syd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cannot find my.cnf file
You must copy /usr/share/doc/MySQL-server-community-5.1.39/my-somefile.cnf file into /etc/my.cnf If no .cnf file in /etc MySQL use default config. if server has 2G RAM then use my-huge.cnf Good luck. - Original Message - From: Sydney Puente sydneypue...@yahoo.com To: mysql@lists.mysql.com Sent: Friday, November 13, 2009 3:10 AM Subject: cannot find my.cnf file Hello, I want to log all sql queries made against a mysql db. Googled and found I should add a line to my.cnf. However I cannot find a my.cnf file [r...@radium init.d]# ps -ef | grep mysql root 13614 1 0 Sep24 ?00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/object01.pid mysql13669 13614 0 Sep24 ?00:21:40 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/object01.err --pid-file=/var/lib/mysql/object01.pid root 23050 22746 0 19:05 pts/000:00:00 grep mysql [r...@radium init.d]# locate cnf /usr/share/doc/MySQL-server-community-5.1.39/my-huge.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-innodb-heavy-4G.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-large.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-medium.cnf /usr/share/doc/MySQL-server-community-5.1.39/my-small.cnf /usr/share/man/man8/cnfsheadconf.8.gz /usr/share/man/man8/cnfsstat.8.gz /usr/share/ssl/openssl.cnf /usr/share/mysql/my-large.cnf /usr/share/mysql/my-huge.cnf /usr/share/mysql/my-innodb-heavy-4G.cnf /usr/share/mysql/my-medium.cnf /usr/share/mysql/my-small.cnf Any ideas? I might add i did not install mysql and I did not start it and the guy who did is in holiday! TIA Syd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=old...@posbank.co.kr
RE: Optimizing my.cnf
If it's a dedicated MySQL server I would increase the key buffer to at least half the available main memory and leave the rest for filesystem cache. You'll probably get the biggest performance increase this way. Cheers, A -Original Message- From: sangprabv [mailto:sangpr...@gmail.com] Sent: 06 October 2009 04:57 To: Rob Wultsch Cc: mysql@lists.mysql.com Subject: Re: Optimizing my.cnf As you see on my my.cnf I skip innodb and federated. So I just use myisam in this case. TIA. Willy On Mon, 2009-10-05 at 20:47 -0700, Rob Wultsch wrote: On Mon, Oct 5, 2009 at 6:12 PM, sangprabv sangpr...@gmail.com wrote: I have Dell PE2950iii with 16GB of RAM, and 1 Quadcore processor @2.00G. Installed with MySQL 5.075 on 64bit Ubuntu Jaunty. I have these parameters in my.cnf: blah blah blah... This heavily depends on workload. Are you using innodb? etc... -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.braithwa...@lovefilm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Optimizing my.cnf
I have Dell PE2950iii with 16GB of RAM, and 1 Quadcore processor @2.00G. Installed with MySQL 5.075 on 64bit Ubuntu Jaunty. I have these parameters in my.cnf: [mysqld] key_buffer = 512M max_allowed_packet = 512M thread_stack= 4096K thread_cache_size = 256 myisam-recover = BACKUP max_connections= 999 table_cache= 2048 thread_concurrency = 100 query_cache_limit = 32M query_cache_size= 512M expire_logs_days= 10 max_binlog_size = 100M skip-innodb skip-federated [mysqldump] quick quote-names max_allowed_packet = 512M [isamchk] key_buffer = 512M Is it optimized enough for a high load MySQL server machine? Is there any suggestion to get more speed and response? TIA Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Optimizing my.cnf
On Mon, Oct 5, 2009 at 6:12 PM, sangprabv sangpr...@gmail.com wrote: I have Dell PE2950iii with 16GB of RAM, and 1 Quadcore processor @2.00G. Installed with MySQL 5.075 on 64bit Ubuntu Jaunty. I have these parameters in my.cnf: blah blah blah... This heavily depends on workload. Are you using innodb? etc... -- Rob Wultsch wult...@gmail.com
Re: Optimizing my.cnf
As you see on my my.cnf I skip innodb and federated. So I just use myisam in this case. TIA. Willy On Mon, 2009-10-05 at 20:47 -0700, Rob Wultsch wrote: On Mon, Oct 5, 2009 at 6:12 PM, sangprabv sangpr...@gmail.com wrote: I have Dell PE2950iii with 16GB of RAM, and 1 Quadcore processor @2.00G. Installed with MySQL 5.075 on 64bit Ubuntu Jaunty. I have these parameters in my.cnf: blah blah blah... This heavily depends on workload. Are you using innodb? etc... -- Rob Wultsch wult...@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
optimize my.cnf
Hi, can you please send me some optimization examples for my.cnf ? I use mysql 5.1.37 The server run just 2 very busy forums. It is quad core cpu and 8 giga ram so we have lot of run (run debian) Can you please give me some examples? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: optimize my.cnf
Hi Christos, Performance optimization is very subjective, and if you are experiencing low performance it can be a million different things. The description you provided didn't mention any of your current my.cnf settings, nor did it mention what kind of tables your database contains, size of data, types of indexes, engines used by your tables etc. You mention you have a quad core machine running 8G ram how much of that ram is being used by MySQL and how much by other applications? how much of it is free? What kind of CPU usage are you experiencing, what is the size of your db, are you logging slow queries and checking for missing indexes? My.cnf is not a magic box which improves performance by setting a couple of variables and normally the performance improvements are minimal as compared to other things like: 1: faster disks 2: normalized and correctly designed db (not in your power I guess) 3: good code in your application (not in your power either) I'd suggest you read High Performance MySQL 2nd ed which will definitely be of help. Sorry for bombarding you with questions but performance tuning is not something you do by setting a couple of params on my.cnf! Gluck Darren www.mysqlpreacher.com - yet another blog from a mysql dba www.securich.com - a mysql security plugin On Thu, Sep 3, 2009 at 10:06 AM, Christos Pelekis chris...@blueice.orgwrote: Hi, can you please send me some optimization examples for my.cnf ? I use mysql 5.1.37 The server run just 2 very busy forums. It is quad core cpu and 8 giga ram so we have lot of run (run debian) Can you please give me some examples? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=i...@mysqlpreacher.com
Re: optimize my.cnf
Well said Darren. Its not magic :) Christos, you can download and run mysqlreport from http://hackmysql.com/mysqlreportguide it will give you more idea about DB serve performance. On Thu, Sep 3, 2009 at 3:07 PM, Darren Cassar i...@mysqlpreacher.comwrote: Hi Christos, Performance optimization is very subjective, and if you are experiencing low performance it can be a million different things. The description you provided didn't mention any of your current my.cnf settings, nor did it mention what kind of tables your database contains, size of data, types of indexes, engines used by your tables etc. You mention you have a quad core machine running 8G ram how much of that ram is being used by MySQL and how much by other applications? how much of it is free? What kind of CPU usage are you experiencing, what is the size of your db, are you logging slow queries and checking for missing indexes? My.cnf is not a magic box which improves performance by setting a couple of variables and normally the performance improvements are minimal as compared to other things like: 1: faster disks 2: normalized and correctly designed db (not in your power I guess) 3: good code in your application (not in your power either) I'd suggest you read High Performance MySQL 2nd ed which will definitely be of help. Sorry for bombarding you with questions but performance tuning is not something you do by setting a couple of params on my.cnf! Gluck Darren www.mysqlpreacher.com - yet another blog from a mysql dba www.securich.com - a mysql security plugin On Thu, Sep 3, 2009 at 10:06 AM, Christos Pelekis chris...@blueice.org wrote: Hi, can you please send me some optimization examples for my.cnf ? I use mysql 5.1.37 The server run just 2 very busy forums. It is quad core cpu and 8 giga ram so we have lot of run (run debian) Can you please give me some examples? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=i...@mysqlpreacher.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
RE: optimize my.cnf
-Original Message- From: prabhat kumar [mailto:aim.prab...@gmail.com] Sent: Thursday, September 03, 2009 1:44 PM To: Darren Cassar Cc: Christos Pelekis; mysql@lists.mysql.com Subject: Re: optimize my.cnf Well said Darren. Its not magic :) [JS] Actually, its voodoo :) Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Christos, you can download and run mysqlreport from http://hackmysql.com/mysqlreportguide it will give you more idea about DB serve performance. On Thu, Sep 3, 2009 at 3:07 PM, Darren Cassar i...@mysqlpreacher.comwrote: Hi Christos, Performance optimization is very subjective, and if you are experiencing low performance it can be a million different things. The description you provided didn't mention any of your current my.cnf settings, nor did it mention what kind of tables your database contains, size of data, types of indexes, engines used by your tables etc. You mention you have a quad core machine running 8G ram how much of that ram is being used by MySQL and how much by other applications? how much of it is free? What kind of CPU usage are you experiencing, what is the size of your db, are you logging slow queries and checking for missing indexes? My.cnf is not a magic box which improves performance by setting a couple of variables and normally the performance improvements are minimal as compared to other things like: 1: faster disks 2: normalized and correctly designed db (not in your power I guess) 3: good code in your application (not in your power either) I'd suggest you read High Performance MySQL 2nd ed which will definitely be of help. Sorry for bombarding you with questions but performance tuning is not something you do by setting a couple of params on my.cnf! Gluck Darren www.mysqlpreacher.com - yet another blog from a mysql dba www.securich.com - a mysql security plugin On Thu, Sep 3, 2009 at 10:06 AM, Christos Pelekis chris...@blueice.org wrote: Hi, can you please send me some optimization examples for my.cnf ? I use mysql 5.1.37 The server run just 2 very busy forums. It is quad core cpu and 8 giga ram so we have lot of run (run debian) Can you please give me some examples? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=i...@mysqlpreacher.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: my.cnf file
michel wrote: I set up mysql and can't start it because I need to hard code the IP address parameter (bind-address) into my.cnf ... but I have three of them in different sub directories of /mysql/mysql-test/suite Should there not be one basic one? http://dev.mysql.com/doc/refman/5.1/en/option-files.html It searches in order of locations, /etc/my.cnf being the first. -- Linux web infrastructure consulting, cr...@codenation.net Free live poker tournament listings, http://www.g5poker.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Default my.cnf for (very) high performance servers....
Hi All, We're setting up a group of servers using MySQL Enterprise 5.1 - Rather than starting with a blank canvas I wondered if there was a suitable my.cnf that is tuned to the kind of environment I'm running where I can tweak it from there. We're running on RHEL, on Sunfire X4140's - 8 disks, 16G RAM, 2 x dual core 3000mhz 64bit... which is reasonably beefy. Environment is more read than write, but write speed is important. Anyone know where I can look? Cheers Craig -- Linux web infrastructure consulting, cr...@codenation.net Free live poker tournament listings, http://www.g5poker.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: Default my.cnf for (very) high performance servers....
Craig Dunn wrote: Hi All, We're setting up a group of servers using MySQL Enterprise 5.1 - Rather than starting with a blank canvas I wondered if there was a suitable my.cnf that is tuned to the kind of environment I'm running where I can tweak it from there. We're running on RHEL, on Sunfire X4140's - 8 disks, 16G RAM, 2 x dual core 3000mhz 64bit... which is reasonably beefy. Environment is more read than write, but write speed is important. Anyone know where I can look? Cheers Craig I should add, I wanted something a bit more up to date than my-huge.cnf, which seems to think a huge server is a system with memory of 1G-2G -- Linux web infrastructure consulting, cr...@codenation.net Free live poker tournament listings, http://www.g5poker.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: Default my.cnf for (very) high performance servers....
There's no such thing as a generic my.cnf for high performance MySQL servers, you will need to provide more information.. Some questions: Are you going to run InnoDB or MyISAM or both (if both, what's the split?) Is there anything else running on that server? i.e. how much of the 16GB is available for MySQL to use? Can you partition your disks as you wish? (How much data do you need host?) Will this server be a master or slave or standalone? (Do we need to deal with binlogs here?) Andrew -Original Message- From: Craig Dunn [mailto:li...@codenation.net] Sent: 06 May 2009 14:02 To: mysql@lists.mysql.com Subject: Re: Default my.cnf for (very) high performance servers Craig Dunn wrote: Hi All, We're setting up a group of servers using MySQL Enterprise 5.1 - Rather than starting with a blank canvas I wondered if there was a suitable my.cnf that is tuned to the kind of environment I'm running where I can tweak it from there. We're running on RHEL, on Sunfire X4140's - 8 disks, 16G RAM, 2 x dual core 3000mhz 64bit... which is reasonably beefy. Environment is more read than write, but write speed is important. Anyone know where I can look? Cheers Craig I should add, I wanted something a bit more up to date than my-huge.cnf, which seems to think a huge server is a system with memory of 1G-2G -- Linux web infrastructure consulting, cr...@codenation.net Free live poker tournament listings, http://www.g5poker.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.braithwa...@lovefilm.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: Default my.cnf for (very) high performance servers....
Andrew Braithwaite wrote: There's no such thing as a generic my.cnf for high performance MySQL servers, you will need to provide more information.. Well, I was more after something a bit more up to date than my-huge.cnf that I could use as a starting point, I see a few example ones posted to Mysql Forge, but they are very innodb orientated. Some questions: Are you going to run InnoDB or MyISAM or both (if both, what's the split?) Both, 90% MyISAM Is there anything else running on that server? i.e. how much of the 16GB is available for MySQL to use? It's a dedicated MySQL box Can you partition your disks as you wish? (How much data do you need host?) About 50G of databases - I've currently got 6 disks with RAID 10 running soley /var/lib/mysql (datadir) on an LVM with the binlogs being written to the other 2 disks (which has the OS on them too) Will this server be a master or slave or standalone? (Do we need to deal with binlogs here?) There are 3 in total, 1 master and 2 slaves (one of which is capable of being failed over to as a master) The current MySQL 4.1 servers that they are replacing have at any one time on average about 1000 open tables, about double the number of selects than inserts, between 2000 and 5000 qps - if thats any use. Cheers Craig -- Linux web infrastructure consulting, cr...@codenation.net Free live poker tournament listings, http://www.g5poker.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: Default my.cnf for (very) high performance servers....
Your disk config is good and you'll need all the nessesary my.cnf entries to point all the logs and data to the correct place. Slaves should have the relay-logs going to the OS disk too. I assume you've set up the master slave config in the my.cnf too. Here's my brain dump on what you need: skip-locking max_allowed_packet = 16M key_buffer_size = 9000M max_allowed_packet = 16M table_cache = 1024 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 1000 query_cache_size = 256M # Nice big key_buffer_size - the most important one for read-heavy MyISAM DBs # query cache nice and high too - if your tables change a lot you may want to turn this off as it will be ineffective tmpdir = /somedir/tmp/ #You may want to point this somewhere else if you are writing a lot of tmp tables to disk innodb_data_home_dir = /somedir/mysql/ innodb_data_file_path = ibdata1:512M:autoextend innodb_log_group_home_dir = /somedir/mysql/ innodb_log_arch_dir = /somedir/mysql/ innodb_buffer_pool_size=1000M set-variable = innodb_additional_mem_pool_size=128M innodb_log_file_size=200M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 innodb_thread_concurrency = 8 innodb_file_per_table # Keep a GB of InnoDB in memory as you're not using that much # use the innodb_file_per_table param for easier management of disk space The most important part is your caches. You can keep an eye on your MyISAM key cache efficiency by running 'SHOW STATUS' and 'SHOW VARIABLES' and calculating the following: Cache hit ratio: 100 - ((Key_reads * 100) / Key_read_requests) Percentage of buffer in use: 100 - ((Key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size) And tweak them as you need. Of course you have to remember that these caches (and the filesystem cache) will take a while to warm up before they become super-efficient. Hope this helps, ANdrew -Original Message- From: Craig Dunn [mailto:li...@codenation.net] Sent: 06 May 2009 14:31 To: Andrew Braithwaite Cc: mysql@lists.mysql.com Subject: Re: Default my.cnf for (very) high performance servers Andrew Braithwaite wrote: There's no such thing as a generic my.cnf for high performance MySQL servers, you will need to provide more information.. Well, I was more after something a bit more up to date than my-huge.cnf that I could use as a starting point, I see a few example ones posted to Mysql Forge, but they are very innodb orientated. Some questions: Are you going to run InnoDB or MyISAM or both (if both, what's the split?) Both, 90% MyISAM Is there anything else running on that server? i.e. how much of the 16GB is available for MySQL to use? It's a dedicated MySQL box Can you partition your disks as you wish? (How much data do you need host?) About 50G of databases - I've currently got 6 disks with RAID 10 running soley /var/lib/mysql (datadir) on an LVM with the binlogs being written to the other 2 disks (which has the OS on them too) Will this server be a master or slave or standalone? (Do we need to deal with binlogs here?) There are 3 in total, 1 master and 2 slaves (one of which is capable of being failed over to as a master) The current MySQL 4.1 servers that they are replacing have at any one time on average about 1000 open tables, about double the number of selects than inserts, between 2000 and 5000 qps - if thats any use. Cheers Craig -- Linux web infrastructure consulting, cr...@codenation.net Free live poker tournament listings, http://www.g5poker.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: Default my.cnf for (very) high performance servers....
Andrew Braithwaite wrote: Your disk config is good and you'll need all the nessesary my.cnf entries to point all the logs and data to the correct place. Slaves should have the relay-logs going to the OS disk too. I assume you've set up the master slave config in the my.cnf too. Yeah the replication and file location stuff is fine, I was after a rough idea of buffer sizes...etc, thanks for all that, most helpful.. Cheers Craig -- Linux web infrastructure consulting, cr...@codenation.net Free live poker tournament listings, http://www.g5poker.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: Default my.cnf for (very) high performance servers....
At 07:56 AM 5/6/2009, you wrote: Hi All, We're setting up a group of servers using MySQL Enterprise 5.1 - Rather than starting with a blank canvas I wondered if there was a suitable my.cnf that is tuned to the kind of environment I'm running where I can tweak it from there. We're running on RHEL, on Sunfire X4140's - 8 disks, 16G RAM, 2 x dual core 3000mhz 64bit... which is reasonably beefy. Environment is more read than write, but write speed is important. Anyone know where I can look? Cheers Craig Take a look at High Performance MySQL 2nd Edition Chapter 6 - starting around page 265. Online: http://books.google.ca/books?id=BL0NNoFPuAQCprintsec=frontcoverdq=high+performance+mysql+baron+schwartz#PPA288,M1 Buy from: http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/0596101716/ref=sr_1_1?ie=UTF8s=booksqid=1241622110sr=8-1 Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
my.cnf file
I set up mysql and can't start it because I need to hard code the IP address parameter (bind-address) into my.cnf ... but I have three of them in different sub directories of /mysql/mysql-test/suite Should there not be one basic one?
Re: my.cnf optimization
Here's all the buffer variables: mysql show variables like '%buffer%'\G *** 1. row *** Variable_name: bulk_insert_buffer_size Value: 8388608 *** 2. row *** Variable_name: innodb_buffer_pool_awe_mem_mb Value: 0 *** 3. row *** Variable_name: innodb_buffer_pool_size Value: 8388608 *** 4. row *** Variable_name: innodb_log_buffer_size Value: 1048576 *** 5. row *** Variable_name: join_buffer_size Value: 131072 *** 6. row *** Variable_name: key_buffer_size Value: 402653184 *** 7. row *** Variable_name: myisam_sort_buffer_size Value: 67108864 *** 8. row *** Variable_name: net_buffer_length Value: 16384 *** 9. row *** Variable_name: preload_buffer_size Value: 32768 *** 10. row *** Variable_name: read_buffer_size Value: 67104768 *** 11. row *** Variable_name: read_rnd_buffer_size Value: 67104768 *** 12. row *** Variable_name: sort_buffer_size Value: 67108856 12 rows in set (0.00 sec) I'll bump innodb_buffer_pool_size to 2G and see how that goes. Thanks for the tips, if there's additional innodb tuning parameters folks tend to hit first I'd be glad to try them as well. -- Ryan Schwartz On Sep 4, 2008, at 8:16 AM, Johnny Withers wrote: If you do have a fair about of innodb tables you can increase performance by increasing the size of innodb_buffer_pool_size. According to your status output, you are currently using the entire buffer pool: *** 137. row *** Variable_name: Innodb_buffer_pool_pages_free Value: 0 It seems to be set small anyway: Variable_name: Innodb_buffer_pool_pages_data Value: 501 It also seems that you do have alot of innodb data: *** 151. row *** Variable_name: Innodb_data_read Value: 27743085907968 Again, i don't know what you have innodb_buffer_pool_size set to, but you have plenty of RAM, I'd set it to about 4.5GB and see if that helps. I also don't know mucha bout OS X and your hardware.. is it 64bit? If it is not 64bit, you probably can't use 4.5GB as the size of your buffer pool. -johnny On 9/3/08, Ryan Schwartz [EMAIL PROTECTED] wrote: All, We're seeing a huge surge in our qps and I'd like to make sure we're tuned as well as we can be. I'm wondering if I've got some variables maybe set too large (is that even possible?) ? We do have a fair bit of innodb, so perhaps I should add some non-defaults there, but I'm not so sure where to start with that. Hardware is an Apple Xserve, 2x Quad-Core Intel @ 3Ghz, 32GB RAM, 3x 280 GB SAS drives in Raid-5 config, OS is Mac OS X 10.5.4 and here's my my.cnf: [billie:~] admin$ egrep -v '^$|^#' /etc/my.cnf [client] port= 3306 socket = /var/mysql/mysql.sock [mysqld] port= 3306 socket = /var/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 50M table_cache = 2048 sort_buffer_size = 64M read_buffer_size = 64M read_rnd_buffer_size = 64M myisam_sort_buffer_size = 64M thread_cache_size = 100 query_cache_size = 64M thread_concurrency = 16 skip-thread-priority max_connections = 750 old-passwords innodb_file_per_table innodb_flush_log_at_trx_commit=1 sync_binlog=1 log-slow-queries long_query_time=2 log_queries_not_using_indexes log-bin=mysql-bin server-id = 4 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout SHOW STATUS\G output follows my sig below... My devs are adding indexes where the slow query log is pointing them, but any suggestions on how better to tune things up would be much appreciated. I'm not sure what else to tune here but we're getting bursts of 1200+ queries per second regularly and seeing things slow down significantly. Best, -- Ryan Schwartz mysql SHOW STATUS\G *** 1. row *** Variable_name: Aborted_clients Value: 1656 *** 2. row *** Variable_name: Aborted_connects Value: 3 *** 3. row *** Variable_name: Binlog_cache_disk_use Value: 276 *** 4. row
Re: my.cnf optimization
On Thu, Sep 4, 2008 at 12:15 AM, Ryan Schwartz [EMAIL PROTECTED] wrote: We're seeing a huge surge in our qps and I'd like to make sure we're tuned as well as we can be. I'm wondering if I've got some variables maybe set too large (is that even possible?) ? We do have a fair bit of innodb, so perhaps I should add some non-defaults there, but I'm not so sure where to start with that. It's not really possible to give good tuning advice without knowing about how you use the database and how your machine is currently responding. However, you can get some good started advice from the sample my.cnf files that come with MySQL and you can get a copy of the High Performance MySQL book for a good primer on what to look for. You can also find conference presentations by Peter Zaitsev that summarize some of the advice in the book. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf optimization
Ryan Schwartz wrote mysql show variables like '%buffer%'\G *** 1. row *** *** 3. row *** Variable_name: innodb_buffer_pool_size Value: 8388608 *** 4. row *** Variable_name: innodb_log_buffer_size Value: 1048576 I'll bump innodb_buffer_pool_size to 2G and see how that goes. Thanks for the tips, if there's additional innodb tuning parameters folks tend to hit first I'd be glad to try them as well. -- Ryan Schwartz Hi ryan. As pointed by Johnny, it is difficult to give optimization advise without exactly knowing the performance of your machine. I'm assuming you are using the machine as Database Server and not running application (Web/other) on the same. (And you are using InnoDB as engine) I would suggest keeping innodb_buffer_pool_size pretty high (+20G) Please read up here : http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/ Also if possible get a copy of High performance MySQL and go through it as it covers many good techniques for high performance MySQL setup. Some of the default InnoDB settings are horribly wrong from high performance point of view. Can you post your complete my.cnf on pastebin or somewhere ? Regards, Ranjeet Walunj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf optimization
On Sep 4, 2008, at 1:48 PM, Ranjeet Walunj wrote: Hi ryan. As pointed by Johnny, it is difficult to give optimization advise without exactly knowing the performance of your machine. I'm assuming you are using the machine as Database Server and not running application (Web/other) on the same. (And you are using InnoDB as engine) I would suggest keeping innodb_buffer_pool_size pretty high (+20G) This is a dedicated MySQL server - nothing else running on it at all, so all that RAM is up for grabs. Mysqld is running in 64 bits, and after bumping innodb_buffer_pool_size to 4G our performance concerns are completely gone - I'll ramp that up after doing a bit more research on InnoDB tuning. Please read up here : http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/ Also if possible get a copy of High performance MySQL and go through it as it covers many good techniques for high performance MySQL setup. I'll have to crack open my copy - haven't read through it in a while, and quite honestly I had forgot to make any adjustments on the InnoDB side of things because when I inherited the old MySQL server we were on the devs were mostly using MyISAM tables. Some of the default InnoDB settings are horribly wrong from high performance point of view. Can you post your complete my.cnf on pastebin or somewhere ? http://pastebin.com/m2ebec4f6 includes everything in my.cnf but comments and blank lines, SHOW STATUS\G, SHOW INNODB STATUS\G, AND SHOW VARIABLES\G All your help is much appreciated - I just wonder if there's not been a simple script set up by someone to autogen my.cnf based on system variables like available RAM, etc? Surely there's some general recommendations depending on those specific system things, rather than just copy my-huge.cnf and modify... -- Ryan Schwartz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf optimization
On Thu, Sep 4, 2008 at 3:23 PM, Ryan Schwartz [EMAIL PROTECTED] wrote: I'll have to crack open my copy - haven't read through it in a while If you have the first edition, I recommend getting the newer one. It has a lot more tuning info. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf optimization
All, We're seeing a huge surge in our qps and I'd like to make sure we're tuned as well as we can be. I'm wondering if I've got some variables maybe set too large (is that even possible?) ? We do have a fair bit of innodb, so perhaps I should add some non-defaults there, but I'm not so sure where to start with that. Hardware is an Apple Xserve, 2x Quad-Core Intel @ 3Ghz, 32GB RAM, 3x 280 GB SAS drives in Raid-5 config, OS is Mac OS X 10.5.4 and here's my my.cnf: [billie:~] admin$ egrep -v '^$|^#' /etc/my.cnf [client] port= 3306 socket = /var/mysql/mysql.sock [mysqld] port= 3306 socket = /var/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 50M table_cache = 2048 sort_buffer_size = 64M read_buffer_size = 64M read_rnd_buffer_size = 64M myisam_sort_buffer_size = 64M thread_cache_size = 100 query_cache_size = 64M thread_concurrency = 16 skip-thread-priority max_connections = 750 old-passwords innodb_file_per_table innodb_flush_log_at_trx_commit=1 sync_binlog=1 log-slow-queries long_query_time=2 log_queries_not_using_indexes log-bin=mysql-bin server-id = 4 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout SHOW STATUS\G output follows my sig below... My devs are adding indexes where the slow query log is pointing them, but any suggestions on how better to tune things up would be much appreciated. I'm not sure what else to tune here but we're getting bursts of 1200+ queries per second regularly and seeing things slow down significantly. Best, -- Ryan Schwartz mysql SHOW STATUS\G *** 1. row *** Variable_name: Aborted_clients Value: 1656 *** 2. row *** Variable_name: Aborted_connects Value: 3 *** 3. row *** Variable_name: Binlog_cache_disk_use Value: 276 *** 4. row *** Variable_name: Binlog_cache_use Value: 6416113 *** 5. row *** Variable_name: Bytes_received Value: 134 *** 6. row *** Variable_name: Bytes_sent Value: 70104 *** 7. row *** Variable_name: Com_admin_commands Value: 0 *** 8. row *** Variable_name: Com_alter_db Value: 0 *** 9. row *** Variable_name: Com_alter_table Value: 0 *** 10. row *** Variable_name: Com_analyze Value: 0 *** 11. row *** Variable_name: Com_backup_table Value: 0 *** 12. row *** Variable_name: Com_begin Value: 0 *** 13. row *** Variable_name: Com_call_procedure Value: 0 *** 14. row *** Variable_name: Com_change_db Value: 0 *** 15. row *** Variable_name: Com_change_master Value: 0 *** 16. row *** Variable_name: Com_check Value: 0 *** 17. row *** Variable_name: Com_checksum Value: 0 *** 18. row *** Variable_name: Com_commit Value: 0 *** 19. row *** Variable_name: Com_create_db Value: 0 *** 20. row *** Variable_name: Com_create_function Value: 0 *** 21. row *** Variable_name: Com_create_index Value: 0 *** 22. row *** Variable_name: Com_create_table Value: 0 *** 23. row *** Variable_name: Com_create_user Value: 0 *** 24. row *** Variable_name: Com_dealloc_sql Value: 0 *** 25. row *** Variable_name: Com_delete Value: 0 *** 26. row *** Variable_name: Com_delete_multi Value: 0 *** 27. row *** Variable_name: Com_do Value: 0 *** 28. row *** Variable_name: Com_drop_db Value: 0 *** 29. row *** Variable_name
MySQL won't start with external bind-adress in my.cnf
Hey all, I'm trying to get my MySQL server configured so outside connections can access it with OpenOffice.org 2.4. My server is running on FreeBSD 6.0 and the my.cnf file looks like this currently. [mysqld] user = mysql pid-file = /usr/local/mysql/bigskypenguin.com.pid socket = /tmp/mysql.sock port = 3306 basedir = /usr datadir = /usr/local/mysql tmpdir = /tmp language = /usr/local/share/mysql/english/ bind-address = 192.168.xxx.xxx log = /var/log/mysql.log # skip-networking My server has two nics, one for internal access at the 192.168.xxx.xxx address, and another for external access at 76.343.xxx.xxx It is this second IP, the external one, I would like to implement so that the database server is accessible externally to people through OpenOffice.org 2.4. To accomplish this I changed the file to: [mysqld] user = mysql pid-file = /usr/local/mysql/bigskypenguin.com.pid socket = /tmp/mysql.sock port = 3306 basedir = /usr datadir = /usr/local/mysql tmpdir = /tmp language = /usr/local/share/mysql/english/ bind-address = 76.343.xxx.xxx log = /var/log/mysql.log # skip-networking But configured in this way, the MySQL server will not restart. I attempt a /usr/local/etc/rc.d/mysql-server.sh restart But the server does not start until I change the address back to the LAN address. Any help would be appreciated. I have not found via Google anyone else having this issue. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL won't start with external bind-adress in my.cnf
Hey Chaim, I'm sorry, I don't know what you mean by default route. I am only familiar with the current contents of my.cnf, and mods were made according to instructions I found via Google. I don't see this noted in MySQL anywhere. Is this a network-wide configuration? Skip Chaim Rieger wrote: Skip Evans wrote: Hey all, /usr/local/etc/rc.d/mysql-server.sh restart But the server does not start until I change the address back to the LAN address. Any help would be appreciated. I have not found via Google anyone else having this issue. whats your default route ? -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL won't start with external bind-adress in my.cnf
Skip Evans wrote: Hey all, I'm trying to get my MySQL server configured so outside connections can access it with OpenOffice.org 2.4. My server is running on FreeBSD 6.0 and the my.cnf file looks like this currently. [mysqld] user = mysql pid-file = /usr/local/mysql/bigskypenguin.com.pid socket = /tmp/mysql.sock port = 3306 basedir = /usr datadir = /usr/local/mysql tmpdir = /tmp language = /usr/local/share/mysql/english/ bind-address = 192.168.xxx.xxx log = /var/log/mysql.log # skip-networking My server has two nics, one for internal access at the 192.168.xxx.xxx address, and another for external access at 76.343.xxx.xxx It is this second IP, the external one, I would like to implement so that the database server is accessible externally to people through OpenOffice.org 2.4. To accomplish this I changed the file to: [mysqld] user = mysql pid-file = /usr/local/mysql/bigskypenguin.com.pid socket = /tmp/mysql.sock port = 3306 basedir = /usr datadir = /usr/local/mysql tmpdir = /tmp language = /usr/local/share/mysql/english/ bind-address = 76.343.xxx.xxx log = /var/log/mysql.log # skip-networking But configured in this way, the MySQL server will not restart. I attempt a /usr/local/etc/rc.d/mysql-server.sh restart But the server does not start until I change the address back to the LAN address. Any help would be appreciated. I have not found via Google anyone else having this issue. Does that IP address resolve to your hostname? -- Gerald L. Clark Sr. V.P. Development Supplier Systems Corporation Unix since 1982 Linux since 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL won't start with external bind-adress in my.cnf
Skip Evans wrote: Hey Chaim, I'm sorry, I don't know what you mean by default route. I am only familiar with the current contents of my.cnf, and mods were made according to instructions I found via Google. I don't see this noted in MySQL anywhere. Is this a network-wide configuration? can you post the output of ifconfig please thanx -- -- Chaim Rieger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL won't start with external bind-adress in my.cnf
Gerald L. Clark wrote: Does that IP address resolve to your hostname? Yes, it does. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL won't start with external bind-adress in my.cnf
I have 5.1 a VCS mysql cluster and I set the bind address for the virtual ip the nodes share in the mysql script; didn't look up 6.0 changes yet: $bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file --bind-address=17x.20.999.999 --local_infile=0 $other_args /dev/null 21 -Original Message- From: Skip Evans [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 29, 2008 10:38 AM To: mysql@lists.mysql.com Subject: MySQL won't start with external bind-adress in my.cnf Hey all, I'm trying to get my MySQL server configured so outside connections can access it with OpenOffice.org 2.4. My server is running on FreeBSD 6.0 and the my.cnf file looks like this currently. [mysqld] user = mysql pid-file = /usr/local/mysql/bigskypenguin.com.pid socket = /tmp/mysql.sock port = 3306 basedir = /usr datadir = /usr/local/mysql tmpdir = /tmp language = /usr/local/share/mysql/english/ bind-address = 192.168.xxx.xxx log = /var/log/mysql.log # skip-networking My server has two nics, one for internal access at the 192.168.xxx.xxx address, and another for external access at 76.343.xxx.xxx It is this second IP, the external one, I would like to implement so that the database server is accessible externally to people through OpenOffice.org 2.4. To accomplish this I changed the file to: [mysqld] user = mysql pid-file = /usr/local/mysql/bigskypenguin.com.pid socket = /tmp/mysql.sock port = 3306 basedir = /usr datadir = /usr/local/mysql tmpdir = /tmp language = /usr/local/share/mysql/english/ bind-address = 76.343.xxx.xxx log = /var/log/mysql.log # skip-networking But configured in this way, the MySQL server will not restart. I attempt a /usr/local/etc/rc.d/mysql-server.sh restart But the server does not start until I change the address back to the LAN address. Any help would be appreciated. I have not found via Google anyone else having this issue. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ -- 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: MySQL won't start with external bind-adress in my.cnf
Chaim Rieger wrote: Skip Evans wrote: can you post the output of ifconfig please bge0: flags=8843UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST mtu 1500 options=1aTXCSUM,VLAN_MTU,VLAN_HWTAGGING inet6 fe80::230:48ff:fe88:2b8a%bge0 prefixlen 64 scopeid 0x1 inet 192.168.1.100 netmask 0xff00 broadcast 192.168.1.255 ether 00:30:48:88:2b:8a media: Ethernet autoselect (100baseTX full-duplex) status: active bge1: flags=8843UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST mtu 1500 options=1aTXCSUM,VLAN_MTU,VLAN_HWTAGGING inet6 fe80::230:48ff:fe88:2b8b%bge1 prefixlen 64 scopeid 0x2 inet 192.168.1.250 netmask 0xff00 broadcast 192.168.1.255 ether 00:30:48:88:2b:8b media: Ethernet autoselect (100baseTX full-duplex) status: active plip0: flags=108810POINTOPOINT,SIMPLEX,MULTICAST,NEEDSGIANT mtu 1500 lo0: flags=8049UP,LOOPBACK,RUNNING,MULTICAST mtu 16384 inet6 ::1 prefixlen 128 inet6 fe80::1%lo0 prefixlen 64 scopeid 0x4 inet 127.0.0.1 netmask 0xff00 -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL won't start with external bind-adress in my.cnf
Chaim Rieger wrote: can you post the output of ifconfig please I should have noted that 192.168.1.250 on bge1 is configured for port forwarding to the public IP address of the server. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL won't start with external bind-adress in my.cnf
Skip Evans wrote: Chaim Rieger wrote: Skip Evans wrote: can you post the output of ifconfig please bge0: flags=8843UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST mtu 1500 options=1aTXCSUM,VLAN_MTU,VLAN_HWTAGGING inet6 fe80::230:48ff:fe88:2b8a%bge0 prefixlen 64 scopeid 0x1 inet 192.168.1.100 netmask 0xff00 broadcast 192.168.1.255 bge1: flags=8843UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST mtu 1500 options=1aTXCSUM,VLAN_MTU,VLAN_HWTAGGING inet6 fe80::230:48ff:fe88:2b8b%bge1 prefixlen 64 scopeid 0x2 inet 192.168.1.250 netmask 0xff00 broadcast 192.168.1.255 ok where do you come up with the public addy then ? i only see private ips listed i would change it to allow networking on all interfaces. -- -- Chaim Rieger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL won't start with external bind-adress in my.cnf
Thanks Chaim, You just clued me in. First, when you said to post output of ifconfig, I realized my LAN is configured not directly to the public address, but to 192.168.1.250 which is then tied to the public IP through port forwarding. I'm not completely up on why this was done, but when the ATT tech helped me configure my LAN and public IPs through the Netopia DSL modem, we ended up doing that to make it work properly. I think it has something to do with a requirement in the Netopia modem. So next I changed the bind-address in my.cnf to the 192.168.1.250 mysql came up fine and all is well with internal access. Next I set up my latop to connect via ODBC through the public address, and with MySQL is bound to the 192.168.1.250 on the server side makes the proper connection. So, in short I have: 1) External connections to public address 2) MySQL on the server binding to 192.168.1.250 3) 192.168.1.250 port forwarding to public addres 4) External OpenOffice.org successfully connecting to the database. Thanks! You guys got me pointing the right direction and all is good now. Chaim Rieger wrote: Skip Evans wrote: Chaim Rieger wrote: Skip Evans wrote: can you post the output of ifconfig please bge0: flags=8843UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST mtu 1500 options=1aTXCSUM,VLAN_MTU,VLAN_HWTAGGING inet6 fe80::230:48ff:fe88:2b8a%bge0 prefixlen 64 scopeid 0x1 inet 192.168.1.100 netmask 0xff00 broadcast 192.168.1.255 bge1: flags=8843UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST mtu 1500 options=1aTXCSUM,VLAN_MTU,VLAN_HWTAGGING inet6 fe80::230:48ff:fe88:2b8b%bge1 prefixlen 64 scopeid 0x2 inet netmask 0xff00 broadcast 192.168.1.255 ok where do you come up with the public addy then ? i only see private ips listed i would change it to allow networking on all interfaces. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX DHTML development framework. http://phpenguin.bigskypenguin.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dismal performance on a 16G memory/8 core server - my.cnf settings?
Hello, We recently purchased a Dell PowerEdge 6650 thinking it would be a real fast server. Specs are: OS: Linux Debian 4.0/Etch RAID 5 on 4x U320 15k rpm drives (uses a perc-raid 3/DC hardware raid controller) 16GB of RAM 4 3.0 Ghz Xeon processors - I think they're dual core, in /proc/cpuinfo it shows up as 8 processors - maybe it's only HT I first made the mistake of using the default kernel, which provides SMP support but not large memory support. I have the output of a mysql sql-bench run from mysql on a Mac Mini to compare performance with. The server was only 0.35 (relative) the speed of the Mac mini - that means an 8 core 3.0 Ghz Xeon server with 16GB of RAM was only about 3x as fast as a as a single-core 1.25 Ghz G4 with 1GB of RAM (and a mini uses those little laptop hard drives, too). Needless to say my employer was shocked at the terrible performance and decided to sell the 6650 right away. But I can't help but wonder if there's not something terribly wrong with the settings - either the OS or mysql settings. I changed the kernel to the -bigmem kernel. It now sees all the RAM, but the sql-bench output on this try was _exactly_ the same: 0.35 I copied the my-huge.cnf from the examples directory and changed the thread_concurrency setting to 8 (because it said to set it to No. of CPUs*2). I also set the tmpdir, basedir, datadir and language, which were set in the original my.cnf I ran sql-bench again and the performance was even worse this time: 0.36 Someone suggested I try the -amd64 kernels which provide 64 bit but when I try to boot it I get various errors about this CPU does not support long (something) please use a 32-bit OS - the 64 bit install CD says the same message. So I assume these are not 64 bit CPUs. Any idea how I can configure this server to maximize performace? I think the multiple CPUs are a waste: I'm not looking for lots of concurrency, I want 1 query done really fast. Thanks. JW -- -- System Administrator - Cedar Creek Software http://www.cedarcreeksoftware.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dismal performance on a 16G memory/8 core server - my.cnf settings?
Hi, On Wed, Apr 23, 2008 at 11:07 PM, JW [EMAIL PROTECTED] wrote: Hello, We recently purchased a Dell PowerEdge 6650 thinking it would be a real fast server. Specs are: OS: Linux Debian 4.0/Etch RAID 5 on 4x U320 15k rpm drives (uses a perc-raid 3/DC hardware raid controller) 16GB of RAM 4 3.0 Ghz Xeon processors - I think they're dual core, in /proc/cpuinfo it shows up as 8 processors - maybe it's only HT I first made the mistake of using the default kernel, which provides SMP support but not large memory support. I have the output of a mysql sql-bench run from mysql on a Mac Mini to compare performance with. The server was only 0.35 (relative) the speed of the Mac mini - that means an 8 core 3.0 Ghz Xeon server with 16GB of RAM was only about 3x as fast as a as a single-core 1.25 Ghz G4 with 1GB of RAM (and a mini uses those little laptop hard drives, too). Needless to say my employer was shocked at the terrible performance and decided to sell the 6650 right away. But I can't help but wonder if there's not something terribly wrong with the settings - either the OS or mysql settings. I changed the kernel to the -bigmem kernel. It now sees all the RAM, but the sql-bench output on this try was _exactly_ the same: 0.35 I copied the my-huge.cnf from the examples directory and changed the thread_concurrency setting to 8 (because it said to set it to No. of CPUs*2). I also set the tmpdir, basedir, datadir and language, which were set in the original my.cnf I ran sql-bench again and the performance was even worse this time: 0.36 Someone suggested I try the -amd64 kernels which provide 64 bit but when I try to boot it I get various errors about this CPU does not support long (something) please use a 32-bit OS - the 64 bit install CD says the same message. So I assume these are not 64 bit CPUs. They almost certainly are. Look at the contents of /proc/cpuinfo. You are probably using a 32-bit OS. You can't use a lot of memory efficiently unless you install a 64-bit OS, regardless of whether it has big memory support. But that's an x86_64 OS, not an AMD64 OS. These are not the same architecture. Any idea how I can configure this server to maximize performace? I think the multiple CPUs are a waste: I'm not looking for lots of concurrency, I want 1 query done really fast. You will be bound by CPU performance on any given single query, yes. But properly tuned, you may get a lot more performance out of this machine. Have you tuned MySQL (key_buffer_size and/or innodb_buffer_pool_size) to use the added memory, for starters? How much data do you even have? If your data all fits in the mac mini's memory and it has a comparable CPU and bus, I wouldn't be surprised to see it keeping up with the Dell fairly well on this benchmark. More to the point: does the benchmark reflect your real-life workload? Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dismal performance on a 16G memory/8 core server - my.cnf settings?
Hi, Someone suggested I try the -amd64 kernels which provide 64 bit but when I try to boot it I get various errors about this CPU does not support long (something) please use a 32-bit OS - the 64 bit install CD says the same message. So I assume these are not 64 bit CPUs. They almost certainly are. Look at the contents of /proc/cpuinfo. You are probably using a 32-bit OS. You can't use a lot of memory efficiently unless you install a 64-bit OS, regardless of whether it has big memory support. But that's an x86_64 OS, not an AMD64 OS. These are not the same architecture. Er, since he's talking about a 6650, a 6th generation Dell machine, it very likely *does* have 32-bit CPUs. And he's said it came with the PERC 3/DC card, which is a very old RAID card. I would hope this machine didn't cost much, as it's quite old. Besides that, though, x86_64 is exactly the amd64 architecture. AMD came up with it, Linux called it amd64, and then when Intel copied it and called it EM64T, it was renamed in Linux to x86_64 to be more generic. The above message is the exact one you get when you try to boot an x86_64 kernel on a 32-bit CPU. Regards, Jeremy -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dismal performance on a 16G memory/8 core server - my.cnf settings?
Hi, On Thu, Apr 24, 2008 at 12:20 PM, Jeremy Cole [EMAIL PROTECTED] wrote: Hi, Someone suggested I try the -amd64 kernels which provide 64 bit but when I try to boot it I get various errors about this CPU does not support long (something) please use a 32-bit OS - the 64 bit install CD says the same message. So I assume these are not 64 bit CPUs. They almost certainly are. Look at the contents of /proc/cpuinfo. You are probably using a 32-bit OS. You can't use a lot of memory efficiently unless you install a 64-bit OS, regardless of whether it has big memory support. But that's an x86_64 OS, not an AMD64 OS. These are not the same architecture. Er, since he's talking about a 6650, a 6th generation Dell machine, it very likely *does* have 32-bit CPUs. And he's said it came with the PERC 3/DC card, which is a very old RAID card. I would hope this machine didn't cost much, as it's quite old. When you get old like me you won't remember every machine model :-) I didn't think they'd made 32-bit Xeons for a long time. (But I guess that's your point). Besides that, though, x86_64 is exactly the amd64 architecture. AMD came up with it, Linux called it amd64, and then when Intel copied it and called it EM64T, it was renamed in Linux to x86_64 to be more generic. And this one gets me every time. OK, sorry for the wrong advice JW! You can probably sort all this out on your own now. Like I said, /proc/cpuinfo. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dismal performance on a 16G memory/8 core server - my.cnf settings?
I think what we need to know is more stuff about the database itself. How big is it? I assume if you were able to serve it from a Mac Mini it can't be all that big. 16GB should be big enough to contain all the data and serve it up quickly. And while 4-disk RAID 5 isn't all that great, it's certainly better than a single ATA disk in Mac Mini. If your database is huge, then 16GB is barely enough to hold innodb buffer pool (if innodb) and maybe the indexes. Then your RAID config will come into play (and maybe filesystem type, tuning). Then whether it's a Dell PE1750 or a 6650 would not make a huge difference since it's I/O bound. Were you testing with something like production data or just some test data? Have you modified my.cnf to reflect the new hardware config? Like Baron Schwartz asked, does your test reflect real-life workload? And yes, the 3.0Ghz Xeon processors you mentioned are 32-bit. You get that error message if your processor doesn't have EM64T capability. -Paul JW wrote: Hello, We recently purchased a Dell PowerEdge 6650 thinking it would be a real fast server. Specs are: OS: Linux Debian 4.0/Etch RAID 5 on 4x U320 15k rpm drives (uses a perc-raid 3/DC hardware raid controller) 16GB of RAM 4 3.0 Ghz Xeon processors - I think they're dual core, in /proc/cpuinfo it shows up as 8 processors - maybe it's only HT I first made the mistake of using the default kernel, which provides SMP support but not large memory support. I have the output of a mysql sql-bench run from mysql on a Mac Mini to compare performance with. The server was only 0.35 (relative) the speed of the Mac mini - that means an 8 core 3.0 Ghz Xeon server with 16GB of RAM was only about 3x as fast as a as a single-core 1.25 Ghz G4 with 1GB of RAM (and a mini uses those little laptop hard drives, too). Needless to say my employer was shocked at the terrible performance and decided to sell the 6650 right away. But I can't help but wonder if there's not something terribly wrong with the settings - either the OS or mysql settings. I changed the kernel to the -bigmem kernel. It now sees all the RAM, but the sql-bench output on this try was _exactly_ the same: 0.35 I copied the my-huge.cnf from the examples directory and changed the thread_concurrency setting to 8 (because it said to set it to No. of CPUs*2). I also set the tmpdir, basedir, datadir and language, which were set in the original my.cnf I ran sql-bench again and the performance was even worse this time: 0.36 Someone suggested I try the -amd64 kernels which provide 64 bit but when I try to boot it I get various errors about this CPU does not support long (something) please use a 32-bit OS - the 64 bit install CD says the same message. So I assume these are not 64 bit CPUs. Any idea how I can configure this server to maximize performace? I think the multiple CPUs are a waste: I'm not looking for lots of concurrency, I want 1 query done really fast. Thanks. JW -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dismal performance on a 16G memory/8 core server - my.cnf settings?
JW wrote: Hello, We recently purchased a Dell PowerEdge 6650 thinking it would be a real fast server. Specs are: OS: Linux Debian 4.0/Etch RAID 5 on 4x U320 15k rpm drives (uses a perc-raid 3/DC hardware raid controller) 16GB of RAM 4 3.0 Ghz Xeon processors - I think they're dual core, in /proc/cpuinfo it shows up as 8 processors - maybe it's only HT snip This machine is not what is seems to be unfortunately. 1. Dell until their latest series with the Woodcrest based CPUs was a notoriously bad performer period. 2. Raid 5 + Database == run faster over serial cable. Use RAID 1+0. 3. Dell hasn't made a re-branded a decent RAID controller that is anything more than 18 months old. 4. 16GB doesn't help you because you are limited by your 32bitness. Get a real machine with real processors. 5. The CPU is hyper threaded, not dual core. I doubt you would ever get decent performance out of this for any database let alone MySQL. Tell your boss to suck it up, spend 5k. You can get a new, decent Dell for that. Or better yet, a HP. Sincerely, Joshua D. Drake -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
default my.cnf?
hi all... i have a 5.0.33 build from source on a freebsd 4.10 machine... i'm looking for a my.cnf file. ps tells me that the base dir is /usr/local but there is no my.cnf there. and i cant find one anywhere. i can get all the variables set up from the cli but i need to change some of them. i guess i can use mysqladmin but just wondering - can i just do a cnf under /usr/local/etc with the variables i need to change? ktrace is showing me that mysqld is not looking for any .cnf?! i mean i can try that but it's a very busy server and i wouldn't mess to much with it. thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default my.cnf?
I had the same problem. I found the distribution contains some prototypes, with slightly more elaborate names. Regards, Mike kalin m [EMAIL PROTECTED] 03/18/08 01:06 PM To mysql@lists.mysql.com cc Subject default my.cnf? hi all... i have a 5.0.33 build from source on a freebsd 4.10 machine... i'm looking for a my.cnf file. ps tells me that the base dir is /usr/local but there is no my.cnf there. and i cant find one anywhere. i can get all the variables set up from the cli but i need to change some of them. i guess i can use mysqladmin but just wondering - can i just do a cnf under /usr/local/etc with the variables i need to change? ktrace is showing me that mysqld is not looking for any .cnf?! i mean i can try that but it's a very busy server and i wouldn't mess to much with it. thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default my.cnf?
Hi, On Tue, Mar 18, 2008 at 1:06 PM, kalin m [EMAIL PROTECTED] wrote: hi all... i have a 5.0.33 build from source on a freebsd 4.10 machine... i'm looking for a my.cnf file. ps tells me that the base dir is /usr/local but there is no my.cnf there. and i cant find one anywhere. i can get all the variables set up from the cli but i need to change some of them. i guess i can use mysqladmin but just wondering - can i just do a cnf under /usr/local/etc with the variables i need to change? ktrace is showing me that mysqld is not looking for any .cnf?! i mean i can try that but it's a very busy server and i wouldn't mess to much with it. You can ask mysqld where it looks for its configuration files. Here's a Debian system: [EMAIL PROTECTED] ~ $ mysql --help --verbose | grep cnf /etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hi,I want to know how big to configurate the max-connections parameter in my.cnf?
On Dec 22, 2007 10:55 AM, Moon's Father [EMAIL PROTECTED] wrote: how big your mysql connections's users. How big users? don't know what you said. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf components
Hello Everybody, I am working on the tweaking of my.cnf and i am not able to understand that which component should get the maximun ram usage. for example max_allowed_packet, innodb_buffer_pool_size component how much of memory should be alloted to them. How the memory division should be done among the different component of my.cnf. Is there any method to find that max_allowed_packet memory usage. My Systems Configurations is Pentium 3.0 Ghz Memory 2 GB Regards, Krishna
Tune my.cnf
Hi All, I have a pentium box 3.0 GHz processor and 2 GB ram. I want to configure my.cnf in such a way that it should give the best performance. So, how should I configure the my.cnf parameter. What are the components on which the most attention should be paid. I am using innodb parameters. Regards, Krishna
Duplication in sles9 for my.cnf
Hi, I have noticed in the man page for mysql from MySQL-client-community-5.0.37-0.sles9.i586.rpm there is a repetition in the my.cnf files read by mysql. I am wondering if this is at all significant. This is what man mysql shows for the MySQL-client-community-5.0.37-0.sles9.i586.rpm install, mysql Ver 14.12 Distrib 5.0.37, for pc-linux-gnu (i686) using readline 5.0 Default options are read from the following files in the given order: /etc/my.cnf ~/.my.cnf /etc/my.cnf So the order is /etc/my.cnf ~/.my.cnf /etc/my.cnf On Debian Sarge with 4.1.11, the order is more sensible, mysql Ver 14.7 Distrib 4.1.11, for pc-linux-gnu (i386) Default options are read from the following files in the given order: /etc/mysql/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf The order on Sarge, 4.1.11 is /etc/mysql/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf Cheers, -Janek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
/etc/my.cnf in 5.0.27 RHEL4 RPMS is broken!
Description: The /etc/my.cnf that is included with RHEL4 5.0.27 RPMS is incompatibel with the /etc/init.d/mysql start scripts! [EMAIL PROTECTED] rhel4]$ sudo rpm -Uveh MySQL*.rpm Password: warning: MySQL-client-standard-5.0.27-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 Preparing...### [100%] 1:MySQL-shared-compat### [ 25%] 2:MySQL-client-standard ### [ 50%] 3:MySQL-devel-standard ### [ 75%] 4:MySQL-server-standard ### [100%] PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h batman.cogcap.com password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com Starting MySQLCouldn't find MySQL manager or server[FAILED] How-To-Repeat: run /etc/init.d/mysql again! Fix: Remove the following section from /etc/my.conf: [mysql.server] user=mysql basedir=/var/lib In /etc/init.d/mysql, parse_server_args sets $bindir=/var/lib/bin. So, /usr/bin/mysqld_safe is not found. Submitter-Id: Aaron Scamehorn Originator: Organization: Cognitive Capital, LLC. MySQL support: none Synopsis: Bad /etc/my.cnf in RHEL4 5.0.27 RPMS Severity: critical Priority: high Category: mysql Class: support Release: mysql-5.0.27-standard (MySQL Community Edition - Standard (GPL)) C compiler:gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2) C++ compiler: gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2) Environment: System: Linux batman.cogcap.com 2.6.9-42.0.8.ELsmp #1 SMP Tue Jan 30 12:33:47 EST 2007 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc/i386-redhat-linux/3.4.6/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-java-awt=gtk --host=i386-redhat-linux Thread model: posix gcc version 3.4.6 20060404 (Red Hat 3.4.6-3) Compilation info: CC='gcc' CFLAGS='-O2 -g -pipe -m32 -march=i386 -mtune=pentium4' CXX='gcc' CXXFLAGS='-O2 -g -pipe -m32 -march=i386 -mtune=pentium4' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Feb 13 15:29 /lib/libc.so.6 - libc-2.3.4.so -rwxr-xr-x 1 root root 1512916 Aug 12 2006 /lib/libc-2.3.4.so -rw-r--r-- 1 root root 2418632 Aug 12 2006 /usr/lib/libc.a -rw-r--r-- 1 root root 204 Aug 12 2006 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-server-suffix=-standard' '--without-embedded-server' '--with-innodb' '--with-archive-storage-engine' '--without-bench' '--with-zlib-dir=bundled' '--with-big-tables' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-pic' '--prefix=/' '--with-extra-charsets=complex' '--with-yassl' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--libdir=/usr/lib' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--enable-thread-safe-client' '--with-comment=MySQL Community Edition - Standard (GPL)' '--with-readline' 'CC=gcc' 'CFLAGS=-O2 -g -pipe -m32 -march=i386 -mtune=pentium4' 'CXXFLAGS=-O2 -g -pipe -m32 -march=i386 -mtune=pentium4' 'CXX=gcc' 'LDFLAGS=' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
