abazoskib Posted July 10, 2009 Share Posted July 10, 2009 +-----------+ | version() | +-----------+ | 5.0.45 | +-----------+ machine specs: cat /proc/meminfo MemTotal: 1788724 kB MemFree: 849172 kB Buffers: 144580 kB Cached: 627588 kB SwapCached: 0 kB Active: 790900 kB Inactive: 86064 kB HighTotal: 1044488 kB HighFree: 301460 kB LowTotal: 744236 kB LowFree: 547712 kB SwapTotal: 917496 kB SwapFree: 917496 kB Dirty: 328 kB Writeback: 0 kB AnonPages: 104796 kB Mapped: 15184 kB Slab: 24828 kB SReclaimable: 18352 kB SUnreclaim: 6476 kB PageTables: 1940 kB NFS_Unstable: 0 kB Bounce: 0 kB CommitLimit: 1811856 kB Committed_AS: 263756 kB VmallocTotal: 114680 kB VmallocUsed: 1360 kB VmallocChunk: 112936 kB my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 skip-networking set-variable = max_connections=2000 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid I run queries on a table with at least a million and a half records(and growing) that are pretty slow. I noticed I have no memory optimization in my.cnf. What can I do in terms of my.cnf to speed things up? Let me know if I need to post any other information. Thanks. Link to comment https://forums.phpfreaks.com/topic/165476-optimizing-mysql-help/ Share on other sites More sharing options...
gassaz Posted July 10, 2009 Share Posted July 10, 2009 You can try adding the line query_cache_size=x x = this value will depends of the amount of system memory, on my production server i set it up to 64M. If you tables are InnoDb like, you can try adding this too: innodb_buffer_pool_size=y y= this value will depends of the amount of system memory, this don't need to be like query_cache_size even it could be more or less. I hope this can help you. Link to comment https://forums.phpfreaks.com/topic/165476-optimizing-mysql-help/#findComment-872799 Share on other sites More sharing options...
Mchl Posted July 10, 2009 Share Posted July 10, 2009 http://pento.net/2009/03/05/a-brief-introduction-to-mysql-performance-tuning/ Link to comment https://forums.phpfreaks.com/topic/165476-optimizing-mysql-help/#findComment-872803 Share on other sites More sharing options...
abazoskib Posted July 10, 2009 Author Share Posted July 10, 2009 thanks for the help guys. can anyone estimate what i would set my max query cache size from the system specs i posted above? Link to comment https://forums.phpfreaks.com/topic/165476-optimizing-mysql-help/#findComment-872831 Share on other sites More sharing options...
gassaz Posted July 10, 2009 Share Posted July 10, 2009 I think the only way to get the magic number is increasing the values until get the best performance. For example I started at 32M, now i have a performance that work for my needs. Link to comment https://forums.phpfreaks.com/topic/165476-optimizing-mysql-help/#findComment-872844 Share on other sites More sharing options...
Mchl Posted July 10, 2009 Share Posted July 10, 2009 It depends if this is a dedicated MySQL machine, or if there are other services running there. Also do not underestimate the possibility, that you can increase performance by adding indexes or optimising the query. Link to comment https://forums.phpfreaks.com/topic/165476-optimizing-mysql-help/#findComment-872851 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.