Hi, It depends on your query using join and sort. Also, on how much ram you have. You can refer to huge_my.cnf You can find huge_my.cnf in the complied mysql-server binary. join 12 MB sort 8 MB
On Wed, Oct 15, 2008 at 3:29 PM, Karthik Pattabhiraman < [EMAIL PROTECTED]> wrote: > I have not specified any values for join_buffer_size and > sort_buffer_size. What should be the optimal values for this? > > > > -Karthik > > > > *From:* Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED] > *Sent:* Tuesday, October 14, 2008 10:03 AM > *To:* Karthik Pattabhiraman > *Cc:* mysql@lists.mysql.com > *Subject:* Re: Trouble with large data in MySql > > > > Hi Karthik, > > You have many columns in a single table (although you can). But, if you > break the below table into two tables ( Vertical partitioning). It will help > you to run your query faster. You haven't shown join_buffer_size and > sort_buffer_size. > > Secondly, check your all the queries with explain. > > On Wed, Oct 8, 2008 at 4:00 PM, Karthik Pattabhiraman < > [EMAIL PROTECTED]> wrote: > > Hi, > > We have 4 tables in which we have approximately 40 Million records > per month. We are having trouble getting results from MySql as it takes > about 4-5 hours to complete for each query. We are using this primarily > for reporting purposes. > > > > My table schema is as follows > > SMAS Table: > > > > Column Name > > Type > > Key > > adnetwork > > adnetworkResponse > > campaignName > > clientRequest > > logkey > > loggingTime > > logmodule > > pageName > > propertyName > > requestId > > requestSystemId > > serverResponse > > sessionId > > siteName > > sucess > > systemId > > varchar(3999) > > text > > varchar(3999) > > varchar(3999) > > varchar(3999) > > timestamp > > varchar(3999) > > varchar(3999) > > varchar(3999) > > varchar(3999) > > varchar(3999) > > text > > varchar(256) > > varchar(3999) > > int(11) > > varchar(3999) > > > > > > > > > > > > MUL > > > > > > > > > > > > > > MUL > > > > > > > > > > Currently, SMAS table has 40Million records and our query takes 5 hours > to execute. > > > > My my.cnf file is as follows and all tables are InnoDB. > > > > [mysqld] > > datadir=/mnt/data-store/mysql/data > > 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 > > log-error=/var/log/mysqld.log > > pid-file=/var/run/mysqld/mysqld.pid > > innodb_data_home_dir=/mnt/data-store/mysql/data > > innodb_data_file_path=ibdata1:15G:autoextend > > innodb_buffer_pool_size=3G > > max_connections=200 > > tmpdir=/mnt/data-store/tmp > > > > [mysqld_safe] > > log-error=/var/log/mysqld.log > > pid-file=/var/run/mysqld/mysqld.pid > > > > My query is as follows on SMAS table > > select campaignName, siteName, adnetwork,date_format(loggingTime > ,'%d/%m/%Y') logDate, count(distinct requestid) adpages > > from SMAS where sucess = 1 GROUP BY 1,2,3,4; > > > > Any help will be highly appreciated. > > > > -Karthik > > > > > > > > -- > Krishna Chandra Prajapati > MySQL DBA, > Ed Ventures e-Learning Pvt.Ltd. > 1-8-303/48/15, Sindhi Colony > P.G.Road, Secunderabad. > Pin Code: 500003 > Office Number: 040-66489771 > Mob: 9912924044 > URL: ed-ventures-online.com > Email-id: [EMAIL PROTECTED] > -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 500003 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED]
